Add Row Numbers in Postgresql

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, Apr 10 '18 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, Apr 10 '18 at 20:55

klin · Accepted Answer · 2023-05-05T12:00:24.207

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;

Add Row Numbers in Postgresql

1 Answers1