Postgres: Create duplicates of existing rows, changing one value?

Question

I am working in Postgres 9.4. I have a table that looks like this:

     Column      │         Type         │                             Modifiers
─────────────────┼──────────────────────┼───────────────────────
 id              │ integer              │ not null default 
 total_list_size │ integer              │ not null
 date            │ date                 │ not null
 pct_id          │ character varying(3) │

I want to take all values where date='2015-09-01', and create identical new entries with the date 2015-10-01.

How can I best do this?

I can get the list of values to copy with SELECT * from mytable WHERE date='2015-09-01', but I'm not sure what to do after that.

Is id column serial?

klin
– klin

2016-02-05 11:02:26 +00:00
Commented Feb 5, 2016 at 11:02 — klin
– klin, Commented Feb 5, 2016 at 11:02

klin · Accepted Answer · 2016-02-05 11:06:06Z

5

If the column id is serial then

INSERT INTO mytable (total_list_size, date, pct_id)
SELECT total_list_size, '2015-10-01', pct_id
FROM mytable 
WHERE date = '2015-09-01';

else, if you want the ids to be duplicated:

INSERT INTO mytable (id, total_list_size, date, pct_id)
SELECT id, total_list_size, '2015-10-01', pct_id
FROM mytable 
WHERE date = '2015-09-01';

answered Feb 5, 2016 at 11:06

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Postgres: Create duplicates of existing rows, changing one value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related