patternsqlMinor

PostgreSQL predicate `IS OF` and `IS NOT OF` supported or documented?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlpredicatedocumentedandnotsupported

Problem

Reviewing some of the regression tests for PostgreSQL, I can see

SELECT x, x IS OF (text) AS is_text FROM q;

This apparently dates back to 7.3

Add IS OF / IS NOT OF type predicate (Thomas)

Is this form supported. In my own code, I would have written,

pg_typeof(x) = 'text'

But I like IS OF better.

Solution

It's not documented, yet, but it is certainly supported and should be moving forward. It's actually in the SQL 2011 spec as type predicate.

8.19 
Function
Specify a type test.

Format
 ::=
 

 ::=
IS [ NOT ] OF   

 ::=

[ {   }... ]

Code Snippets

8.19 <type predicate>
Function
Specify a type test.

Format
<type predicate> ::=
<row value predicand> <type predicate part 2>

<type predicate part 2> ::=
IS [ NOT ] OF <left paren> <type list> <right paren>

<type list> ::=
<user-defined type specification>
[ { <comma> <user-defined type specification> }... ]

Context

StackExchange Database Administrators Q#181985, answer score: 2

Revisions (0)

No revisions yet.