COPY default columns

[nnani]

Hi,

This may sound the simplest of all, but still I am a bit stuck on here

I am trying to use the COPY utility to load a table. Below is the table structure

Code: Select all

=> \d+ ntab;
                                       List of Fields by Tables
 Schema | Table |  Column  |     Type     | Size |   Default   | Not Null | Primary Key | Foreign Key
--------+-------+----------+--------------+------+-------------+----------+-------------+-------------
 tempdb | ntab  | n_id     | int          |    8 |             | t        | f           |
 tempdb | ntab  | n_err_dt | varchar(30)  |   30 |             | t        | f           |
 tempdb | ntab  | m_msg    | varchar(255) |  255 |             | f        | f           |
 tempdb | ntab  | n_rsn    | varchar(200) |  200 |             | f        | f           |
 tempdb | ntab  | load_dt  | date         |    8 | "sysdate"() | t        | f           |
(5 rows)

I have a flat file with records like this

Code: Select all

14|2009-06-25 10:12:22|Active|Cable

Now when I try to execute it, it says few columns found and cannot execute the COPY statement
I don't understand, if the column is stated not null and it is given the default as sysdate(), then why is it not taking the value.
Any workaround for this... Please correct me, If I am understanding it wrong

[JimKnicely]

Hi!

I think you need to specify the columns in your COPY command.

Like this:

Code: Select all

dbadmin-> \d ntab;
                                       List of Fields by Tables
 Schema | Table |  Column  |     Type     | Size |   Default   | Not Null | Primary Key | Foreign Key
--------+-------+----------+--------------+------+-------------+----------+-------------+-------------
 public | ntab  | n_id     | int          |    8 |             | t        | f           |
 public | ntab  | n_err_dt | varchar(30)  |   30 |             | t        | f           |
 public | ntab  | m_msg    | varchar(255) |  255 |             | f        | f           |
 public | ntab  | n_rsn    | varchar(200) |  200 |             | f        | f           |
 public | ntab  | load_dt  | date         |    8 | "sysdate"() | t        | f           |
(5 rows)

Code: Select all

dbadmin=> copy ntab (n_id, n_err_dt, m_msg, n_rsn) from '/home/dbadmin/jim.txt';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from ntab;
 n_id |      n_err_dt       | m_msg  | n_rsn |  load_dt
------+---------------------+--------+-------+------------
   14 | 2009-06-25 10:12:22 | Active | Cable | 2013-06-21
(1 row)

[nnani]

Hi,

It seems you did not use the last column name in your copy command and still it got loaded.
.
Even I am doing the same. Still Its giving me the same error. "few columns found, input record 1 rejected"

[nnani]

I am using this sql for the COPY command

Code: Select all

COPY ntab
(
t1 filler int
,t2 filler varchar(100)
,t3 filler varchar(255)
,t4 filler varchar (100)
,n_err_dt as t2
,m_msg as t3
,n_rsns t4
)
FROM LOCAL /home/nnani/test1.txt
NO ESCAPE
ABORT ON ERROR
DIRECT
STREAM NAME 'TEST1';

this is giving me error "too less columns found"

[JimKnicely]

Hi,

In your original post the n_id column is listed as being NOT NULL.

Are you trying to ignore this column while loading the data? If so, it has to be nullable. The you can ignore it.

Code: Select all

dbadmin=> ALTER TABLE ntab alter column n_id drop not null;
ALTER TABLE

dbadmin=> \d ntab;
                                       List of Fields by Tables
 Schema | Table |  Column  |     Type     | Size |   Default   | Not Null | Primary Key | Foreign Key
--------+-------+----------+--------------+------+-------------+----------+-------------+-------------
 public | ntab  | n_id     | int          |    8 |             | f        | f           |
 public | ntab  | n_err_dt | varchar(30)  |   30 |             | t        | f           |
 public | ntab  | m_msg    | varchar(255) |  255 |             | f        | f           |
 public | ntab  | n_rsn    | varchar(200) |  200 |             | f        | f           |
 public | ntab  | load_dt  | date         |    8 | "sysdate"() | t        | f           |
(5 rows)

dbadmin=> copy ntab (n_id_f filler int, n_err_dt, m_msg, n_rsn) from '/home/dbadmin/jim.txt';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from ntab;
 n_id |      n_err_dt       | m_msg  | n_rsn |  load_dt
------+---------------------+--------+-------+------------
      | 2009-06-25 10:12:22 | Active | Cable | 2013-06-24
(1 row)