public inbox for [email protected]  

help / color / mirror / Atom feed

From: Francisco Olarte <[email protected]>
To: rob stone <[email protected]>
Cc: PostGreSQL MailingList <[email protected]>
Subject: Re: Using \copy to populate a table
Date: Sun, 15 Mar 2026 10:50:33 +0100
Message-ID: <CA+bJJbwDP00q-fcXUaU3pXg=o4DdWwoeSGDQOUBQ1Sa363fiug@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Hi Rob:

On Sun, 15 Mar 2026 at 08:12, rob stone <[email protected]> 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.

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], [email protected]
  Subject: Re: Using \copy to populate a table
  In-Reply-To: <CA+bJJbwDP00q-fcXUaU3pXg=o4DdWwoeSGDQOUBQ1Sa363fiug@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