Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Loading data into column with default as nextval(seq)

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?

Comments

  • 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.
  • 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!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.