,--- 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.


| 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:


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 
| > | doesn't support that, i.e. doesn't deduce the nullability of result 
| > | 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.
        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:

Reply via email to