On Oct7, 2011, at 00:02 , Alex Goncharov wrote:
> ,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
> | Sure, but there are still a lot of cases where the database could deduce
> | (quite easily) that a result column cannot be null.
>
> Right. Of course. I can do it in 'psql'.
For the result of an *arbitrary* query?
I think what you are missing is that there is *huge* difference between
tables (as created by CREATE TABLE) and result sets produced by SELECT
statements.
The former can carry all sorts of constraints like NOT NULL, CHECK,
REFERENCES, ..., and their structure as well as the constraints they carry
are stored in the catalog tables in the schema pg_catalog.
The latter cannot carry any constraints, and their meta-data thus consist
simply of a list of column names and types. Their meta-data is also
transient in nature, since it differs for every SELECT you issue.
Views are a kind of mixture between the two - their meta-data isn't any
richer than that of a SELECT statement, but since VIEWs aren't transient
objects like statements, their meta-data *is* reflected in the catalog.
> | Other databases do that - for example, I believe to remember that
> | Microsoft SQL Server preserves NOT NULL constraints if you do
> |
> | CREATE TABLE bar AS SELECT * from foo;
>
> I don't know a database where this would not be true.
Ähm... postgres would be one where the resulting table doesn't have any
NOT NULL columns. Ever.
> | So the question makes perfect sense, and the answer is: No, postgres currently
> | doesn't support that, i.e. doesn't deduce the nullability of result columns,
> | not even in the simplest cases.
>
> You are wrong: as in my original mail, use pg_attribute.attnotnull to
> see why I say this.
Nope, you miss-understood what I said. I said "result columns", meaning the
columns resulting from a SELECT statement. Postgres doesn't deduce the nullability
of these columns. The fact that postgres supports NOT NULL constraints on tables
(which is what pg_attribute.attnotnull is for) really has nothing to do with that.
best regards,
Florian Pflug