Redshift - inserting into identity column

1
votes

I have created a table as mentioned below.

create table employee (
surrogate_key bigint IDENTITY(1,1), 
first_name varchar(200), 
last_name varchar(200), 
phone_number varchar(200), 
creditcard_number bigint
)

insert into employee values 
('gaurang', 'shah', '356-776-4456', '4716973408090483')

However, following code is giving error.

Error

[Code: 500310, SQL State: 0A000]  [Amazon](500310) Invalid operation: cannot set an identity column to a value;
2
amazon-redshift

2 Answers

1
votes

insert into employee(first_name,last_name,phone_number,creditcard_number)

values('gaurang', 'shah', '356-776-4456', 4716973408090483)

You have to specify column names when identity column present in the table

0
votes

One more option is define surrogate key with default like this

surrogate_key bigint generated by default as IDENTITY(1,1),

Then run this query

insert into employee1

values(default,'gaurang', 'shah', '356-776-4456', 4716973408090483)