,--- 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 --
[email protected] --
