MIME-Version: 1.0
References: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
In-Reply-To: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
From: Peter Kleiner <runtfan71@gmail.com>
Date: Mon, 16 Mar 2026 22:16:26 -0400
Message-ID: <CAP8Hi7KSkwP-9=Nd5qJ-co4jg3ucDHR_gcGTgpsp7Y_cTwq5uw@mail.gmail.com>
Subject: Re: Using \copy to populate a table
Cc: PostGreSQL MailingList <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d940aa064d2eec99"
Archived-At: <https://www.postgresql.org/message-id/CAP8Hi7KSkwP-9%3DNd5qJ-co4jg3ucDHR_gcGTgpsp7Y_cTwq5uw%40mail.gmail.com>
Precedence: bulk

--000000000000d940aa064d2eec99
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?
>
>
Caveat: I'm a hack, I rarely respond to questions here, I have limited
knowledge about PGSQL, databases in general, and generally hesitate to
chime in where my betters are wont to dwell.

That said... If it was me, I would at least try switching this

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

with this

 \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' with header delimiter '|' NULL as
'null';

In other words, your CSV file seems to be outputting the string 'null' for
NULL values.

Doing it this way, however, would mean that any of your fields whose string
values might be "null" would be interpreted as NULL values.

The fact that no one has already offered this as an approach makes me
skeptical, but perhaps you could give it a shot to see if it works.

Pete

--000000000000d940aa064d2eec99
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><br><=
/blockquote><div>=C2=A0</div><div>Caveat: I&#39;m a hack, I rarely respond=
=C2=A0to questions here, I have limited knowledge about PGSQL, databases in=
 general, and generally hesitate to chime in where my betters are wont to d=
well.</div><div><br></div><div>That said... If it was me, I would at least =
try switching this</div><div><br></div><div>=C2=A0\copy forms_table (item_t=
ype, 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 delim=
iter &#39;|&#39;;</div><div><br></div><div>with this</div><div><br></div><d=
iv>=C2=A0\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/loadfile=
s/formstable.txt&#39; with header delimiter &#39;|&#39; NULL as &#39;null&#=
39;;</div><div><br></div><div>In other words, your CSV file seems to be out=
putting the string &#39;null&#39; for NULL values.=C2=A0 =C2=A0</div><div><=
br></div><div>Doing it this way, however, would mean that any of your field=
s whose string values might be &quot;null&quot; would be interpreted as NUL=
L values.</div><div><br></div><div>The fact that no one has already offered=
 this as an approach makes me skeptical, but perhaps you could give it a sh=
ot to see if it works.</div><div><br></div><div>Pete</div><div><br></div><d=
iv><br></div></div></div>

--000000000000d940aa064d2eec99--