How to COPY data into tables with default values?

[debfawn]

Hi guys,

I'm testing a simple data load into a table that has a date time field with a default value of sysdate. I want to copy data from a file that contains the column data for the table's columns expect for the date time field. I want that field to simply default to sysdate during the COPY.

Here's my table:

Code: Select all

debfawn=> \d test_load;
                                          List of Fields by Tables
    Schema    |   Table   | Column |    Type     | Size |   Default   | Not Null | Primary Key | Foreign Key
--------------+-----------+--------+-------------+------+-------------+----------+-------------+-------------
 deb_test_01 | test_load | a      | int         |    8 |             | f        | f           |
 deb_test_01 | test_load | b      | varchar(80) |   80 |             | f        | f           |
 deb_test_01 | test_load | c      | timestamp   |    8 | "sysdate"() | f        | f           |
(3 rows)

Here's my data file:

Code: Select all

debfawn=> \! cat test_load.txt
1|DEB
2|FAWN

Here's my COPY command:

Code: Select all

debfawn=> copy test_load from '/usr/home/debfawn/test_load.txt' EXCEPTIONS '/usr/home/debfawn/test_load.log';
 Rows Loaded
-------------
           0
(1 row)

Finally, here are the exceptions:

Code: Select all

debfawn=> \! cat /usr/home/debfawn/test_load.log
COPY: Input record 1 has been rejected (Too few columns found).  Please see /usr/local/data/deb/v_deb_01_node0001_catalog/CopyErrorLogs/test_load-test_load.txt-copy-from-rejected-data, record 1 for the rejected record.
COPY: Input record 2 has been rejected (Too few columns found).  Please see /usr/local/data/deb/v_deb_node0001_catalog/CopyErrorLogs/test_load-test_load.txt-copy-from-rejected-data, record 2 for the rejected record.
COPY: Loaded 0 rows, rejected 2 rows.

Anyone know how I can alter my COPY command so that I can get the c column to default to sysdate?

Thank you

[doug_harmon]

Does this work?:

Code: Select all

copy test_load ( a, b ) from '/usr/home/debfawn/test_load.txt' EXCEPTIONS '/usr/home/debfawn/test_load.log';

[debfawn]

Wow, yeah that worked! Thanks, Doug !!!

Code: Select all

dbadmin=> copy test_load ( a, b ) from '/usr/home/debfawn/test_load.txt' EXCEPTIONS '/usr/home/debfawn/test_load.log';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select * from test_load;
 a |  b   |             c
---+------+----------------------------
 1 | DEB  | 2013-01-30 07:54:19.850456
 2 | FAWN | 2013-01-30 07:54:19.850456
(2 rows)

How'd you figure that out