I’ve got two sets of data in postgres, the first (mistakenly) uses a bigserial as the key:
<code>CREATE TABLE info_tbl (
id bigserial NOT NULL,
"uuid" uuid NOT NULL,
"time_stamp" timestamp NOT NULL,
"json_thing" jsonb NOT NULL,
CONSTRAINT info_tbl_pkey PRIMARY KEY (id)
);
</code>
<code>CREATE TABLE info_tbl (
id bigserial NOT NULL,
"uuid" uuid NOT NULL,
"time_stamp" timestamp NOT NULL,
"json_thing" jsonb NOT NULL,
CONSTRAINT info_tbl_pkey PRIMARY KEY (id)
);
</code>
CREATE TABLE info_tbl (
id bigserial NOT NULL,
"uuid" uuid NOT NULL,
"time_stamp" timestamp NOT NULL,
"json_thing" jsonb NOT NULL,
CONSTRAINT info_tbl_pkey PRIMARY KEY (id)
);
The correct table is generated as below (has the uuid as the primary key):
<code>CREATE TABLE info_tbl_correct (
"uuid" uuid NOT NULL,
"time_stamp" timestamp NOT NULL,
"json_thing" jsonb NOT NULL,
CONSTRAINT info_tbl_correct_pkey PRIMARY KEY (uuid)
);
</code>
<code>CREATE TABLE info_tbl_correct (
"uuid" uuid NOT NULL,
"time_stamp" timestamp NOT NULL,
"json_thing" jsonb NOT NULL,
CONSTRAINT info_tbl_correct_pkey PRIMARY KEY (uuid)
);
</code>
CREATE TABLE info_tbl_correct (
"uuid" uuid NOT NULL,
"time_stamp" timestamp NOT NULL,
"json_thing" jsonb NOT NULL,
CONSTRAINT info_tbl_correct_pkey PRIMARY KEY (uuid)
);
I’ve been trying to generate the posgres code to copy the old data from info_tbl into info_tbl_correct, however I’m coming unstuck as when it detects a conflict I want to only copy across data that has a newer timestamp.
<code>INSERT INTO info_tbl_correct
("uuid", "time_stamp", "json_thing")
SELECT src."uuid", src."time_stamp", src."json_thing"
FROM info_tbl AS src
ON CONFLICT ("uuid")
DO UPDATE
SET "time_stamp"=EXCLUDED."time_stamp", "json_thing"=EXCLUDED."json_thing";
</code>
<code>INSERT INTO info_tbl_correct
("uuid", "time_stamp", "json_thing")
SELECT src."uuid", src."time_stamp", src."json_thing"
FROM info_tbl AS src
ON CONFLICT ("uuid")
DO UPDATE
SET "time_stamp"=EXCLUDED."time_stamp", "json_thing"=EXCLUDED."json_thing";
</code>
INSERT INTO info_tbl_correct
("uuid", "time_stamp", "json_thing")
SELECT src."uuid", src."time_stamp", src."json_thing"
FROM info_tbl AS src
ON CONFLICT ("uuid")
DO UPDATE
SET "time_stamp"=EXCLUDED."time_stamp", "json_thing"=EXCLUDED."json_thing";
Can I get postgres to support two conflict conditions, or can I sort the data as it is being read from info_tbl? (This would mean that the data will always be newer as it gets read from old->new).