Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable - Mailing list 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 aresult 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.  selectt1.nn1, -- guaranteed: not nullt1.ny1, --
nullablet2.nn2,-- guaranteed: not nullt2.ny2  -- nullable from t1, t1;    
 
| best regards,
| Florian Pflug

Thank you -- I appreciate the conversation!

-- Alex -- alex-goncharov@comcast.net --