CURRENT_TIMESTAMP is different between versions

19 views
Skip to first unread message

kevin

unread,
Mar 28, 2023, 1:00:30 PM3/28/23
to pgTAP Users
I’m finding that CURRENT_TIMESTAMP is treated differently between PG v13
& v14. You can see that in these statements that work only on the
commented version and the difference is the quotes:

-- v13, cannot have quotes
--SELECT col_default_is('public', 'flow_states', 'start_dt',
CURRENT_TIMESTAMP,'start_dt default');
-- v14, must have quotes
--SELECT col_default_is('public', 'flow_states', 'start_dt',
'CURRENT_TIMESTAMP', 'start_dt default');

The docs suggest trying something like this, but it fails on both
versions, which makes sense when I think about it because 1 of the
statements is always wrong (just different a different half for each
version).

-- fails on both
--SELECT col_default_is('public', 'flow_states', 'start_dt', CASE WHEN
pg_version_num() >= 140000 THEN 'CURRENT_TIMESTAMP' ELSE
CURRENT_TIMESTAMP END , 'start_dt default');

The only thing I’ve found that works is putting it in a function. Both
of these functions work, but I went with #2 so I can use it in more columns.

/*
CREATE FUNCTION ct() RETURNS TEXT AS $$
DECLARE
  rc TEXT;
BEGIN
  IF (pg_version_num() >= 140000) THEN
    rc := (SELECT col_default_is('public', 'flow_states', 'start_dt',
'CURRENT_TIMESTAMP', 'start_dt default'));
  ELSE
    rc := (SELECT col_default_is('public', 'flow_states', 'start_dt',
CURRENT_TIMESTAMP, 'start_dt default'));
  END IF;
RETURN rc;
END $$ LANGUAGE plpgsql;

select ct();
*/

CREATE FUNCTION ct() RETURNS TEXT AS $$
DECLARE
  rc TEXT;
BEGIN
  IF (pg_version_num() >= 140000) THEN
    rc := 'CURRENT_TIMESTAMP';
  ELSE
    rc := CURRENT_TIMESTAMP;
  END IF;
  RETURN rc;
END
$$ LANGUAGE plpgsql;

select col_default_is('public', 'flow_states', 'start_dt', ct(),
'start_dt default');


->-> Question: Is there any way to do this test without that function?
Or is this something that needs to be in the documentation?

Thanks,
Kevin
Reply all
Reply to author
Forward
0 new messages