0

I use PostgreSQL and I have a table 'PERSON' in schema 'public' that looks like this:

    +----+-------------+-------+----------------------------+
    | id | internal_id | name  | created                    |
    +----+-------------+-------+----------------------------+
    | 1  | P0001-XX00  |  Bob  |  2021-05-24 22:10:01.93025 |
    +----+-------------+-------+----------------------------+
    | 2  | P0001-CX00  |  Tom  |  2021-06-27 22:10:01.93025 |
    +----+-------------+-------+----------------------------+
    | 3  | P0002-XX00  |  Anna |  2021-05-24 22:10:01.93025 |    
    +----+-------------+-------+----------------------------+

id -> bigint; internal_id -> character varying; name -> character varying; created  -> timestamp without timezone

I need to write procedure that delete those records that are older than fixed timestamp, for example: now(). But as soon as such an old record has been found, I need to check if there are other records in the table which are not old yet and with the same first 5 characters in internal_id as the found old record. If there are such records, then I should not delete the old record.

So I wrote the following procedure with plpgsql and it seems to work:

BEGIN
DELETE FROM public."PERSON" AS t1
   WHERE t1.created < now()
   AND NOT EXISTS
     (
      SELECT 
      FROM   public."PERSON" AS t2 
      WHERE  left(t2.internal_id, 5) = left(t1.internal_id, 5) 
      AND    t2.created >= now()
     );
COMMIT;
END;

Questions:

  1. Could it have been made more correct or prettier or cleaner? Perhaps, instead of using the left() function, it was necessary to use LIKE, or, in principle, to do it somehow differently?

  2. Do you think this procedure has normal performance or it can be improved?

Thank you in advance!

1
  • 1
    "Could it have been made more correct?" - only you can tell whether it works or not. "Do you think this procedure has normal performance?" - yes. Is it fast enough for you? Then you're done. Otherwise, please post the query execution plan and what indices you have on your table. Commented May 25, 2021 at 22:31

1 Answer 1

1

That should work just fine.

For good performance, create an index:

CREATE INDEX ON public."PERSON" (left(internal_id, 5), created);
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for the answer, will make an index!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.