Re: [GENERAL] COPY with default values?
You are correct and if you did your bulk insert with INSERT commands, it
will work just fine. The difference is the COPY command which AFAIK was/is
intended for backup and restore use.
len morgan
-Original Message-
From: Jeff Boes <[EMAIL PROTECTED]>
To: Postgres-general <[EMAIL PROTECTED]>
Date: Friday, May 25, 2001 10:25 AM
Subject: Re: [GENERAL] COPY with default values?
>On Fri, 25 May 2001 10:33:41 -0400
>Tom Lane <[EMAIL PROTECTED]> wrote:
>
>> COPY does not deal with insertion of default values. Sorry.
>
>This seems odd to me, especially since Pgsql treats
>
>INSERT INTO keywords (key_id, key_text) VALUES (null, 'foo');
>
>differently than
>
>INSERT INTO keywords (key_text) VALUES ('foo');
>
>It's been a while, but I'm pretty sure Oracle will do the same thing for
>each of these inserts, namely apply a default value when a null is
>detected.
>
>But thanks for the help!
>
>--
>Jeff Boes vox 616.226.9550
>Database Engineer fax 616.349.9076
>Nexcerpt, Inc. [EMAIL PROTECTED]
>
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] COPY with default values?
On Fri, 25 May 2001 10:33:41 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> COPY does not deal with insertion of default values. Sorry.
This seems odd to me, especially since Pgsql treats
INSERT INTO keywords (key_id, key_text) VALUES (null, 'foo');
differently than
INSERT INTO keywords (key_text) VALUES ('foo');
It's been a while, but I'm pretty sure Oracle will do the same thing for
each of these inserts, namely apply a default value when a null is
detected.
But thanks for the help!
--
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc. [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] COPY with default values?
Jeff Boes <[EMAIL PROTECTED]> writes: > Now I would like to initialize this table with a COPY statement, but > without supplying values for the primary key. COPY does not deal with insertion of default values. Sorry. One rather klugy answer is to COPY to a temp table that has only the columns you want to supply, and then INSERT ... SELECT into the main table. Might be faster if there are enough rows involved. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY with default values?
I believe COPY is limited to reading ENTIRE records into a table not pieces
of them.
len morgan
-Original Message-
From: Jeff Boes <[EMAIL PROTECTED]>
To: Postgres-general <[EMAIL PROTECTED]>
Date: Friday, May 25, 2001 9:20 AM
Subject: [GENERAL] COPY with default values?
>Let's say I have a table of keywords, with a SERIAL primary key.
>
>CREATE TABLE keywords (
> key_id SERIAL PRIMARY KEY,
> key_text TEXT
>);
>
>Now I would like to initialize this table with a COPY statement, but
>without supplying values for the primary key. In other words, how can I
>use COPY to perform the same function as
>
>INSERT INTO keywords (keyword_text) VALUES ('foo');
>INSERT INTO keywords (keyword_text) VALUES ('bar');
>...
>
>I have tried
>
>COPY keywords FROM stdin USING DELIMITERS '|';
>|foo
>|bar
>...
>
>and also
>
>0|foo
>0|bar
>
>and even
>
>\N|foo
>\N|bar
>
>I even tried creating a view on keywords that has only keyword_text, and
>copying into THAT--no luck. Then I wrote a rule to replace inserts on the
>view with inserts on the table, but apparently COPY doesn't trigger INSERT
>rules. Grumble...
>
>
>--
>Jeff Boes vox 616.226.9550
>Database Engineer fax 616.349.9076
>Nexcerpt, Inc. [EMAIL PROTECTED]
>
>
>---(end of broadcast)---
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] COPY with default values?
Let's say I have a table of keywords, with a SERIAL primary key.
CREATE TABLE keywords (
key_id SERIAL PRIMARY KEY,
key_text TEXT
);
Now I would like to initialize this table with a COPY statement, but
without supplying values for the primary key. In other words, how can I
use COPY to perform the same function as
INSERT INTO keywords (keyword_text) VALUES ('foo');
INSERT INTO keywords (keyword_text) VALUES ('bar');
...
I have tried
COPY keywords FROM stdin USING DELIMITERS '|';
|foo
|bar
...
and also
0|foo
0|bar
and even
\N|foo
\N|bar
I even tried creating a view on keywords that has only keyword_text, and
copying into THAT--no luck. Then I wrote a rule to replace inserts on the
view with inserts on the table, but apparently COPY doesn't trigger INSERT
rules. Grumble...
--
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc. [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] COPY with default values won't work?
At 07:15 +0200 on 24/03/1999, Charles Tassell wrote:
>
> I'm trying to copy data into the following table:
>
> CREATE SEQUENCE seq_account_type_ndx;
>
> CREATE TABLE accounts (
> Account_Type_NDXint4 not null default
> nextval('seq_account_type_ndx'),
> Account_NameText
> );
>
> Using this as a datafile:
> \N|Box
> \N|NetSurfer120
> \N|eMailer
> \N|eMailerLite
>
> I've tried writing the code in C using libpq, using the copy command as the
> postgres super user, or using \copy as my normal user. NONE will work with
> the "not null" in there, and if I remove it, it just inserts a null value
> into account_type_ndx, without using the default. I've also tried
> switching the default to a number (ie default 12) instead of the nextval of
> the sequence, with no better luck.
>
> Here is the copy command I tend to use:
> COPY accounts from stdin USING delimiters '|'
> or \COPY accounts from '/tmp/datafile.txt' USING delimiters '|'
>
> Any ideas?
I thought the above would work, too, but apparently it doesn't.
So, two possible solutions:
A) Update with the sequence after you have copied.
1) Create the table without the NOT NULL.
2) Make the copy
3) Use
UPDATE accounts
SET Account_Type_NDX = nextval( 'seq_account_type_ndx' );
4) Vacuum.
B) Copy into a separate table and insert.
1) Create the table, including the NOT NULL and everything.
2) Create a temporary table, with all the same fields, without NOT NULL.
3) Copy into the temporary table.
4) Use:
INSERT INTO accounts ( Account_Name )
SELECT Account_Name FROM temp_accounts;
5) Drop the temp_accounts table.
Variation: Create the temp_accounts table without the Account_Type_NDX
field. It's null anyway. Have your copy files without the "\N|" part.
Saves the transfer of three bytes per row and the insertion of a null
value per row. Makes things a wee bit faster.
My personal favourite is plan (B), because it allows building the table
with the "NOT NULL" constraint, and does not require you to remember the
name of the sequence. The general principle here is:
1) Look at your table and decide which fields should be inserted from
an external data source, and which from an internal data source
(these are usually the fields that have a default value).
2) Create a temporary table that contains only the fields that need to
be fed externally.
3) Copy your data into that table. The copy files need not have any
NULL value unless it truely stands for "no value here".
4) Insert into your real table using a SELECT statement. The INSERT
clause should include only the names of "external source" fields.
This will cause the internal ones to be filled from the default
source.
This method allows also the use of functions and stuff when populating the
table.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Re: [GENERAL] COPY with default values won't work?
On Wed, 24 Mar 1999, Charles Tassell wrote: > @#$#!! Any way to make COPY use default, or shove a lot of data in with a > single INSERT query? According to older messages in the mailing list, it > is *possible*, but I can't get it to work. > > My problem is that I might be using this to put a few thousand entries in > the db every night, and when I last attempted this using a few thousand > insert statements, it was awfully slow (on the order of taking HOURS) You could write a perl script to read the data column by column frm the text file and insert it into the databse using the Pg module. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- Quantum Mechanics is God's version of "Trust me." -BEGIN GEEK CODE BLOCK- Version: 3.12 GAT dpu s:-- a C UL$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++ PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y --END GEEK CODE BLOCK--
Re: [GENERAL] COPY with default values won't work?
On Wed, 24 Mar 1999, Vadim Mikheev wrote: > > Or instead of eating tons of memory inserting all those record in one > I think this was fixed ~ 1.5-2 years ago... I have the same problem in 6.4.2. I splitted COPY into small chunks (about 500 rows) to overcome this. I beleive Jan fixed this in 6.5-beta a month ago. > Vadim > Oleg. Oleg Broytmann http://members.xoom.com/phd2/ [EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN.
