PostgreSQL，重新配置现有表，将主键更改为type = serial [英] PostgreSQL, reconfigure existing table, changing primary key to type=serial

问题描述

我必须使用现有数据重新配置一个表，因此需要建议/示例操作方法。

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

我最喜欢的示例表：

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');

简单的话，我只需要几个步骤。

1）添加一个新列'int'类型的'kalk_br'。 （我知道这样做），

2）将所有单元格 kalk_id的内容复制到 kalk_br。 （我不知道这样做），

3）将 kalk_id的列类型从 int更改为 serial。 （由于'kalk_id'是主键，因此我不知道这样做。）

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).

通过wildplasser的建议进行编辑：
4）设置当前值串行到MAX（kalk_id）

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.

编辑：

这是我到目前为止：

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;

现在仍然存在：

1）终止'kalk_br'作为主键。

2）将现在已序列化的'kalk_id'设置为主键，并将最高值更新为当前值。

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.

推荐答案

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;

结果：

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)

这篇关于PostgreSQL，重新配置现有表，将主键更改为type = serial的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！