Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

On Thu, Oct 6, 2011 at 5:02 PM, Alex Goncharov
<alex-goncharov@comcast.net> wrote:
> ,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
> | My understanding is that libpq does not allow one to find if a result
> | set column is nullable.
> ,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
> | why aren't you using PQgetisnull()?
> ,--- I/Alex (Thu, 06 Oct 2011 16:22:28 -0400) ----*
> | This function is not about the nullability of a column but rather
> | about the value in a result set cell:
> |      int PQgetisnull(const PGresult *res, int row_number, int column_number);
> | Notice the 'row_number'.
> ,--- Merlin Moncure (Thu, 6 Oct 2011 15:38:59 -0500) ----*
> | right -- get it.  well, your question is doesn't make sense then --
>
> What?..
>
> * It makes complete logical sense to ask a question if a result set
>  column may ever have a NULL cell.
>
> * It can be done for a table using pg_attribute.attnotnull.
>
> * It can be done, at the C API level, in a wide variety of other
>  databases, including the two most often mentioned in this audience:
>  Oracle (through and OCI call) and MySQL (at least through ODBC.)
>
> | any column can be transformed in ad hoc query, so it only makes sense
> | to test individual values post query..
>
> What query?
>
> Look at the subject line: it mentioned PQdescribePrepared.
>
> I execute PQprepare, and then PQdescribePrepared -- I never fetch the
> data.  When the statement is described, plenty information can be
> obtained about the columns -- but not its nullability (what I wanted
> to be confirmed or denied -- for libpq API.)
>
> | btw, if you don't like querying system catalogs, check out
> | information_schema.columns.
>
> Than was not my question, right?  (What difference is there between
> using pg_X tables of information_schema?)
>
> ,--- 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'.
>
> | 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.
>
> | 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.
>
> ,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
> | hm, good point.  not sure how it's useful though.  I suppose an
> | application could leverage that for validation purposes, but that's a
> | stretch I think.
> `--------------------------------------------------------*
>
> Thanks for sharing your knowledge of applications.
>
> (Look, I appreciate anybody's reply and readiness to help, but if you
> have a limited expertise in the subject area, why bother replying?)


Well, admittedly, perhaps my response was hastily written.  But try to
understand the zen of things around here: often if you
propose/gripe/suggest something, you'll get a challenge back which is
really fishing for more detail.  It's not personal.  By the way, you
still haven't explained use cases.  You can always talk
hypotheticals...'other people do it' is not a standard for inclusion
of a feature (although it can be).  I've been coding against libpq for
years and years and have never needed to test for nullability, so
that's where my skepticism comes from.

merlin