From: | Alex Goncharov <alex-goncharov(at)comcast(dot)net> |
---|---|
To: | Florian Pflug <fgp(at)phlo(dot)org> |
Cc: | alex-goncharov(at)comcast(dot)net, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable |
Date: | 2011-10-06 23:42:13 |
Message-ID: | E1RBxZt-000DM9-Qr@hanssachs.home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
,--- 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-goncharov(at)comcast(dot)net --
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Goncharov | 2011-10-07 00:15:36 | Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable |
Previous Message | Bruce Momjian | 2011-10-06 23:09:16 | Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable |