CODEWITHSUNDEEP
amazon-redshift
Gaurang Shah
Gaurang Shah

Reputation: 12910

Redshift - inserting into identity column

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;

Upvotes: 2

Views: 6212

Answers (2)

Amish Shah
Amish Shah

Reputation: 106

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)

Upvotes: 1

Amish Shah
Amish Shah

Reputation: 106

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

Upvotes: 2

Related Questions