CODEWITHSUNDEEP
sqlpostgresql
buddy123
buddy123

Reputation: 6327

How to add column with auto incremented value for added rows in SQL?

I have a main table X with columns a,b which I wish to copy to other table Y. Y has 3 columns: a,b,z where z should be auto incremented - 0,1,2... I want to be able to do

INSERT INTO Y(a,b,z) select a,b from X WHERE ....

so i actually have subquery with only a,b and I want them to be added with values 0,1,2,3... in the new table Y. How do I do that?

Upvotes: 1

Views: 309

Answers (2)

Mzf
Mzf

Reputation: 5260

In MSSQL you would use 

Id       Int          NoNullable Identity(1,1)

For Postgres auroincrement use SERIAL

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You could use row_number(). Subtract one to start at zero:

insert  into y
        (a,b,z) 
select  a
,       b 
,       row_number() over (order by a, b) - 1
from    X

Upvotes: 2

Related Questions