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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers