PostgreSQL – error when performing not in

here is the table:

create table video_call (
timestamp timestamp,
callerid int,
receiverid int,
call_length int);

insert into video_call values
('2018-12-12 01:01:01', 1, 2, 3),
('2019-01-01 01:01:01', 1, 3, 5),
('2019-01-01 01:01:01', 2, 4, 3),
('2019-01-01 01:01:01', 5, 6, 3),
('2019-01-02 01:01:01', 3, 4, 3),
('2019-01-03 01:01:01', 3, 5, 3),
('2019-01-04 01:01:01', 3, 7, 3);

Now I’d like to get the new users on day 2019-01-01. Here is my answer:

select user from
(select distinct callerid as user from video_call where to_char(timestamp, 'YYYY-MM-DD') = '2019-01-01'
union
select distinct receiverid as user from video_call where to_char(timestamp, 'YYYY-MM-DD') = '2019-01-01') as t
where user not in 
(select distinct callerid as user from video_call where to_char(timestamp, 'YYYY-MM-DD') < '2019-01-01'
union
select distinct receiverid as user from video_call where to_char(timestamp, 'YYYY-MM-DD') < '2019-01-01');

then get this error:

ERROR:  operator does not exist: name = integer
LINE 5: where user not in 
                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 250

any idea of this? I already checked that all the subqueries work. Thanks

Answer

user is a reserved word which resolves to the name of the currently logged-in PostgreSQL user. You can see this by doing something like:

select user from
(select distinct callerid as user from video_call where to_char(timestamp, 'YYYY-MM-DD') = '2019-01-01') as t;
  user  
--------
 jjanes
 jjanes
 jjanes

To have it refer to a table column alias spelled “user”, you need to qualify it with a table name, or put it in double quotes, or better yet just spell it differently to start with.

Advertisement

Answer