Re: Using \copy to populate a table

On Sun, Mar 15, 2026 at 3:12 AM rob stone <floriparob(at)tpg(dot)com(dot)au> wrote:

> 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 '|');

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!