CODEWITHSUNDEEP
postgresqlinsertcreate-table
Prasad
Prasad

Reputation: 175

Add serial number for each id in Postgres

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

Upvotes: 0

Views: 356

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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

Upvotes: 1

Related Questions