Re: COPY FROM - force a value - Mailing list pgsql-sql

From Alvaro Herrera
Subject Re: COPY FROM - force a value
Date
Msg-id 20070208192149.GY24069@alvh.no-ip.org
Whole thread Raw
In response to COPY FROM - force a value  ("Demel, Jeff" <Jeff.Demel@JavelinDirect.com>)
List pgsql-sql
Demel, Jeff wrote:
> Is there a way to force a value when you're doing a COPY FROM, importing
> a file into a table?
> 
> Here's my query as it is now:
> 
> COPY filetable (value1, value2, value3, value4, forcevalue1,
> forcevalue2)
> FROM 'C:\\InsertFiles\\thisfile.txt' 
>   WITH DELIMITER AS '    '
> ;
> 
> The file only contains data for values 1 through 4.  I'd like to insert
> values for the last two fields.  This is what I had in mind, which
> doesn't work:
> 
> COPY filetable (value1, value2, value3, value4, forcevalue1,
> forcevalue2)
> FROM 'C:\\InsertFiles\\thisfile.txt' 
>   WITH DELIMITER AS '    ',
>   forcevalue1 = 1,
>   forcevalue2 = 'this value'
> ;

I'd try setting a DEFAULT for those two columns using ALTER TABLE, then
the COPY FROM call excluding those columns, then removing the DEFAULT.
If you do it in a transaction block, no other transaction can be
molested by the default values, though they will be blocked of the table
during that transaction.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


pgsql-sql by date:

Previous
From: "David Klugmann"
Date:
Subject: unsubscribe
Next
From: Jan Wieck
Date:
Subject: Re: Differentiate Between Zero-Length String and NULLColumn Values