What am I doing wrong in this SELECT statement?

Question

New to postgres and SQL. Have few tables and trying to write common function that can track any UPDATES and capture the details in an audit table.

Referred to response form on another post form Demian Martinez This Approach solves the problem and I don't have to hardcode column names and maintain the function.

However when I try to include the login in an insert statement, as shown, I am getting message indicating some syntax issue in SELECt statement.

insert into request_trail (Requ_Id,Time_stam,Acti,uid,Deta) values ('ST00000011',now(),'U',1234567,
SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each('{"col1": "same", "col2": "prediff", "col3": 1, "col4": false}') AS pre
CROSS JOIN jsonb_each('{"col1": "same", "col2": "postdiff", "col3": 1, "col4": true}') AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
AND pre.key IN ('col1', 'col2'));

ERROR:  syntax error at or near "SELECT"
LINE 2: SELECT pre.key AS columname, pre.value AS prevalue, post.val...

Have no idea what am I doing wrong and how to fix. Can some one advise please?

score 2 · Answer 1 · answered Apr 25 '20 at 07:37

If you want to use INSERT .. SELECT, you can't have the VALUES clause. To use constant values, just add them to the SELECT list.

insert into request_trail (Requ_Id,Time_stam,Acti,uid,Deta,?????) 
SELECT 'ST00000011',
       now(),
       'U',
       1234567, 
       pre.key AS columname, 
       pre.value AS prevalue, 
       post.value AS postvalue
FROM jsonb_each('{"col1": "same", "col2": "prediff", "col3": 1, "col4": false}') AS pre
  CROSS JOIN jsonb_each('{"col1": "same", "col2": "postdiff", "col3": 1, "col4": true}') AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
AND pre.key IN ('col1', 'col2'));

However, the above will still fail, because the SELECT has more columns then the INSERT specifies. You will have to specify the correct target columns where I put the ???

What am I doing wrong in this SELECT statement?

1 Answers1