public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: PostGreSQL MailingList <[email protected]>
Subject: Re: Using \copy to populate a table
Date: Sun, 15 Mar 2026 05:48:10 -0400
Message-ID: <CANzqJaD__1E3dnoUz+pjDWus+M6rvWoC+_NqdBpso9g0pn0+wg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Sun, Mar 15, 2026 at 3:12 AM rob stone <[email protected]> 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!
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]
Subject: Re: Using \copy to populate a table
In-Reply-To: <CANzqJaD__1E3dnoUz+pjDWus+M6rvWoC+_NqdBpso9g0pn0+wg@mail.gmail.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