Putting timestamp in a COMMENT ON TABLE

Question

I'm regularly re-creating a table in PostgreSQL (9.4.1), much like this:

DROP TABLE IF EXISTS test.foo;
CREATE TABLE test.foo AS
  SELECT * FROM test.dagi_kommune
  WHERE ST_Area(wkb_geometry) < 500;

I would like to add a comment to the table, stating when the table was created. There are no problem creating a basic comment, like this:

COMMENT ON TABLE test.foo IS 'Table create date: ';

And I can also generate a independent time stamp, like this:

SELECT to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS');

But if I try to put the time stamp into the comment, like this:

COMMENT ON TABLE test.foo IS to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS');

I get the following response:

ERROR:  syntax error at or near "to_char"
LINE 10: COMMENT ON TABLE test.foo IS to_char(LOCALTIMESTAMP, 'YYYY-M...
                                  ^

********** Error **********

ERROR: syntax error at or near "to_char"
SQL state: 42601
Character: 276

How can I get a current 'date and time' stamped into the table's comment?

Primrose

Answer 1

You have to build and execute the statement as dynamic SQL.

DO
$do$
BEGIN
EXECUTE 'COMMENT ON TABLE b2 IS ''Table create date: '
     || to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS')
     || '''';
END
$do$

Plain concatenation is safe in this case, for unsafe input, use format() accordingly. See:

• Error when setting n_distinct using a plpgsql variable
• Table name as a PostgreSQL function parameter
• To convert from Python arrays to PostgreSQL quickly?

Line breaks are optional. The same as one-liner for scripting:

DO $do$BEGIN EXECUTE 'COMMENT ON TABLE b2 IS ''Table create date: ' || to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS') || ''''; END $do$;

Vanden

Thanks. It's sort of clumsy, like in too many lines, for the use I had in mind. It would considerably clutter my SQL scripts. But now I know how to do it. – Primrose

Answer 2

If you will be executing your commands using psql the following is a clean and concise method using psql variables to add a timestamp:

SELECT current_timestamp AS now
\gset
COMMENT ON TABLE test.foo IS :'now';

My preferred way to add additional text to the comment is as follows, using \set to concatenate:

SELECT current_timestamp AS now
\gset
\set comment :now 'text'
COMMENT ON TABLE test.foo IS :'comment';

I have not found an effective way to concatenate another string in the COMMENT command, since it expects one string literal.

Gambill

Thanks for your a reply, but the project was finished some years back... – Primrose

Understood. It was intended more for those who arrive here in the future. – Gambill

Can i concatenate this with some more text? Like 'Current time was: variable' – Garcia

Answer 3

Enhancing Spencer's comment above:

SELECT current_timestamp as dump_time
\gset
COMMENT ON SCHEMA my_schema IS 'Latest dump at: ':'dump_time';

Garcia

This adds an extra single quote, resulting in Latest dump at: 'YYYY-MM-DD. It isn't clear why it works, but I don't think it's being parsed the way you expect. – Gambill