Set Identity_Insert ON/OFF

Moderator: NorbertKrupa

Post Reply
rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Set Identity_Insert ON/OFF

Post by rajasekhart » Wed Apr 25, 2012 11:12 am

Hi,

Can anyone help me whether vertica is having the property of Identity Insertion on/off.
Please let me know.

Thank you,
Rajasekhar.
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: Set Identity_Insert ON/OFF

Post by jpcavanaugh » Tue May 01, 2012 3:40 am

I do not believe you can disable an identity column but a possible way around this is to use a sequence.

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: Set Identity_Insert ON/OFF

Post by rajasekhart » Sat May 05, 2012 11:16 am

Can you please provide an example of that possibility?

Thanks in advance ,
Raj
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Set Identity_Insert ON/OFF

Post by id10t » Sun May 06, 2012 7:25 pm

Hi, Raj!

What do you want? Enumerate on output? Look for function row_number(). Enumerate by insertion, so create timestamp column and... look fo row_number() over ().

Table

Code: Select all

CREATE TABLE public.enumerate
(
    id timestamp NOT NULL,
    column1 varchar(80),
    column2 varchar(80)
);

ALTER TABLE public.enumerate ADD CONSTRAINT C_PRIMARY PRIMARY KEY (id); 
Data:

Code: Select all

copy enumerate (id as getdate(), column1, column2) from stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> foo,bar
>> baz,egg
>> tik,tak
>> qux,rex
>> qux,rex
>> \.
Query:

Code: Select all

select row_number() over (order by id) as '#',column1,column2  from enumerate ;

 # | column1 | column2 
---+---------+---------
 1 | foo     | bar
 2 | baz     | egg
 3 | tik     | tak
 4 | qux     | rex
 5 | qux     | rex
(5 rows)
Test enumeration by insertion:

Code: Select all

test_db=> update enumerate set column1 = 'TOK' where column1 = 'tik';
 OUTPUT 
--------
      1
(1 row)

test_db=> select row_number() over (order by id) as '#',column1,column2  from enumerate ;
 # | column1 | column2 
---+---------+---------
 1 | foo     | bar
 2 | baz     | egg
 3 | qux     | rex
 4 | qux     | rex
 5 | TOK     | tak
(5 rows)

Post Reply

Return to “New to Vertica”