时间戳

首页 > 解决方案 > 如何在postgres中增加重复的ID

postgresql - 如何在postgres中增加重复的ID

问题描述

我有一个需要 id 唯一的表，我的表中的 id 列具有类似 sourcename_number 的值

id
noaa_1
noaa_2
noaa_3
noaa_3
noaa_3
meteo_1
meteo_2
meteo_2

我想把它改成

noaa_1
noaa_2
noaa_3
noaa_4
noaa_5
meteo_1
meteo_2  
meteo_3

使列中没有重复

标签： postgresqlunique

解决方案

  • 您需要一个正则表达式来匹配-
  • 你需要在字符串类row_number()中枚举
  • 您需要一个标识列来排序（这可能是ctid系统列）
  • 您需要一个自连接子查询来引用row_number()
\i tmp.sql

CREATE TABLE ugly_duck
        ( id_name text
        );
INSERT INTO ugly_duck(id_name) VALUES
 ( 'noaa_1' ) , ( 'noaa_2' ) , ( 'noaa_3' ) , ( 'noaa_3' ) , ( 'noaa_3' )
, ( 'meteo_1' ) , ( 'meteo_2' ) , ( 'meteo_2' )
        ;

SELECT * FROM ugly_duck;

        -- create a surrogate unique key
ALTER TABLE ugly_duck ADD COLUMN seq serial UNIQUE;

UPDATE ugly_duck dst
SET id_name = src.prefix || src.rn::integer
FROM ( SELECT seq
        , substring( id_name FROM '.*_') AS prefix
        , row_number() OVER (PARTITION BY substring( id_name FROM '.*_') ORDER BY seq) rn
        FROM ugly_duck
        ) src
WHERE src.seq=dst.seq
        ;

SELECT * FROM ugly_duck;

        -- remove the surrogate key
ALTER TABLE ugly_duck DROP COLUMN seq;

        -- create the natural key
ALTER TABLE ugly_duck ADD PRIMARY KEY (id_name);


SELECT * FROM ugly_duck;

\d+ ugly_duck

输出：

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 8
 id_name 
---------
 noaa_1
 noaa_2
 noaa_3
 noaa_3
 noaa_3
 meteo_1
 meteo_2
 meteo_2
(8 rows)

ALTER TABLE
UPDATE 8
 id_name | seq 
---------+-----
 meteo_1 |   6
 meteo_2 |   7
 meteo_3 |   8
 noaa_1  |   1
 noaa_2  |   2
 noaa_3  |   3
 noaa_4  |   4
 noaa_5  |   5
(8 rows)

ALTER TABLE
ALTER TABLE
 id_name 
---------
 meteo_1
 meteo_2
 meteo_3
 noaa_1
 noaa_2
 noaa_3
 noaa_4
 noaa_5
(8 rows)

                                  Table "tmp.ugly_duck"
 Column  | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+------+-----------+----------+---------+----------+--------------+-------------
 id_name | text |           | not null |         | extended |              | 
Indexes:
    "ugly_duck_pkey" PRIMARY KEY, btree (id_name)

推荐阅读