PostgreSQL, reconfigure existing table, changing primary key to type=serial

Question

I have to reconfigure one table with existing data so need advice/example how to do that.

My favourite example table:

DROP TABLE IF EXISTS kalksad1;

CREATE TABLE kalksad1(
kalk_id     int PRIMARY KEY,
brkalk      integer, 
brred       integer, 
description text
);

INSERT INTO kalksad1 VALUES
(12, 2, 5, 'text index 12 doc 2 row 5'),
(26, 2, 1, 'text index 26 doc 2 row 1'),
(30, 2, 2, 'text index 30 doc 2 row 2'),
(32, 4, 1, 'text index 32 doc 4 row 1'),
(36, 1, 1, 'text index 36 doc 1 row 1'),
(37, 1, 2, 'text index 37 doc 1 row 2'),
(38, 5, 1, 'text index 38 doc 5 row 1'),
(39, 5, 2, 'text index 39 doc 5 row 2'),
(42, 2, 3, 'text index 42 doc 2 row 3'),
(43, 2, 4, 'text index 43 doc 2 row 4'),
(46, 3, 1, 'text index 46 doc 3 row 1'),
(47, 3, 2, 'text index 47 doc 3 row 2');

By simple words I need few steps.
1) Add one new column 'kalk_br' of 'int' type. (I know to do that),
2) Copy contents of all cells 'kalk_id' to 'kalk_br'. (I don't know to do that),
3) Change column type for 'kalk_id' from 'int' to 'serial'. (I don't know to do that since 'kalk_id' is PRIMARY KEY).

EDIT by wildplasser's advice: 4) set the current value for the serial to MAX(kalk_id)

Is this possible to do and how? If not, please any idea to solve this differently.

EDIT:
This is I came so far:

/*1. rename kalk_id to kalk_br. 2. create a serial column kalk_id. 3. copy contents from kalk_br to kalk_id.*/

ALTER TABLE kalksad1 RENAME COLUMN kalk_id TO kalk_br;
ALTER TABLE kalksad1 ADD COLUMN kalk_id serial;
UPDATE kalksad1 SET kalk_id = kalk_br;

Now remains:
1) Terminate 'kalk_br' to be primary key.
2) Set 'kalk_id' which is now serial to be primary key with highest value updated as current value.

How about you switch it around? 1. rename kalk_id to kalk_br. 2. create a serial column kalk_id. 3. copy contents from kalk_br to kalk_id. — Wolph
– Wolph, Commented Dec 15, 2013 at 11:31
That sounds OK, Can you provide some code example to do that on showed table? — Wine Too
– Wine Too, Commented Dec 15, 2013 at 11:36
+add: 4) set the current value for the serial to MAX(kalk_id) — wildplasser
– wildplasser, Commented Dec 15, 2013 at 11:37

wildplasser · Accepted Answer · 2013-12-15 14:43:42Z

3

CREATE SEQUENCE kalksad1_kalk_id_seq;

ALTER TABLE kalksad1
        ALTER COLUMN kalk_id SET DEFAULT nextval('kalksad1_kalk_id_seq' )
        ;

ALTER SEQUENCE kalksad1_kalk_id_seq OWNED BY kalksad1.kalk_id;

WITH mx AS (SELECT max(kalk_id) AS mx FROM kalksad1)
SELECT setval( 'kalksad1_kalk_id_seq' , mx.mx)
FROM mx
        ;

   -- Test it ...
INSERT INTO kalksad1(brkalk, brred, description) VALUES (42, 666, 'Tralala' );
SELECT * FROM kalksad1;

Result:

NOTICE:  table "kalksad1" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 12
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval 
--------
     47
(1 row)

INSERT 0 1
 kalk_id | brkalk | brred |        description        
---------+--------+-------+---------------------------
      12 |      2 |     5 | text index 12 doc 2 row 5
      26 |      2 |     1 | text index 26 doc 2 row 1
      30 |      2 |     2 | text index 30 doc 2 row 2
      32 |      4 |     1 | text index 32 doc 4 row 1
      36 |      1 |     1 | text index 36 doc 1 row 1
      37 |      1 |     2 | text index 37 doc 1 row 2
      38 |      5 |     1 | text index 38 doc 5 row 1
      39 |      5 |     2 | text index 39 doc 5 row 2
      42 |      2 |     3 | text index 42 doc 2 row 3
      43 |      2 |     4 | text index 43 doc 2 row 4
      46 |      3 |     1 | text index 46 doc 3 row 1
      47 |      3 |     2 | text index 47 doc 3 row 2
      48 |     42 |   666 | Tralala
(13 rows)

edited Dec 15, 2013 at 14:43

answered Dec 15, 2013 at 12:38

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

2 Comments

Wine Too Over a year ago

Thank you wildplasser. According my few testings that seem's do a job exactly as expected!

Wine Too Over a year ago

Of course it works. I tests it on real data which is huge and procedure pass with no porblems at all. Thanks for updating an answer.

Collectives™ on Stack Overflow

PostgreSQL, reconfigure existing table, changing primary key to type=serial

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related