Feedback-ID: i76984098:Fastmail
Message-ID: <18ef99ab-f4c1-44a5-b78a-35c261993017@aklaver.com>
Date: Sun, 15 Mar 2026 08:30:44 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Using \copy to populate a table
To: rob stone <floriparob@tpg.com.au>,
 PostGreSQL MailingList <pgsql-general@postgresql.org>
References: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/18ef99ab-f4c1-44a5-b78a-35c261993017%40aklaver.com>
Precedence: bulk

On 3/15/26 12:11 AM, rob stone wrote:
> 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:-
> 

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

CSV is a text format and in:

C|0|pageloader|||null|null|null|null|t|f|f|0

the null values are the string 'null' which indeed is not a valid syntax 
for a smallint.

> 
> Has anybody else had this problem and if so what was the solution?

Do you have control over whatever creates the file?

An option is to create a staging table that is has all string fields, 
import into it and then do clean up before moving to final table.


> 
> Thanks,
> Rob
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com