postgresql returning updated column

Question

I'd like to log any table. So I need to know what columns changed. Could you give me some tips for hoping result? I only want updated column.

table: test

col1 |col2 | col3
------+------+------
 a    | a    | a
 b    | b    | b

Query:

UPDATE test SET col2='x' WHERE col1='a'RETURNING *;

result:

 col1 | col2 | col3 
------+------+------
 a    | x    | a

expected result:

| col2 | 
+------+
| x    |

Do you want to build an audit trail ? wiki.postgresql.org/wiki/Audit_trigger — Marcello Romani
– Marcello Romani, Commented Jun 11, 2013 at 9:02
Why do you expect a single column when you specify * which means "all columns"? — user330315
– user330315, Commented Jul 10, 2015 at 16:07

Denis de Bernardy · Accepted Answer · 2013-06-11 09:11:21Z

3

Returning * works much like a select statement. You can specify column names, e.g. returning col2.

Also, note Marcello's comment: audits are better done using triggers.

answered Jun 11, 2013 at 9:11

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

1 Answer 1