Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable - Mailing list pgsql-hackers

,--- 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
thisaudience: 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?)

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