pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY FROM - force a value
3+ messages / 2 participants
[nested] [flat]
* COPY FROM - force a value
@ 2007-02-08 19:13 Demel, Jeff <Jeff.Demel@JavelinDirect.com>
0 siblings, 1 reply; 3+ messages in thread
From: Demel, Jeff @ 2007-02-08 19:13 UTC (permalink / raw)
To: pgsql-sql
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'
;
TIA
-Jeff
This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: COPY FROM - force a value
@ 2007-02-08 19:21 Alvaro Herrera <alvherre@commandprompt.com>
parent: Demel, Jeff <Jeff.Demel@JavelinDirect.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2007-02-08 19:21 UTC (permalink / raw)
To: Demel, Jeff <Jeff.Demel@JavelinDirect.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: COPY FROM - force a value
@ 2007-02-08 19:55 Demel, Jeff <Jeff.Demel@JavelinDirect.com>
0 siblings, 0 replies; 3+ messages in thread
From: Demel, Jeff @ 2007-02-08 19:55 UTC (permalink / raw)
To: pgsql-sql
That works like a charm. Thanks, Alvaro!
-Jeff
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Thursday, February 08, 2007 1:22 PM
To: Demel, Jeff
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY FROM - force a value
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.
This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-08 19:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-08 19:13 COPY FROM - force a value Demel, Jeff <Jeff.Demel@JavelinDirect.com>
2007-02-08 19:21 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-02-08 19:55 Re: COPY FROM - force a value Demel, Jeff <Jeff.Demel@JavelinDirect.com>
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