Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2017
    Posts
    12

    Answered: Loading JSON into PostgreSQL 9.6

    I have a postgresql 9.6 database I just created.
    I created a table using the following layout:
    CREATE TABLE IF NOT EXISTS schema_name.table_name (
    sample_dttm TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data JSONb NOT NULL,
    PRIMARY KEY (sample_dttm)
    );

    The purpose is to load JSON data from a flat-file, with a timestamp of when the record is loaded to be provided by the server itself (hence the DEFAULT value).
    The timestamp is not in the file, only the JSON data.
    Problem is, I can't figure out how to load this data.
    The COPY command required ADMIN privleges, and the "\copy" command requires all the columns be included in the flat-file. I cannot load using the INSERT command, as the data is too long and is generating errors due to the length.

  2. Best Answer
    Posted by shammat

    "\copy does not require all columns to be in the file, you can specify the target columns the same way you can do with COPY:
    Code:
    \copy table_name (data) from '/path/to/input_file.txt'
    But I highly question the use of a timestamp column as the primary key. The resolution is is not fine grained enough and current_timestamp returns the same value for all calls inside a transaction. For details please see: https://www.postgresql.org/docs/curr...TETIME-CURRENT

    So insert into table_name (sample_dttm) values (current_timestamp, current_timesamp) will fail. You should at least change the default to use clock_timestamp() instead. If you want a generated primary key, use a serial instead."


  3. #2
    Join Date
    Nov 2003
    Posts
    2,983
    Provided Answers: 23
    \copy does not require all columns to be in the file, you can specify the target columns the same way you can do with COPY:
    Code:
    \copy table_name (data) from '/path/to/input_file.txt'
    But I highly question the use of a timestamp column as the primary key. The resolution is is not fine grained enough and current_timestamp returns the same value for all calls inside a transaction. For details please see: https://www.postgresql.org/docs/curr...TETIME-CURRENT

    So insert into table_name (sample_dttm) values (current_timestamp, current_timesamp) will fail. You should at least change the default to use clock_timestamp() instead. If you want a generated primary key, use a serial instead.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Jan 2017
    Posts
    12

    Thanks

    I missed that capability in "\copy", ill go back and take a look at that.
    As for the timestamp, maybe im missing something here. Both seem to have the same resolution.

    postgres=# select clock_timestamp();
    clock_timestamp
    -------------------------------
    2017-01-08 05:20:52.281311-07
    (1 row)

    postgres=# select current_timestamp;
    now
    -------------------------------
    2017-01-08 05:20:55.924452-07
    (1 row)


    Since the records (in my case) are every 60 seconds, and each is inserted using a new invocation of psql via a shell job, it seems as the PK as defined should be fine.
    I can see the argument regarding multiple calls to "\copy" within a transaction causing issues however in my case I don't see how I could get a PK violation.

  5. #4
    Join Date
    Nov 2003
    Posts
    2,983
    Provided Answers: 23
    clock_timestamp() and current_timestamp do have the same resolution.

    But for multi-row inserts the default with current_timestamp will fail - e.g. if your input file has more then one row in it
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #5
    Join Date
    Jan 2017
    Posts
    12

    Ok

    Ok, I see. All my loads are 1 record every 60 seconds.
    Perhaps the issue is that the JSON record is a multi-line object.
    Ill try removing the line-feed characters and see if that resolves my issue.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •