Add Row Numbers in Postgresql [duplicate]

Question

I have two questions. Both of these are for 1 table in a database.

I have a table "performance".

Name Info 
x    12.5
y    9
z    10

How can I add a column Index in the table, as shown below? My desired output is -

Index Name Info
0     x    12.5
1     y    9
2     z    10
.......

Assume that I have thousands of rows just like above in 1 table.

How can I insert row numbers in each row in postgresql database? The second questions is how can I read the Index column one by one and print it on the screen?

I am using Python to interact with this database.

I want to use a function using row_number() to generate numbers and insert a column in the database tables.

SELECT ROW_NUMBER() OVER (ORDER BY name), Name, Info FROM yourtable should do the trick. Just guessing on your ORDER BY — JNevill
– JNevill, Commented Apr 10, 2018 at 20:51
This would do the trick but it won't change the table. I want to insert an additional columns "Index" with the numbers. Please provide a solution for that! — New User
– New User, Commented Apr 10, 2018 at 20:55

klin · Accepted Answer · 2023-05-05 12:00:24Z

16

You can add the column with the command alter table.

Use serial pseudotype in a single alter table statement, example:

create table performance(name text, info numeric);
insert into performance values
    ('x', 12.5),
    ('y', 9),
    ('z', 10);

alter table performance add id serial;

select *
from performance;

 name | info | id 
------+------+----
 x    | 12.5 |  1
 y    |    9 |  2
 z    |   10 |  3
(3 rows)

If you want the new column to be auto-increment, leave the table as is. If you do not want this, drop the sequence in this way:

alter table performance alter id drop default;
drop sequence performance_id_seq;

Note that Postgres 10 introduced the syntax using generated as identity that is more compliant with SQL standards:

alter table performance add id integer generated always as identity;
-- optionally
-- alter table performance alter id drop identity;

edited May 5, 2023 at 12:00

answered Apr 10, 2018 at 23:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Add Row Numbers in Postgresql [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related