CREATE TABLE LIKE doesn't preserve IDENTITY

Kris_Peeters

I tried the following: drop table if exists public.test; drop table if exists public.copytest; create table public.test(id IDENTITY(1,1), name varchar(80)); insert into public.test(name) VALUES ('bla'); create table public.copytest like public.test; insert into public.copytest(name) VALUES ('bla'); I get an error at the last statement because it needs the id column. Somehow, IDENTITY is not copied. Is there a way around this?

colin_loghin

CREATE TABLE X LIKE Y is a new construct and probably does not support sequences. This should be indicated to Vertica support. Hopefully they will see this thread and acknowledge. As such, avoid the LIKE syntax and spell out the DDL definition for public.copytest. You are guaranteed then to have the identity columns in the new table ready for use.

Kris_Peeters

Yup, that's what I did. It's just a longer route to achieve the same thing. Would be convenient if the LIKE statement worked.

colin_loghin

On the same note, create table B like A does not preserve constraints . Is this a bug that is going to get fixed in 6.1.3 or future version ? Please advise. 

[Deleted User]

[Deleted User]

You are correct that create table like doesn't preserve Identity and I will log an defect in our system regarding this. 
You can workaround the issue by creating a sequence 

Using your example above.
drop table if exists public.test; 
drop table if exists public.copytest; 

create table public.test(id IDENTITY(1,1), name varchar(80));
insert into public.test(name) VALUES ('bla'); 
create table public.copytest like public.test;

create sequence s minvalue 1 maxvalue 100 increment 1 cache 1;
alter taBLE copytest  alter column id set default s.nextval;
insert into public.copytest(name) values('bla');

select * from copytest;
select * from test;

Colin_Loghin_1

Nice. Great workaround with the use of the sequence. Could you also comment on the recent post of not copying the table constraints as well using the create table like syntax ?

Perhaps attach to the same JIRA in the database ?

[Deleted User]

Yes, that is basically what the defect already says. I have logged the defect for the it not copying the constraint using the create table like. As you stated using a create like is the issue and it is converting the identity to an int not null column. Using the standard create table DDL will give you the expected results but as previously stated using like is a faster way.