Make duplicate row in Postgresql

Question

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.

Define column name explicitly. e.g. INSERT INTO table_name SELECT col2, col3, .... coln FROM table_name WHERE id=255; — mmuzahid
– mmuzahid, Commented Apr 8, 2018 at 8:56
Do you know ahead of time which columns should not be duplicated e.g. the PK column(s), or do you not know it at all (or they could have any name)? In other words, would you be happy with hard coding those PK column names or supplying them as input to a function, or would you not have that information? — 404
– 404, Commented Apr 8, 2018 at 10:05
@eurotrash Yes, I would be happy to write PK columns. It is just "id". Whats the query for this? — Ankit
– Ankit, Commented Apr 8, 2018 at 12:59
Also does that column get a default value (so you could just exclude that column from the insert and it will have a valid id) or do you have to provide a value yourself? — 404
– 404, Commented Apr 8, 2018 at 13:01
@eurotrash It is set as auto increment. If i dont supply the value while inserting record then it pich the next value automatically. — Ankit
– Ankit, Commented Apr 8, 2018 at 13:03

Kaushik Nayak · Accepted Answer · 2018-04-08 10:09:17Z

2

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;

answered Apr 8, 2018 at 10:09

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Ankit Over a year ago

Thanks Kaushik. I just made few changes in script as per my requirement and it is working fine. I used 2nd option.

404 · Accepted Answer · 2018-04-08 13:17:13Z

1

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.

answered Apr 8, 2018 at 13:17

404

8,7622 gold badges33 silver badges53 bronze badges

1 Comment

Ankit Over a year ago

Tnx. Will check it out soon.

Collectives™ on Stack Overflow

Make duplicate row in Postgresql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related