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
Date: Thu, 8 Feb 2007 16:21:49 -0300
Message-ID: <20070208192149.GY24069@alvh.no-ip.org> (raw)
In-Reply-To: <136ED738BD4F1545B97E4AC06FF6370734C296@DMSP-MSG-EVS01.mail.pvt>
References: <136ED738BD4F1545B97E4AC06FF6370734C296@DMSP-MSG-EVS01.mail.pvt>
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.
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: pgsql-sql@postgresql.org
Cc: alvherre@commandprompt.com, Jeff.Demel@JavelinDirect.com
Subject: Re: COPY FROM - force a value
In-Reply-To: <20070208192149.GY24069@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox