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?
Thanks,
Rob
