Menu
My table has a NOT NULL column called 'created' with DEFAULT CLOCK_TIMESTAMP(). My CSV file from which I am copying intentionally does not have a column called 'created' because I want the database to use the default value for it. My copy command is:
\copy table_name from '/local/path/to/file.csv' delimiter ',' CSV HEADER
The error I receive is:
missing data for column "created_"
The PostgreSQL documentation says: "If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns."
Thanks for any help
778
The default is a tab character in text format, a comma in CSV format. This must be a single one-byte character.
The PostgreSQL CURRENT_DATE function returns the current date (the system date on the machine running PostgreSQL) as a value in the 'YYYY-MM-DD' format. In this format, 'YYYY' is a 4-digit year, 'MM' is a 2-digit month, and 'DD' is a 2-digit day. The returned value is a date data type.
In a table definition, default values are listed after the column data type. For example: CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99 ); The default value can be an expression, which will be evaluated whenever the default value is inserted (not when the table is created).
Specifies the ASCII quotation character in CSV mode. The default is double-quote.
The clue is in the "that are not in the column list".
You need to specify the "column list" for this to work and leave out the one with the default value:
\copy table_name (column1, column2, ...) from '/local/path/to/file.csv' delimiter ',' CSV HEADER
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
