kevin
unread,Mar 28, 2023, 1:00:30 PM3/28/23Sign in to reply to author
Sign in to forward
You do not have permission to delete messages in this group
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
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