How to COALESCE a timestamp column?
If you want a boolean:
coalesce(grand > current_timestamp, false)
If you need 0 or 1:
coalesce((grand > current_timestamp)::integer, 0)
In your updated question you have an extra comma between the select list and the into
clause
coalesce(vip > current_timestamp, false),
into is_grand, is_vip
Take it out
coalesce(vip > current_timestamp, false)
into is_grand, is_vip
Alexander Farber
/me/likes: Java, С#, Perl, PHP, JavaScript, PostgreSQL, Linux, Azure /me/speaks: German, English, Russian /me/learns: https://github.com/afarber/android-questions https://github.com/afarber/unity-questions https://github.com/afarber/ios-questions
Updated on June 04, 2022Comments
-
Alexander Farber almost 2 years
In PostgreSQL 9.3 I have the following table with 2 timestamps:
create table pref_users ( id varchar(32) primary key, first_name varchar(64) not null, last_name varchar(64), female boolean, avatar varchar(128), city varchar(64), mobile varchar(64), login timestamp default current_timestamp, logout timestamp, last_ip inet, vip timestamp, /* XXX can be NULL */ grand timestamp, /* XXX can be NULL */ mail varchar(256), green integer, red integer, medals integer not null default 0 );
The timestamps
vip
andgrand
indicate if the users of my game have paid for certain privilleges - until those dates.When a user connects to my game server, I call the following procedure with
OUT
parameters:create or replace function pref_get_user_info( IN _id varchar, OUT is_banned boolean, OUT is_grand boolean, OUT is_vip boolean, OUT rep integer ) as $BODY$ begin is_banned := exists(select 1 from pref_ban where id=_id); if is_banned then return; end if; select grand > current_timestamp, vip > current_timestamp, into is_grand is_vip from pref_users where id=_id; if is_grand or is_vip then return; end if; select count(nullif(nice, false)) - count(nullif(nice, true)) into rep from pref_rep where id=_id; end; $BODY$ language plpgsql;
This does work well, but sometimes delivers
NULL
values to my game daemon (to a Perl script):# select * from pref_get_user_info('OK674418426646'); is_banned | is_grand | is_vip | rep -----------+----------+--------+----- f | | | 126 (1 row)
I don't need a
NULL
though (and it prints a warning in my Perl script) - I just need a "true" or "false" values there.So I have tried:
select coalesce(grand, 0) > current_timestamp, coalesce(vip, 0) > current_timestamp, into is_grand is_vip from pref_users where id=_id;
But this gives me error:
# select * from pref_get_user_info('OK674418426646'); ERROR: COALESCE types timestamp without time zone and integer cannot be matched LINE 2: coalesce(grand, 0) > current_timesta... ^ QUERY: select coalesce(grand, 0) > current_timestamp, coalesce(vip, 0) > current_timestamp, is_vip from pref_users where id=_id CONTEXT: PL/pgSQL function pref_get_user_info(character varying) line 9 at SQL statement
So I wonder what to do here please?
Do I really have to
select coalesce(grand, current_timestamp - interval '1 day') > current_timestamp, coalesce(vip, current_timestamp - interval '1 day') > current_timestamp, into is_grand is_vip from pref_users where id=_id;
or is there maybe a nicer way (like maybe "epoch start" or "yesterday")?
UPDATE:
As suggested by Clodoaldo Neto (thanks!) I've tried:
select coalesce(grand > current_timestamp, false), coalesce(vip > current_timestamp, false), into is_grand is_vip from pref_users where id=_id;
but
is_vip
is NULL whenvip
is NULL:# select * from pref_get_user_info('OK674418426646'); is_banned | is_grand | is_vip | rep -----------+----------+--------+----- f | t | | (1 row)
And when I try either of the following I get syntax error:
select coalesce(grand > current_timestamp, false), coalesce(vip > current_timestamp, false), into is_grand, is_vip from pref_users where id=_id; select coalesce(grand > current_timestamp, false), coalesce(vip > current_timestamp, false), into (is_grand, is_vip) from pref_users where id=_id;
How can I
SELECT
into 2 variables at once here?