Set Identity_Insert ON/OFF

Forum for the new Vertica user

Moderator: norbertk

Set Identity_Insert ON/OFF

Postby 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.
rajasekhart
Intermediate
Intermediate
 
Posts: 91
Joined: Wed Apr 25, 2012 8:10 am

Sponsored Links

Advertisements

Advertisements
 

Re: Set Identity_Insert ON/OFF

Postby 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.
jpcavanaugh
Intermediate
Intermediate
 
Posts: 150
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York

Re: Set Identity_Insert ON/OFF

Postby rajasekhart » Sat May 05, 2012 11:16 am

Can you please provide an example of that possibility?

Thanks in advance ,
Raj
rajasekhart
Intermediate
Intermediate
 
Posts: 91
Joined: Wed Apr 25, 2012 8:10 am

Re: Set Identity_Insert ON/OFF

Postby sKwa » 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)
I'm not a computer expert - if you take anything I write as a suggestion then: DO NOT come to me crying it is my fault that YOU ruined your system!
sKwa
GURU
GURU
 
Posts: 766
Joined: Mon Apr 16, 2012 2:44 pm


Return to New to Vertica

Who is online

Users browsing this forum: No registered users and 1 guest