CODEWITHSUNDEEP
postgresqlunique
swapna
swapna

Reputation: 41

How to increment the duplicate id's in postgres

I have a table that needs id to be unique, the id column in my table has values like sourcename_number

id
noaa_1
noaa_2
noaa_3
noaa_3
noaa_3
meteo_1
meteo_2
meteo_2

I wanted to change it as

noaa_1
noaa_2
noaa_3
noaa_4
noaa_5
meteo_1
meteo_2  
meteo_3

so that there are no duplication in the column

Upvotes: 1

Views: 729

Answers (1)

wildplasser
wildplasser

Reputation: 44250

  • You need a regex to match the -
  • you need a row_number() to enumerate within the string classes
  • You need an identity column to order by (this could be the ctid system column)
  • you need a self-join subquery to refer to the 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

Output:

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)

Upvotes: 1

Related Questions