CODEWITHSUNDEEP
sqlpostgresql
osh
osh

Reputation: 1261

How to recreate primary after dropping primary key column?

I would like to recreate a primary key column in pg 8.4. But the query i am trying does not work ( it does not execute actually):

update beta
set id=rown
from 
    (select row_number() as rown 
    from beta as b over (order by b.id) -- b.id is null on all rows
    ) q;

Upvotes: 1

Views: 65

Answers (2)

AmaChefe
AmaChefe

Reputation: 405

You should use the ALTER TABLE command

ALTER TABLE beta ADD PRIMARY KEY (id);

This link should help http://www.postgresql.org/docs/current/static/sql-altertable.html

Upvotes: 0

Tony
Tony

Reputation: 17647

Have you tried this solution? Always Backup Everything first.

--Approach 2: Closer to Hubert's Examples
--Advantage: Easy to read - intuitive, doesn't rely on a primary key
--Disadvantage: Creates temp junk in the db 
--      which means reusing in same session you must drop
--  and using in nested subquery results may be unpredictable

CREATE TEMP sequence temp_seq;
SELECT nextval('temp_seq') As row_number, oldtable.*
FROM (SELECT * FROM beta) As oldtable;

http://www.postgresonline.com/journal/archives/79-Simulating-Row-Number-in-PostgreSQL-Pre-8.4.html

Upvotes: 2

Related Questions