Add serial number for each id in Postgres

Question

I have to create table in postgres with serno,id,name,address and the serno should be a running number for each id which comes from other table. I have the folowing query

CREATE TABLE Ids (SerNo integer, Id varchar(100),Name varchar(250),Address varchar(500));

INSERT INTO Ids (SerNo,Id,Name,Address) 
VALUES (rank() OVER(ORDER BY "Id"),
(SELECT distinct("Id") from   "Table2"),'John','US');

ERROR:  window functions are not allowed in VALUES
LINE 2: VALUES (rank() OVER(ORDER BY "Id"),

Please correct me

Clodoaldo Neto · Accepted Answer · 2016-08-26 20:08:04Z

1

Use a sequence:

create table Ids (SerNo serial...

insert into Ids (Id,Name,Address) 
select distinct Id, 'John', 'US'
from Table2;

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

answered Aug 26, 2016 at 20:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prasad Over a year ago

Got it !! Thank you.

Collectives™ on Stack Overflow

Add serial number for each id in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related