Martijn van Oosterhout <kleptog@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
