public inbox for [email protected]
help / color / mirror / Atom feedFrom: Brent Wood <[email protected]>
To: rob stone <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Using \copy to populate a table
Date: Sun, 15 Mar 2026 07:21:52 +0000
Message-ID: <AK0P299MB010177B7E8902CCB5C4DD48DFF43A@AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Have you tried explicitly specifying the string to represent null values?
COPY table_name FROM 'path/to/file' WITH (FORMAT text, NULL 'null');
-- Or the older syntax:
COPY table_name FROM 'path/to/file' WITH DELIMITER ',' NULL AS 'null';
________________________________
From: rob stone <[email protected]>
Sent: Sunday, March 15, 2026 8:11 PM
To: PostGreSQL MailingList <[email protected]>
Subject: Using \copy to populate a table
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
Brent Wood
Principal Technician - GIS and Spatial Data Management
+64-4-386-0529
301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
Earth Sciences New Zealand
[Earth Sciences New Zealand]<https://earthsciences.nz;
The Institute of Geological and Nuclear Sciences Limited and the National Institute of Water and Atmospheric Research Limited joined to become the New Zealand Institute for Earth Science Limited. We are known as Earth Sciences New Zealand. For more information on the Earth Sciences transition click here<https://niwa.co.nz/about-niwa/science-sector-reforms;.
Notice: This email and any attachments may contain information which is confidential and/or subject to copyright or legal privilege, and may not be used, published or redistributed without the prior written consent of Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and delete the email and any attachments. Any opinion or views expressed in this email are those of the individual sender and may not represent those of Earth Sciences New Zealand.
For information about how we process data and monitor communications please see our privacy policy<https://niwa.co.nz/about-niwa/privacy-policy;.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Using \copy to populate a table
In-Reply-To: <AK0P299MB010177B7E8902CCB5C4DD48DFF43A@AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox