Re: Using \copy to populate a table

Hi Rob:

On Sun, 15 Mar 2026 at 08:12, rob stone <floriparob(at)tpg(dot)com(dot)au> wrote:

>
> This is what is on line 2 of the file:-
> C|0|pageloader|||null|null|null|null|t|f|f|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.
>

It does not for me, but syntax for INSERT and COPY ( \copy just does a COPY
from stdin ) is different.

> 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?
>

Have you tried a little RTFM?

specifically ....

https://www.postgresql.org/docs/18/sql-copy.html

Where somewhere it says:

NULL

Specifies the string that represents a null value. The default is \N
(backslash-N) in text format, and an unquoted empty string in CSV format.
You might prefer an empty string even in text format for cases where you
don't want to distinguish nulls from empty strings. This option is not
allowed when using binary format.

If you generate four text files I would recommend switching to \N, it will
be less painful.

Francisco Olarte.