Checking for valid UUID values?

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Checking for valid UUID values?
Date: 2020-07-24 17:02:18
Message-ID: CAOC+FBX34wbYg07mJADrc=cikgGLi1MRZhQ4ZGcHqGkFZSa+uQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi: we have a vendor who despite their best intentions will sometimes
generate invalid UUIDs. I would like to ignore these records.

I am trying to insert their data into my table and with a programming
language I'd use some sort of try/catch to try casting their UUIDs to
actual UUIDs: does PSQL offer any kind way of doing this?

INSERT INTO mytable SELECT uuid FROM vendor WHERE ..... is_valid_uuid(uuid)
IS NOT NULL? Or something?

Which would essentially try/catch casting to UUID and returning NULL if
it's bad. At which point I send a politely worded letter to my vendor.

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jeff Janes 2020-07-24 17:12:24 Re: Encryption in pg_dump
Previous Message Stephen Frost 2020-07-24 13:30:53 Re: questions about point in time recovery of the primary postgres Server