Can UPDATE use column and value lists like INSERT with a VALUES expression?-CodePudding

I have developed a lambda function that is connected to Postgres database. This lambda function is connected to AWS Kinesis stream which sends records and my lambda dumps these records into Postgres. I am inserting records like this:

insert_statement = "insert into public.ctr (%s) values %s;"
cur.execute(insert_statement, (AsIs(','.join(columns)), tuple(values)))

It creates my Insert query like this:

insert into public.ctr (arn,aftercontactworkduration,aftercontactworkendtimestamp,aftercontactworkstarttimestamp,agentconnectionattempts,agentinteractionduration,answeringmachinedetectionstatus,channel,connectedtoagenttimestamp,connectedtosystemtimestamp,customerendpointaddress,customerendpointtype,customerholdduration,dequeuetimestamp,disconnectreason,disconnecttimestamp,queueduration,enqueuetimestamp,hierarchygroups,initialcontactid,initiationmethod,initiationtimestamp,instancearn,lastupdatetimestamp,longestholdduration,nextcontactid,numberofholds,previouscontactid,queuearn,queuename,recordingdeletionreason,recordinglocation,recordingstatus,recordingtype,routingprofilearn,routingprofilename,scheduledtimestamp,systemendpointaddress,systemendpointtype,transfercompletedtimestamp,transferredtoendpoint,username,voiceidresult,id)
values ('arn:aws:connect:us-east-1:7479230:instance/84ecefa9-04/agent/439b-4040', 4, '2022-08-02T10:47:09Z', '2022-08-02T10:47:05Z', 2, 8, NULL, 'VOICE', '2022-08-02T10:46:35Z', '2022-08-02T10:45:51Z', 'Anonymous', 'TELEPHONE_NUMBER', 22, '2022-08-02T10:46:35Z', 'THIRD_PARTY_DISCONNECT', '2022-08-02T10:47:18Z', 29, '2022-08-02T10:46:05Z', NULL, NULL, 'INBOUND', '2022-08-02T10:45:51Z', 'arn:aws:connect:us-east-693930:instance/84ecefa9-184', '2022-08-02T10:48:25Z', 22, 'b4-4091-b3f4-0ea02e', 1, NULL, 'arn:aws:connect:us-east-1:747926693930:instance/8f28f7e4', 'BasicQueue', NULL, 'new-bucket-for-metrics/s/2022/08/02/577214dc802T10:46_UTC.wav', 'AVAILABLE', 'AUDIO', 'arn:aws:connect:us-east-1:7493930:instance/84ecefa9-184a-400f-babf-3288d7c8fd04/routing-profile/837a145d-baaa-4911e7e4d816', 'Basic Routing Profile', NULL, ' 185487', 'TELEPHONE_NUMBER', '2022-08-02T10:47:05Z', NULL, 'ahm', NULL, '5-4d60-94cb-2cc443db4c3f');

This is working fine.

For all I know, the UPDATE statement syntax is different:

Update table name
set column = val, column2 = val2, ..., columnN=valN;

Is there any way to put all column names at once, as well as values, like in the INSERT statement?

CodePudding user response：

Can look like this:

UPDATE public.ctr
SET   (arn,aftercontactworkduration,aftercontactworkendtimestamp, ...)
    = ('arn:aws:connect:us-east-1:7479230:instance/84ecefa9-04/agent/439b-4040', 4, '2022-08-02T10:47:09Z',  ...)
WHERE  ???;  -- filter target row(s)!