Add table data to itself, incrementing timestamp

Question

I have a table with dummy data in it, with 40,000 rows, and a timestamp on each row that increments by a few milliseconds. I want to multiply these rows by, say, 10, each 40,000 rows incrementing by a day, and hour, whatever I set it to be.

Is there a way to select data from a table and then feed it back into itself with one column changed slightly?

FWIW, there are 33 columns on this table.

Any help is appreciated!

score 1 · Accepted Answer · answered Dec 02 '11 at 08:17

1

The mysql code from gustavotkg is along the right lines.

INSERT INTO mytable (event_ts, col1, col2)
SELECT event_ts + interval '1 day', col1, col2
FROM mytable
WHERE event_ts BETWEEN <something> AND <something else>

Repeat with different intervals for multiple copies.

answered Dec 02 '11 at 08:17

Richard Huxton

21,516
3
39
51

Yup! Thanks. I had to do some local tweaks to this due to unique ID constraints and date range constraints but this basically works. – Jon Mitten Dec 02 '11 at 19:03

Erwin Brandstetter · Answer 2 · 2011-12-02T18:46:27.430

It is unclear whether you want to just update the rows or also select them at the same time, or even insert new rows. Updating is very simple:

UPDATE tbl
SET col1 = col1*10
   ,ts   = ts + interval '1 day'

To also return all rows like a SELECT statement would (the updated state!):

UPDATE tbl
SET col1 = col1*10
  , ts   = ts + interval '1 day'
RETURNING *

If you actually want to INSERT new rows with just one column changed and the timestamp changed, and the point is to avoid having to type out all 33 columns, you could:

CREATE TEMP TABLE tbl_tmp AS SELECT * FROM tbl;
UPDATE tbl_tmp SET col1 = col1*10, ts = ts + interval '1 day';
INSERT INTO tbl SELECT * FROM tbl_tmp;
DROP tbl_tmp;

OR somewhat faster with the new writable CTEs in version 9.1:

CREATE TEMP TABLE ON COMMIT DROP tbl_tmp AS SELECT * FROM tbl;
WITH x AS (
    UPDATE tbl_tmp SET col1 = col1*10, ts = ts + interval '1 day'
    RETURNING *
    )
INSERT INTO tbl SELECT * FROM x;
DROP tbl_tmp;

Be sure to have autovacuum running or run VACUUM ANALYZE manually afterwards.

Add table data to itself, incrementing timestamp

2 Answers2