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)