Page 1 of 1

Set Identity_Insert ON/OFF

Posted: Wed Apr 25, 2012 11:12 am
by rajasekhart
Hi,

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

Thank you,
Rajasekhar.

Re: Set Identity_Insert ON/OFF

Posted: Tue May 01, 2012 3:40 am
by jpcavanaugh
I do not believe you can disable an identity column but a possible way around this is to use a sequence.

Re: Set Identity_Insert ON/OFF

Posted: Sat May 05, 2012 11:16 am
by rajasekhart
Can you please provide an example of that possibility?

Thanks in advance ,
Raj

Re: Set Identity_Insert ON/OFF

Posted: Sun May 06, 2012 7:25 pm
by id10t
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)