Request a Demo
Home   >   mailing lists

Re: Using \copy to populate a table - Mailing list pgsql-general

From Francisco Olarte
Subject Re: Using \copy to populate a table
Date
Msg-id CA+bJJbwDP00q-fcXUaU3pXg=o4DdWwoeSGDQOUBQ1Sa363fiug@mail.gmail.com
Whole thread
In response to Using \copy to populate a table  (rob stone <floriparob@tpg.com.au>)
List pgsql-general
Tree view
Hi Rob:

On Sun, 15 Mar 2026 at 08:12, rob stone <floriparob@tpg.com.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.

pgsql-general by date:

Previous
From: Ron Johnson
Date:
Subject: Re: Using \copy to populate a table
Next
From: Adrian Klaver
Date:
Subject: Re: Using \copy to populate a table