PostgreSQL - error when performing not in

Question

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

jjanes · Accepted Answer · 2020-04-08 22:00:02Z

1

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.

answered Apr 8, 2020 at 22:00

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-04-09 16:13:22Z

0

I would suggest writing this as:

select v.userid
from video_call vc cross join lateral
     (values (vc.callerid), (vc.receiverid)) v(userid)
where timestamp < '2019-01-02'
group by v.userid
having min(timestamp) >= '2019-01-01';

It seems like a much simpler approach and should get around your syntax issues.

edited Apr 9, 2020 at 16:13

answered Apr 8, 2020 at 16:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

JamesWang Over a year ago

This approach is very cool! but I'm not familiar with lateral. Is that an in-built function?

JamesWang Over a year ago

when I run your solution, I get this error: ERROR: syntax error at or near "user" LINE 3: (values (vc.callerid), (vc.receiverid)) v(user) ^ SQL state: 42601 Character: 100 then I change user to users and it works!

JamesWang Over a year ago

Did some research and looks like lateral is SQL foreach and it can access to the query result preceding it. and values can create tmp table without insert. Learned a lot from your solution. Thanks @Gordon Linoff!

Gordon Linoff Over a year ago

user is a Postgres reserved word. You shouldn't use it as a column name. I just figured it was a name used only in the question.

Collectives™ on Stack Overflow

PostgreSQL - error when performing not in

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related