Date: Thu, 8 Feb 2007 16:21:49 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: "Demel, Jeff" <Jeff.Demel@JavelinDirect.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: COPY FROM - force a value
Message-ID: <20070208192149.GY24069@alvh.no-ip.org>
Mail-Followup-To: "Demel, Jeff" <Jeff.Demel@JavelinDirect.com>,
	pgsql-sql@postgresql.org
References: <136ED738BD4F1545B97E4AC06FF6370734C296@DMSP-MSG-EVS01.mail.pvt>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <136ED738BD4F1545B97E4AC06FF6370734C296@DMSP-MSG-EVS01.mail.pvt>
User-Agent: Mutt/1.5.13 (2006-08-11)

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.