CODEWITHSUNDEEP
postgresql
Sam Leurs
Sam Leurs

Reputation: 2000

postgreql insert when no row exists

I have a table in postgresql named 'views', containing information about users viewing a classified ad.

CREATE TABLE views (
    view_id uuid DEFAULT random_gen_uuid() NOT NULL,
    user_id uuid NOT NULL,
    ad_id uuid NOT NULL,
    timestamp timestamp with time zone DEFAULT 'NOW()' NOT NULL
);

I want to be able to insert a row for a specific user/ad ONLY when there is no other row 'younger' than 5 minutes. So I want to check if there already is a row with the user ID and the ad ID and where the timestamp is less than 5 minutes old. If so, I want to do something like INSERT... ON CONFLICT DO NOTHING.

Is this possible to do with a UNIQUE constraint? Or do I need a CHECK constraint, or do I have to do a separate query first every time I insert this?

Upvotes: 0

Views: 60

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

You have to do a lookup first, but you can do the lookup and the insert in one statement using something like this:

with invars (user_id, ad_id) as (
  values (?, ?)  -- Pass your two ids in 
)
insert into views (user_id, ad_id)
select user_id, ad_id 
  from invars i
 where not exists (select 1 
                     from views
                    where (user_id, ad_id) = (i.user_id, i.ad_id)
                      and "timestamp" >= now() - interval '5 minutes');

Upvotes: 1

Related Questions