Postgresql – Fastest way to check if record is related to a particular user in postgres

performancepostgresqlpostgresql-10query-performance

While implementing a sharelikes table I added a procedure to add a new record for a like.

I wanted to add a check before insert that the item being liked does not belong to the users who submitted the like request. Given function parameters Pshareid and Puserid I used the following conditional in the procedure

SELECT INTO checkuser true FROM ks.shares 
WHERE id = Pshareid AND userid = Puserid limit 1;
IF checkuser THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

In the select described above the record will almost always not exist.

Is that more efficient than a SELECT on the share id to return the record (if it exists which is very likely at the point of select) and then to check the userid of the returned record?

SELECT userid INTO checkuser FROM ks.shares 
WHERE id = Pshareid limit 1;
IF checkuser = Puserid THEN
    -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

Best Answer

You can use PERFORM 1 test with a control statement using FOUND special variable. Your query will get like this;

PERFORM 1 
    FROM ks.shares 
    WHERE
        id = Pshareid AND userid = Puserid
    LIMIT 1;

IF FOUND THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

Referenced sections;

Executing a Command With No Result

Available Diagnostics Items (special variable named FOUND)

Related Question