,--- You/Florian (Fri, 7 Oct 2011 01:00:40 +0200) ----* | 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?
In 'psql', no: I was commenting specifically, and confirming what you said, on your a lot of cases where the database could deduce (quite easily) that a result column cannot be null | 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. Actually, no, I am not missing the huge difference -- again, I was just agreeing with you. Agreeing that there is a lot of cases where the nullability can be trivially deduced, even in 'psql'. (That also meant disagreeing with the message posted before yours.) | 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. Yes. | 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. Right: but for (most?) every SELECT, one can logically deduce whether it can be guaranteed that a given column will never have a NULL value. Since in a given SELECT, the result column are a combination of either other columns, or expressions, including literals. Now, I am not even wondering about a 100% percent reliable determination by a hypothetical 'PQfisnullable(PQresult *r, int idx)'. But if libpq can tell me about column names, types and sizes (PQfname, PQftype, PQfmod), why would it be impossible to have 'PQfisnullable'? Today I tested that it is done in: Oracle, DB2, MySQL, Teradata, Informix, Netezza and Vertica (in many of these via ODBC.) This is conceptually feasible. And in PostgreSQL, this could be done by combining (1) Oid PQftable(const PGresult *res, int column_number); (2) int PQftablecol(const PGresult *res, int column_number); (3) a SQL query of pg_attribute,attnotnull I have not tried this yet, hesitating to walk into a monstrosity and hoping that there is some hidden way to get the information through one of int PQfmod(const PGresult *res, int column_number); int PQgetisnull(const PGresult *res, int row_number, int column_number); (the latter with an odd 'row_number'; I actually tried row_number= 0 and -1, after preparing a statement. No luck.) | 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. Again, combining (1), (2) and (3) above should give a good answer here. | > | 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. Not sure what you mean here: -------------------------------------------------- http://www.postgresql.org/docs/8.4/interactive/ddl-constraints.html#AEN2290: A not-null constraint simply specifies that a column must not assume the null value. CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric ); The NOT NULL constraint has an inverse: the NULL constraint. CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL ); -------------------------------------------------- | | > | 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. You said, "not even in the simplest cases" -- and this is what caused my statement. | I said "result columns", meaning the columns resulting from a SELECT | statement. Then I misunderstood you, indeed -- I thought you included an inquiry about a table. Sorry for the misunderstanding then. | 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. create table t1(nn1 char(1) not null, yn1 char(1) null); create table t2(nn2 char(1) not null, yn2 char(1) null); (may use pg_attribute.attnotnull on t1, t2, is I didn't see the 'create's. Now, for this statement, I can easily identify non-nullable columns. select t1.nn1, -- guaranteed: not null t1.ny1, -- nullable t2.nn2, -- guaranteed: not null t2.ny2 -- nullable from t1, t1; | best regards, | Florian Pflug Thank you -- I appreciate the conversation! -- Alex -- alex-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
