PostgreSQL UUID date type

I'm building a platform with a PostgreSQL database (first time) but I've experience with Oracle and MySQL databases for a few years now.

My question is about the UUID data type in Postgres. I am using an UUIDv4 uuid to indentify a record in multiple tables, so the request to /users/2df2ab0c-bf4c-4eb5-9119-c37aa6c6b172 will respond with the user that has that UUID. I also have an auto increment ID field for indexing.

My query is just a select with a where clause on UUID. But when the user enters an invalid UUID like this 2df2ab0c-bf4c-4eb5-9119-c37aa6c6b17 (without the last 2) then the database responds with this error: Invalid input syntax for UUID.

I was wondering why it returned this because when you select on a integer-type with a string-type it does work.

Now I need to set a middleware/check on each route that has an UUID-type parameter in it because otherwise the server would crash.

Btw I'm using Flask 0.12 (Python) and PostgreSQL 9.6

Solution

UUID as defined by RFC 4122, ISO/IEC 9834-8:2005... is a 128-bit quantity ... written as a sequence of lower-case hexadecimal digits... for a total of 32 digits representing the 128 bits. (Postgresql Docs)

There is no conversion from a 31 hex digits text to a 128-bit UUID (sorry). You have some options:

Convert to ::text on your query (not really recommended, because you'd be converting every row, every time).
```
SELECT * FROM my_table WHERE my_uuid::TEXT = 'invalid uid';
```
Don't store it as a UUID type. If you don't want / need UUID semantics, store it as a varchar.
Check your customer input. (My recommendation). Conceptually, this is no different from asking for someone's age and getting 'ABC' as the response.

Postgres allows upper/lower case, and is flexible about use of hyphens, so a pre-check is simply strip the hyphens, lowercase, count [0-9[a-f] & if == 32, you have a workable UUID. Otherwise, rather than telling your user "not found", you can tell them, "not a UUID", which is probably more user-friendly.