Loading data into column with default as nextval(seq)
+1I 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?
+2Hi Karan,
You can follow any of two approaches:
Firs Approach: Use nextval function in COPY command
Creating Table:
Creating sequence:
Hope this helps.
You can follow any of two approaches:
Firs Approach: Use nextval function in COPY command
Creating Table:
create table navin.seq_test(Creating Sequence:
seq_id int,
file_id int,
file_value varchar(20)
)
CREATE SEQUENCE seq_for_test START 1Using COPY:
copy navin.seq_test(Checking Records:
seq_id as seq_for_test.nextval,
file_id,
file_value
) from '/home/dbadmin/navin/test_seq.txt' delimiter ',';
nnani=> select * from navin.seq_test; seq_id | file_id | file_valueSecond Approach : Using default value as nextval in table column
--------+---------+------------
3 | 3 | 'ert'
2 | 2 | 'abc'
1 | 1 | 'syz'
(3 rows)
Creating sequence:
CREATE SEQUENCE seq_for_test_default START 1 no CACHECreating table:
create table navin.seq_test_default(Using COPY:
seq_id int default seq_for_test_default.nextval,
file_id int,
file_value varchar(20)
)
copy navin.seq_test_default(Checking Records:
file_id,
file_value
) from '/home/dbadmin/navin/test_seq.txt' delimiter ',';
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.
+1Thanks 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!
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
