Обсуждение: COPY with default values won't work?

I'm trying to copy data into the following table:

CREATE SEQUENCE seq_account_type_ndx;

CREATE TABLE accounts (
        Account_Type_NDX        int4 not null default
nextval('seq_account_type_ndx'),
        Account_Name            Text
);

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?

This is standard behaviour. DEFAULT is for INSERT only,
when attribute is not specified in INSERT' target list.

Vadim

@#$#!!  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)

BTW: Thanks for the quick response.  I send the mail, answer some messages,
and get a response.  Quicker than a Microsoft $0.95/min help line. :-)

At 01:24 AM 3/24/99, you wrote:
>This is standard behaviour. DEFAULT is for INSERT only,
>when attribute is not specified in INSERT' target list.
>
>Vadim

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)

@#$#!!
-:)

Use BEGIN/END to insert a few thousand rowes in SINGLE transaction.

Vadim

Hello!

On Wed, 24 Mar 1999, Charles Tassell wrote:
> 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)

   DROP INDEX
   BEGIN WORK
   COPY .......
   END
   CREATE INDEX

Oleg.
----
    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
           Programmers don't die, they just GOSUB without RETURN.

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------

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_NDX        int4 not null default
> nextval('seq_account_type_ndx'),
>         Account_Name            Text
> );
>
> 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