CODEWITHSUNDEEP
postgresql
Richard
Richard

Reputation: 65600

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

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.

Upvotes: 5

Views: 2478

Answers (1)

klin
klin

Reputation: 121889

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';

Upvotes: 5

Related Questions