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