psql (18.3 (Debian 18.3-1+b1)) Type "help" for help.
Debian OS is forky.
When setting up a new development database, the following occurred:-
applntestdb=> \copy forms_table (item_type, navgn_refn, html_name, table_key, navgn_bar, rows_page, forward_to, second_to, tertiary_to, active_item, super_only, system_admin, inserted_by) from '/home/postgres/loadfiles/formstable.txt' header delimiter '|'; ERROR: invalid input syntax for type smallint: "null" CONTEXT: COPY forms_table, line 2, column rows_page: "null" applntestdb=>
This is what is on line 2 of the file:- C|0|pageloader|||null|null|null|null|t|f|f|0
The test below was simplified to just choose one SMALLINT column.
applntestdb=> insert into forms_table (item_type, navgn_refn, html_name, rows_page, active_item, inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0); INSERT 0 1 applntestdb=> delete from forms_table where item_type = 'C'; DELETE 1 applntestdb=> insert into forms_table (item_type, navgn_refn, html_name, rows_page, active_item, inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0); ERROR: invalid input syntax for type smallint: "" LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0) ^
So, psql is quite happy to insert null into a column defined as smallint, but when you use the \copy mechanism to populate a table it pulls an error.
How do we fix this? Alter all the nulls to zeroes in the file or write a program to dissect the file and create individual insert statements?
Has anybody else had this problem and if so what was the solution?
"null" is not what COPY uses to signal a null value. I think it's \N but the best solution i to COPY TO your table to STDOUT and see what it uses as the null indicator.
\copy forms_table TO STDOUT WITH (HEADER, DELIMITER '|');
