Message-ID: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
Subject: Using \copy to populate a table
From: rob stone <floriparob@tpg.com.au>
To: PostGreSQL MailingList <pgsql-general@postgresql.org>
Date: Sun, 15 Mar 2026 18:11:59 +1100
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2-8 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/43388977a1cdbde81043c690b41e0647e9b95a2b.camel%40tpg.com.au>
Precedence: bulk

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=3D> \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=3D>

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.=20

applntestdb=3D> 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=3D> delete from forms_table where item_type =3D 'C';
DELETE 1
applntestdb=3D> 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)
                                                   ^
                                                  =20
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