MIME-Version: 1.0
References: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
In-Reply-To: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 15 Mar 2026 05:48:10 -0400
Message-ID: <CANzqJaD__1E3dnoUz+pjDWus+M6rvWoC+_NqdBpso9g0pn0+wg@mail.gmail.com>
Subject: Re: Using \copy to populate a table
To: PostGreSQL MailingList <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ae3b1a064d0d00d6"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD__1E3dnoUz%2BpjDWus%2BM6rvWoC%2B_NqdBpso9g0pn0%2Bwg%40mail.gmail.com>
Precedence: bulk

--000000000000ae3b1a064d0d00d6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Mar 15, 2026 at 3:12=E2=80=AFAM rob stone <floriparob@tpg.com.au> w=
rote:

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


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

--000000000000ae3b1a064d0d00d6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Mar 15, 2026 at 3:12=E2=80=AFAM r=
ob stone &lt;<a href=3D"mailto:floriparob@tpg.com.au">floriparob@tpg.com.au=
</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">psql (18.3 (Debian 18.3-1+b1))=
<br>
Type &quot;help&quot; for help.<br>
<br>
Debian OS is forky.<br>
<br>
When setting up a new development database, the following occurred:-<br>
<br>
applntestdb=3D&gt; \copy forms_table (item_type, navgn_refn, html_name,<br>
table_key, navgn_bar, rows_page, forward_to, second_to, tertiary_to,<br>
active_item, super_only, system_admin, inserted_by) from<br>
&#39;/home/postgres/loadfiles/formstable.txt&#39; header delimiter &#39;|&#=
39;;<br>
ERROR:=C2=A0 invalid input syntax for type smallint: &quot;null&quot;<br>
CONTEXT:=C2=A0 COPY forms_table, line 2, column rows_page: &quot;null&quot;=
<br>
applntestdb=3D&gt;<br>
<br>
This is what is on line 2 of the file:-<br>
C|0|pageloader|||null|null|null|null|t|f|f|0<br>
<br>
The test below was simplified to just choose one SMALLINT column. <br>
<br>
applntestdb=3D&gt; insert into forms_table (item_type, navgn_refn,<br>
html_name, rows_page, active_item,<br>
inserted_by) values (&#39;C&#39;, 0, &#39;pageloader&#39;, null, TRUE, 0);<=
br>
INSERT 0 1<br>
applntestdb=3D&gt; delete from forms_table where item_type =3D &#39;C&#39;;=
<br>
DELETE 1<br>
applntestdb=3D&gt; insert into forms_table (item_type, navgn_refn,<br>
html_name, rows_page, active_item,<br>
inserted_by) values (&#39;C&#39;, 0, &#39;pageloader&#39;, &#39;&#39;, TRUE=
, 0);<br>
ERROR:=C2=A0 invalid input syntax for type smallint: &quot;&quot;<br>
LINE 2: inserted_by) values (&#39;C&#39;, 0, &#39;pageloader&#39;, &#39;=
9;, TRUE, 0)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^<br>
<br>
So, psql is quite happy to insert null into a column defined as<br>
smallint, but when you use the \copy mechanism to populate a table it<br>
pulls an error.<br>
<br>
How do we fix this? Alter all the nulls to zeroes in the file or write<br>
a program to dissect the file and create individual insert statements?<br>
<br>
Has anybody else had this problem and if so what was the solution?<br></blo=
ckquote><div><br></div><div>&quot;null&quot; is not what COPY uses to signa=
l a null value.=C2=A0 I think it&#39;s \N but the best solution i to COPY T=
O your table to STDOUT and see what it uses as the null indicator.</div><di=
v>\copy forms_table=C2=A0 TO STDOUT WITH (HEADER,=C2=A0DELIMITER &#39;|&#39=
;);</div><div><br></div><div><br></div></div><span class=3D"gmail_signature=
_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></=
div></div></div></div>

--000000000000ae3b1a064d0d00d6--