I am on the phone with Eric Ridge of ZomboDB and PGX fame. We chat often on the People, Postgres, Data Discord server (yes you should join) and we have unofficial “we are human so we get on the phone” calls about twice a month. The calls are generally about PostgreSQL and the awesome Open Source projects he is building around our famed database. However, on this call I got a question I don’t normally get: how good is your SQL?
Now this question is not nearly as mundane as you would expect. It is true, I would consider myself a PostgreSQL expert. However, that doesn’t mean I know anything about SQL and in fact I would argue that although I am competent in SQL, I am in no way (nor do I want to be) an expert. My love for PostgreSQL is in designing architecture, enterprise deployments and production class stability. In practice this means that I can design a highly available infrastructure in my sleep but I will be cursing the gods if you try to get me to write a CTE query.
IS OF
The source of his question was that during the development of one of his upcoming Open Source Rust tools for PostgreSQL he ran into “IS OF” and asked if I have ever heard of it. I had not and we went on a mission to find out what IS OF was all about. It appears that the purpose is simple, useful and potentially powerful. I would also like to mention that IS OF appears to be PostgreSQL specific and completely undocumented except in some obscure portions of the code.
postgres=# SELECT 4 IS OF (text);
?column?
----------
f
postgres=# SELECT 'four' IS OF (integer);
?column?
----------
f
postgres=# SELECT 2 IS OF (integer);
?column?
----------
t
As you can see IS OF is used to determine if a value is valid as a particular data type. In these two cases, 4 is not considered text (it would be if you were to use ‘4’), ‘four’ is not of type integer and 2 is of type integer. What could we use this as in a real world example? Would it make sense to extend IS OF abilities?