CODEWITHSUNDEEP
postgresqlduplicates
Ankit
Ankit

Reputation: 69

Make duplicate row in Postgresql

I am writing migration script to migrate database. I have to duplicate the row by incrementing primary key considering that different database can have n number of different columns in the table. I can't write each and every column in query. If i simply just copy the row then, I am getting duplicate key error.

Query: INSERT INTO table_name SELECT * FROM table_name WHERE id=255;

ERROR:  duplicate key value violates unique constraint "table_name_pkey"
DETAIL:  Key (id)=(255) already exist

Here, It's good that I don't have to mention all column names. I can select all columns by giving *. But, same time I am also getting duplicate key error.

What's the solution of this problem? Any help would be appreciated. Thanks in advance.

Upvotes: 0

Views: 6905

Answers (2)

404
404

Reputation: 8572

This is just a DO block but you could create a function that takes things like the table name etc as parameters.

Setup:

CREATE TABLE public.t1 (a TEXT, b TEXT, c TEXT, id SERIAL PRIMARY KEY, e TEXT, f TEXT);
INSERT INTO public.t1 (e) VALUES ('x'), ('y'), ('z');

Code to duplicate values without the primary key column:

DO $$
DECLARE
        _table_schema   TEXT := 'public';
        _table_name     TEXT := 't1';
        _pk_column_name TEXT := 'id';
        _columns        TEXT;
BEGIN
        SELECT STRING_AGG(column_name, ',')
                INTO _columns
        FROM information_schema.columns
        WHERE table_name = _table_name
        AND table_schema = _table_schema
        AND column_name <> _pk_column_name;

        EXECUTE FORMAT('INSERT INTO %1$s.%2$s (%3$s) SELECT %3$s FROM %1$s.%2$s', _table_schema, _table_name, _columns);
END $$

The query it creates and runs is: INSERT INTO public.t1 (a,b,c,e,f) SELECT a,b,c,e,f FROM public.t1. It's selected all the columns apart from the PK one. You could put this code in a function and use it for any table you wanted, or just use it like this and edit it for whatever table.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

If you are willing to type all column names, you may write

INSERT INTO table_name (
    pri_key
    ,col2
    ,col3
    )
SELECT (
        SELECT MAX(pri_key) + 1
        FROM table_name
        )
    ,col2
    ,col3
FROM table_name
WHERE id = 255;

Other option (without typing all columns , but you know the primary key ) is to CREATE a temp table, update it and re-insert within a transaction.

BEGIN;
CREATE TEMP TABLE temp_tab ON COMMIT DROP AS SELECT * FROM table_name WHERE id=255;
UPDATE temp_tab SET pri_key_col = ( select MAX(pri_key_col) + 1 FROM table_name );
INSERT INTO table_name select * FROM temp_tab;
COMMIT;

Upvotes: 2

Related Questions