How to update records without using UPDATE query in Postgres

Question

I have a requirement to insert and update records from one table to another. The source table structure with sample records looks like below:

CREATE TABLE BOOKMARK.TEMP_TABLE
(
   systemuid varchar(50) NOT NULL,
   filename varchar(250) NOT NULL,
   mindatetime timestamp,
   maxdatetime timestamp,
   fileid varchar(50),
   batchid varchar(50),
   inserteddatetime timestamp DEFAULT now(),
   updateddatetime timestamp DEFAULT now(),
   file_uuid uuid
);

TEMP TABLE

systemuid       filename            mindatetime              maxdatetime             fileid                        batchid       inserteddatetime               updateddatetime            file_uuid 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10006        monitor_3.dat   2019-05-08 08:29:50.0    2019-07-06 09:49:13.0   1d462091-a582-457b-ab8d-2df76df494b0  76887     2019-08-08 18:17:27.010075     2019-08-08 18:17:27.010075     null   
10006        monitor_3.dat   2019-07-06 09:49:13.001  2019-07-08 16:58:06.0   78fd19c5-67a5-452d-abae-f63dd3237721  76889     2019-08-08 20:04:14.994077     2019-08-08 20:04:14.994077     null   
10006        monitor_3.dat   2019-07-08 16:58:06.001  2019-07-09 17:22:11.0   02b3f634-7cdd-4c70-8503-6f57f4322ed2  76891     2019-08-08 21:40:08.362082     2019-08-08 21:40:08.362082     null   
10006        monitor_3.dat   2019-07-09 17:22:11.001  2019-07-10 16:02:45.0   0c09ea61-1b59-430f-96cd-4c0ae6474c1f  76892     2019-08-08 23:03:04.270083     2019-08-08 23:03:04.270083     null   
10006        monitor_3.dat   2019-07-10 16:02:45.001  2019-07-11 15:56:50.0   f384fb31-a506-4360-818f-b8ce2c612a89  76894     2019-08-09 00:31:20.487717     2019-08-09 00:31:20.487717     null 
10006        monitor_3.dat   2019-05-08 08:29:50.0    2019-08-14 15:59:27.0   190f2a13-c6e2-4803-8b86-c2964d9a95f0  77721     2019-08-19 15:08:41.167284     2019-08-19 15:08:41.167284     e7f7be43-118a-487b-adfa-1d5471b32197     
12345        monitor_7.dat   2019-02-10 16:02:45.001  2019-02-11 14:56:50.0   02b3f634-a506-4360-818f-b8ce2c612a89  76894     2019-08-09 00:31:20.487717     2019-08-09 00:31:20.487717     null 
12345        monitor_7.dat   2019-03-08 08:29:50.0    2019-08-14 15:59:27.0   8bc2752a-f542-4803-8b86-c2964d9a95f0  77721     2019-08-19 15:08:41.167284     2019-08-19 15:08:41.167284     null

The output table should be like:

AUDIT TABLE

systemuid       filename            mindatetime              maxdatetime             fileid                        batchid       inserteddatetime               updateddatetime            updated_fileid 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10006        monitor_3.dat   2019-05-08 08:29:50.0    2019-07-06 09:49:13.0   1d462091-a582-457b-ab8d-2df76df494b0  76887     2019-08-08 18:17:27.010075     2019-08-08 18:17:27.010075     e7f7be43-118a-487b-adfa-1d5471b32197   
10006        monitor_3.dat   2019-07-06 09:49:13.001  2019-07-08 16:58:06.0   78fd19c5-67a5-452d-abae-f63dd3237721  76889     2019-08-08 20:04:14.994077     2019-08-08 20:04:14.994077     e7f7be43-118a-487b-adfa-1d5471b32197   
10006        monitor_3.dat   2019-07-08 16:58:06.001  2019-07-09 17:22:11.0   02b3f634-7cdd-4c70-8503-6f57f4322ed2  76891     2019-08-08 21:40:08.362082     2019-08-08 21:40:08.362082     e7f7be43-118a-487b-adfa-1d5471b32197   
10006        monitor_3.dat   2019-07-09 17:22:11.001  2019-07-10 16:02:45.0   0c09ea61-1b59-430f-96cd-4c0ae6474c1f  76892     2019-08-08 23:03:04.270083     2019-08-08 23:03:04.270083     e7f7be43-118a-487b-adfa-1d5471b32197   
10006        monitor_3.dat   2019-07-10 16:02:45.001  2019-07-11 15:56:50.0   f384fb31-a506-4360-818f-b8ce2c612a89  76894     2019-08-09 00:31:20.487717     2019-08-09 00:31:20.487717     e7f7be43-118a-487b-adfa-1d5471b32197      
12345        monitor_7.dat   2019-02-10 16:02:45.001  2019-02-11 14:56:50.0   02b3f634-a506-4360-818f-b8ce2c612a89  76894     2019-08-09 00:31:20.487717     2019-08-09 00:31:20.487717     null 
12345        monitor_7.dat   2019-03-08 08:29:50.0    2019-08-14 15:59:27.0   8bc2752a-f542-4803-8b86-c2964d9a95f0  77721     2019-08-19 15:08:41.167284     2019-08-19 15:08:41.167284     null

My insert query and update queries are:

INSERT INTO BOOKMARK_AUDIT.AUDIT_TABLE(systemuid, filename, mindatetime, maxdatetime, fileid, batchid, inserteddatetime, updateddatetime, updated_fileid) SELECT systemuid, filename, mindatetime, maxdatetime, fileid, batchid, inserteddatetime, updateddatetime, file_uuid FROM BOOKMARK.TEMP_TABLE WHERE file_uuid IS NULL; 
UPDATE BOOKMARK_AUDIT.AUDIT_TABLE t1 SET updated_fileid = (SELECT DISTINCT(file_uuid) FROM BOOKMARK.TEMP_TABLE t2 WHERE t1.systemuid = t2.systemuid AND t1.filename = t2.filename ORDER BY t2.file_uuid LIMIT 1);

I have to first insert all the records which are having file_uuid as null from TEMP table to AUDIT table.

Then I am doing an update on those records with new file_uuid from TEMP table which is notnull. Currently, there are millions of records in the TEMP table. The insert query takes a few minutes to execute while the update statement takes hours to run. I wanted to know if there is an alternative to achieve this need without using the UPDATE query?

I have provided the table structure in the post. It is same as the audit table except for the column name difference for file_uuid. — Abhilash
– Abhilash, Commented Aug 26, 2019 at 6:00

Barbaros Özhan · Accepted Answer · 2019-08-26 06:17:38Z

1

You'd better directly to use INSERT statement without UPDATE by using a correlated subselect. Since INSERT is mostly less expensive operation than UPDATE as cost.

INSERT INTO AUDIT_TABLE(systemuid, filename, mindatetime, maxdatetime, fileid, 
                        batchid, inserteddatetime, updateddatetime, updated_fileid)
SELECT systemuid, filename, mindatetime, maxdatetime, fileid, 
       batchid, inserteddatetime, updateddatetime, 
      (SELECT DISTINCT(file_uuid) 
         FROM TEMP_TABLE t2 
        WHERE t1.systemuid = t2.systemuid 
          AND t1.filename = t2.filename 
        ORDER BY t2.file_uuid 
        LIMIT 1)
  FROM TEMP_TABLE t1
 WHERE file_uuid IS NULL;

answered Aug 26, 2019 at 6:17

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2019-08-26 06:16:35Z

0

That would be

INSERT INTO BOOKMARK_AUDIT.AUDIT_TABLE
   (systemuid, filename, mindatetime,
    maxdatetime, fileid, batchid,
   inserteddatetime, updateddatetime, updated_fileid)
SELECT DISTINCT ON (t.systemuid, t.filename)
       t.systemuid, t.filename, t.mindatetime,
       t.maxdatetime, t.fileid, t.batchid,
       t.inserteddatetime, t.updateddatetime, a.file_uuid
FROM BOOKMARK.TEMP_TABLE AS t
   LEFT JOIN BOOKMARK.TEMP_TABLE AS a USING (systemuid, filename)
WHERE t.file_uuid IS NULL
ORDER BY a.file_uuid, t.mindatetime,
       t.maxdatetime, t.fileid, t.batchid,
       t.inserteddatetime, t.updateddatetime;

You might need an index for efficiency.

answered Aug 26, 2019 at 6:16

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

How to update records without using UPDATE query in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related