How can I add a table column at the first position?

Question

In my PostgreSQL database:

select index from sample;
index
---------
1
2
3
1
2

I want to place the index as below:

select index from sample;
index
---------
1
2
3
4
5

Saddam Khan provided a way to solve it:

alter table sample add column sort_id serial;
-- Drop column index from sample table.
alter table sample drop column index;
-- Rename column sort_id to index in sample table
alter table sample rename column sort_id to index;
-- Add Primary Key.
alter table sample add primary key(index);

But now,the index column is at the right side (end of the table). How can I place it at the left side?

 select * from sample;
 ticker |   roe   | income  | index 
--------+---------+---------+-------
 000820 | 2347.63 |  120.16 |     1
 300071 |  676.52 | 1035.38 |     2
 002188 |  289.69 |  273.95 |     3
 600734 |  151.58 |  921.82 |     4
 000523 |  139.94 | 2585.55 |     5
 600078 |  138.28 | 3333.41 |     6

Answer 1

You can change the column to an identity column and then update the values based on the underlying sequence.

-- required for an identity column
alter table sample
    alter column "index" set not null;

-- add the identity attribute - recommended over the proprietary "serial"
alter table sample
    alter column "index" add generated by default as identity;

Now a sequence was generated which can be used to update the existing values from to make them unique:

update sample
   set "index" = nextval(pg_get_serial_sequence('sample', 'index'));

Then make the column the primary key:

alter table sample add primary key ("index");

Answer 2

Add Primary Key ...

Primary Key fields are intended to be unique, not sequential. If you have any kind of processing that relies on something being "ordered", do not rely on the Primary Key to provide it.

Can you imagine the chaos if your Bank renumbered people's Checking Accounts every time someone else closed their account? It's a very, very Bad Idea. Also, as soon as you have Foreign Keys referencing these Primary Keys, the database really should prevent you from doing this (and no; "cascading" changes is not the answer. The amount of work that the database has to do to make such changes grows exponentially with every new referring table and all the rows in them).

the index column is at the right-side ...

Why do you feel this matters?
Every single SELECT statement you write should specify the columns that it wants, in the order it wants them. Never rely on the physical table layout because, as you've discovered, it can change.

Databases are inherently share Entities and you never know who [else] is going to be doing what in (or to?) them.

Answer 3

New columns are added at the end of the column list with ALTER TABLE. You need to recreate the table to get the desired order of columns.
Or UPDATE the existing column in place, which is slghtly tricky in the presence of a UNIQUE or PRIMARY KEY constraint (while doable).

Either is expensive (for big tables) and blocking. The first option might be even cheaper overall, though more invasive. Typically a problem for DBs with concurrent access. Not a problem if you are the only user.

If you are going to write a new table, do that instead of all you are showing above.

Any internal references to the old table have to be dropped and recreated. Like foreign keys, views etc.

ALTER TABLE sample RENAME TO sample_old;

-- drop all indexes and constraints on old table to free up names
-- drop all referencing objects

CREATE TABLE sample (
  index integer GENERATED ALWAYS AS IDENTITY
, ticker text  -- use your actual data types
, roe numeric
, income numeric
);

INSERT INTO sample
      (ticker, roe, income)
SELECT ticker, roe, income
FROM   sample_old
-- no ORDER BY ??
;

ALTER TABLE sample ADD PRIMARY KEY (index);

-- recreate all indexes and constraints on new table
-- recreate all referencing objects

DROP TABLE sample_old;  -- after making sure

The new IDENTITY column is filled with auto-incremented numbers from its attached SEQUENCE automatically if not targeted in the INSERT statement. See:

• Auto increment table column