Loading data into column with default as nextval(seq)

K__1

I would like to know if its possible to use the COPY statement to load data into a table that has a column with a default value setup as nextval(seq). Ex: table (i int default nextval('seq_name'), j int, k varchar) I would like to insert data into this table using the copy statement. An example file could be 1, 'syz' 2, 'abc' 3, 'ert' So at the end I would want data in the table to be like 1,1,'syz' 2,2,'abc' 3,3'ert' And No, I don't want to use identity for this case. It has to be a sequence. Does copy allow you to to do this? Also, is there a way to call the nextval funciton from within the copy statement?

Navin_C

Hi Karan,

You can follow any of two approaches:

Firs Approach: Use nextval function in COPY command

Creating Table:

create table navin.seq_test(
seq_id int,
file_id int,
file_value varchar(20)
)

Creating Sequence:

CREATE SEQUENCE seq_for_test START 1

Using COPY:

copy navin.seq_test(
seq_id as seq_for_test.nextval,
file_id,
file_value
) from '/home/dbadmin/navin/test_seq.txt' delimiter ',';

Checking Records:

nnani=> select * from navin.seq_test;   seq_id | file_id | file_value
--------+---------+------------
      3 |       3 |  'ert'
      2 |       2 |  'abc'
      1 |       1 |  'syz'
(3 rows)      

Second Approach : Using default value as nextval in table column

Creating sequence:

CREATE SEQUENCE seq_for_test_default START 1 no CACHE

Creating table:

create table navin.seq_test_default(
seq_id int default seq_for_test_default.nextval,
file_id int,
file_value varchar(20)
)

Using COPY:

copy navin.seq_test_default(
file_id,
file_value
) from '/home/dbadmin/navin/test_seq.txt' delimiter ',';

Checking Records:

nnani=>  select * from navin.seq_test_default;   seq_id | file_id | file_value
--------+---------+------------
      3 |       3 |  'ert'
      2 |       2 |  'abc'
      1 |       1 |  'syz'
(3 rows)  

Hope this helps.

K__1

Thanks Navin! . I tried the same thing on Friday but some reason it didn't work for me. I was probably missing something. Anyways, it worked this time. Thanks again for your help!