Results 1 to 5 of 5
\r\n \r\n\r\n \r\n \r\n
\r\n
\r\n
\r\n I have a postgresql 9.6 database I just created.
\r\nI created a table using the following layout:
\r\nCREATE TABLE IF NOT EXISTS schema_name.table_name (
\r\n
sample_dttm TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
\r\ndata JSONb NOT NULL,
\r\nPRIMARY KEY (sample_dttm)
);

\r\nThe 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).
\r\nThe timestamp is not in the file, only the JSON data.
\r\nProblem is, I can\'t figure out how to load this data.
\r\nThe 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.
\r\n
\r\n
\r\n\r\n \r\n\r\n\r\n
\r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n
\r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n\r\n
  • \r\n
    \r\n
    \r\n \r\n

    \r\n \r\n \r\n
    Best Answer
    Posted by shammat\r\n

    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n
    \r\n

    "\\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:
    \r\n
    \r\n
    Code:
    \r\n
    \\copy table_name (data) from \'/path/to/input_file.txt\'
    \r\n
    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
    \r\n
    \r\nSo 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."

    \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; pd[6646420] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #2\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n shammat is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Nov 2003
    \r\n \r\n \r\n
    Posts
    2,993
    \r\n \r\n
    \r\n \r\n
    Provided Answers: 23
    \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n \\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:
    \r\n
    \r\n
    Code:
    \r\n
    \\copy table_name (data) from \'/path/to/input_file.txt\'
    \r\n
    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
    \r\n
    \r\nSo 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.
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n
    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
    \r\n
    \r\nTips for good questions:
    \r\n
    \r\nhttp://tkyte.blogspot.de/2005/06/how...questions.html
    \r\nhttp://wiki.postgresql.org/wiki/SlowQueryQuestions
    \r\nhttp://catb.org/esr/faqs/smart-questions.html
    \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; pd[6646421] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #3\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n usao is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Jan 2017
    \r\n \r\n \r\n
    Posts
    16
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n

    \r\n Thanks\r\n

    \r\n \r\n \r\n
    \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n I missed that capability in "\\copy", ill go back and take a look at that.
    \r\nAs for the timestamp, maybe im missing something here. Both seem to have the same resolution.
    \r\n
    \r\n
    postgres=# select clock_timestamp();
    \r\n clock_timestamp
    \r\n-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-
    \r\n 2017-01-08 05:20:52.281311-07
    \r\n(1 row)
    \r\n
    \r\npostgres=# select current_timestamp;
    \r\n now
    \r\n-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-
    \r\n 2017-01-08 05:20:55.924452-07
    \r\n(1 row)

    \r\n
    \r\nSince 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.
    \r\nI 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.
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; pd[6646423] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #4\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n shammat is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Nov 2003
    \r\n \r\n \r\n
    Posts
    2,993
    \r\n \r\n
    \r\n \r\n
    Provided Answers: 23
    \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n clock_timestamp() and current_timestamp do have the same resolution.
    \r\n
    \r\nBut for multi-row inserts the default with current_timestamp will fail - e.g. if your input file has more then one row in it
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n
    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
    \r\n
    \r\nTips for good questions:
    \r\n
    \r\nhttp://tkyte.blogspot.de/2005/06/how...questions.html
    \r\nhttp://wiki.postgresql.org/wiki/SlowQueryQuestions
    \r\nhttp://catb.org/esr/faqs/smart-questions.html
    \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; pd[6646424] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #5\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n usao is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Jan 2017
    \r\n \r\n \r\n
    Posts
    16
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n

    \r\n Ok\r\n

    \r\n \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n Ok, I see. All my loads are 1 record every 60 seconds.
    \r\nPerhaps the issue is that the JSON record is a multi-line object.
    \r\nIll try removing the line-feed characters and see if that resolves my issue.
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; // next/previous post info pn[6646417] = "6646424,6646420"; pn[0] = ",6646417"; pn[6646420] = "6646417,6646421"; pn[6646421] = "6646420,6646423"; pn[6646423] = "6646421,6646424"; pn[6646424] = "6646423,6646417"; // cached usernames pu[0] = guestphrase; pu[365396] = "usao"; pu[46908] = "shammat"; // -->

    Threaded View

    1. #1
      Join Date
      Jan 2017
      Posts
      16

      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."


    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
    •