Re: Cannot declare record members NOT NULL

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> Firstly, the output of most queries is of a type not represented
> anywhere in the catalogs. It's mostly going to be an undeclared record
> whose members are listed in pg_type. So using pg_attribute for anything
> like this is probably completely wrong.

Right --- it's incapable of working for any query column that's not a
syntactically-trivial reference to a table column (which is the basis
of the OP's original complaint). The fact that PG even attempts to
report that much is just to satisfy some rather limited requirements
of the JDBC spec.

> If I were writing it I would ignore the attisnull flag altogether and
> assume that any column can be NULL. If you like you could use the
> typisnull column in pg_type, that *is* enforced since that's an actual
> constraint on the type.

Unfortunately that won't go far either. typisnull could only be true
for a domain type, and the SELECT-output code reports the base type
not the domain type of any domain column.

Another little problem is that not-null-constrained domains don't
actually work, if by "work" you mean that a column putatively of such
a type can never contain any nulls. The counterexample here is a LEFT
JOIN with such a column on the right side. The SQL spec is silent on
what to do in such a case, but PG just goes ahead and performs the left
join. I'm of the opinion that not-null-constrained domains were simply
a Bad Idea that should never have got into the spec at all.

Bottom line is that you should probably never assume that a query result
column can't be null.

regards, tom lane