Обсуждение: Cannot declare record members NOT NULL

Hi,

I am not sure if this qualifies as a bug report or a feature request,
but I don't see any way to tell Postgresql that the members of a record
cannot be NULL.  This causes all kinds of problems when this record
is used to declare the return type of a function.  Suppose I had the
following table: (note that all columns are NOT NULL)

CREATE TABLE movies
        (
        movie_id        int4 UNIQUE NOT NULL,
        movie_name      text NOT NULL,
        PRIMARY KEY (movie_id)
        );


Suppose also that I didn't want the clients to query the table directly,
but instead they have to go through a function "get_movies" which returned
a record of type "get_movies_t":

CREATE TYPE get_movies_t AS
    (
    movie_id                int4,
    movie_name              text
    );


CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies;
$$;


The problem is that Postgresql tells the client that the function returns
two columns, both of which can be NULL, and this makes a mess on the
client side.  Is there anyway I can tell Postgresql that the columns of
get_movies_t are NOT NULL?

If this is (yet another) defect in the SQL standard, can someone suggest
an alternative that would get around it?

Thanks for the help!
C.S.



      ____________________________________________________________________________________
Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get listings, and more!
http://tv.yahoo.com/collections/3658

On 9/12/07, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
> Thanks for the help!

Not really following you, but try these:

CREATE OR REPLACE FUNCTION GET_MOVIES ()
RETURNS SETOF MOVIES
LANGUAGE SQL STABLE
AS
$$
SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES;
$$;

-- OR --

CREATE OR REPLACE FUNCTION GET_MOVIES (OUT MOVIE_ID INT4, OUT MOVIE_NAME TEXT)
RETURNS SETOF RECORD
LANGUAGE SQL STABLE
AS
$$
SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES;
$$;

Why do you create an extra type for that?
Just have your method return "movies"
i.e.

CREATE FUNCTION get_movies ()
RETURNS SETOF movies
...
...

HTH

Uwe


On Wednesday 12 September 2007, Cultural Sublimation wrote:
> Hi,
>
> I am not sure if this qualifies as a bug report or a feature request,
> but I don't see any way to tell Postgresql that the members of a record
> cannot be NULL.  This causes all kinds of problems when this record
> is used to declare the return type of a function.  Suppose I had the
> following table: (note that all columns are NOT NULL)
>
> CREATE TABLE movies
>         (
>         movie_id        int4 UNIQUE NOT NULL,
>         movie_name      text NOT NULL,
>         PRIMARY KEY (movie_id)
>         );
>
>
> Suppose also that I didn't want the clients to query the table directly,
> but instead they have to go through a function "get_movies" which returned
> a record of type "get_movies_t":
>
> CREATE TYPE get_movies_t AS
>     (
>     movie_id                int4,
>     movie_name              text
>     );
>
>
> CREATE FUNCTION get_movies ()
> RETURNS SETOF get_movies_t
> LANGUAGE sql STABLE
> AS
> $$
> SELECT movie_id, movie_name FROM movies;
> $$;
>
>
> The problem is that Postgresql tells the client that the function returns
> two columns, both of which can be NULL, and this makes a mess on the
> client side.  Is there anyway I can tell Postgresql that the columns of
> get_movies_t are NOT NULL?
>
> If this is (yet another) defect in the SQL standard, can someone suggest
> an alternative that would get around it?
>
> Thanks for the help!
> C.S.
>
>
>
>
> ___________________________________________________________________________
>_________ Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get
> listings, and more! http://tv.yahoo.com/collections/3658
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/



--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

> Why do you create an extra type for that?
> Just have your method return "movies"

Hi,

Thanks for the answer.  The simple example obfuscates the fact that in reality
the table has a few extra columns that are omitted from get_movies_t.
Therefore, I cannot return "movies".

However, your answer did give me an idea:  instead of declaring "get_movies_t"
as a record, I declare it as dummy table, and return that (see code at the
end).
This works, though it is *very* ugly.  Any other ideas?

Thanks,
C.S.


CREATE TABLE get_movies_t
(
movie_id                int4 NOT NULL,
movie_name              text NOT NULL
);

CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies;
$$;





      ____________________________________________________________________________________
Shape Yahoo! in your own image.  Join our Network Research Panel today!
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 

Hi again,

> However, your answer did give me an idea:  instead of declaring
> "get_movies_t" as a record, I declare it as dummy table, and return
> that (see code at the end).
> This works, though it is *very* ugly.  Any other ideas?

My apologies, but it turns out that this solution doesn't work after
all. Postgresql still insists that movie_id and movie_name are NULL,
though I explicitly declared them as NOT NULL:

> CREATE TABLE get_movies_t
> (
> movie_id                int4 NOT NULL,
> movie_name              text NOT NULL
> );
>
> CREATE FUNCTION get_movies ()
> RETURNS SETOF get_movies_t
> LANGUAGE sql STABLE
> AS
> $$
> SELECT movie_id, movie_name FROM movies;
> $$;


This bug seems to obvious to have been generally missed.  I have to
wonder if it is not a bug in my particular build of Postgresql -- I am
running version 8.2.4 straight from the Ubuntu repositories (package
version 8.2.4-1~edgy1).

Could someone else try the code and check if Postgresql returns
the correct type?

Thanks,
C.S.



      ____________________________________________________________________________________
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz

I haven't tried it with a view yet - so this may or may not work. But try
giving it a shot by declaring a view

create view vmovies as
select movie_id,movie_text from movies

and let your function return setof vmovies

Maybe that works - I think it should.

Uwe


On Wednesday 12 September 2007, Cultural Sublimation wrote:
> > Why do you create an extra type for that?
> > Just have your method return "movies"
>
> Hi,
>
> Thanks for the answer.  The simple example obfuscates the fact that in
> reality the table has a few extra columns that are omitted from
> get_movies_t. Therefore, I cannot return "movies".
>
> However, your answer did give me an idea:  instead of declaring
> "get_movies_t" as a record, I declare it as dummy table, and return that
> (see code at the end).
> This works, though it is *very* ugly.  Any other ideas?
>
> Thanks,
> C.S.
>
>
> CREATE TABLE get_movies_t
> (
> movie_id                int4 NOT NULL,
> movie_name              text NOT NULL
> );
>
> CREATE FUNCTION get_movies ()
> RETURNS SETOF get_movies_t
> LANGUAGE sql STABLE
> AS
> $$
> SELECT movie_id, movie_name FROM movies;
> $$;
>
>
>
>
>
>
> ___________________________________________________________________________
>_________ Shape Yahoo! in your own image.  Join our Network Research Panel
> today!   http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/



--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

Cultural Sublimation escreveu:
> Hi,
>
> I am not sure if this qualifies as a bug report or a feature request,
> but I don't see any way to tell Postgresql that the members of a record
> cannot be NULL.  This causes all kinds of problems when this record
> is used to declare the return type of a function.  Suppose I had the
> following table: (note that all columns are NOT NULL)
>
> CREATE TABLE movies
>         (
>         movie_id        int4 UNIQUE NOT NULL,
>         movie_name      text NOT NULL,
>         PRIMARY KEY (movie_id)
>         );
>
>
> Suppose also that I didn't want the clients to query the table directly,
> but instead they have to go through a function "get_movies" which returned
> a record of type "get_movies_t":
>
> CREATE TYPE get_movies_t AS
>     (
>     movie_id                int4,
>     movie_name              text
>     );
>
>
> CREATE FUNCTION get_movies ()
> RETURNS SETOF get_movies_t
> LANGUAGE sql STABLE
> AS
> $$
> SELECT movie_id, movie_name FROM movies;
> $$;
>
>
> The problem is that Postgresql tells the client that the function returns
> two columns, both of which can be NULL, and this makes a mess on the
> client side.  Is there anyway I can tell Postgresql that the columns of
> get_movies_t are NOT NULL?
>
> If this is (yet another) defect in the SQL standard, can someone suggest
> an alternative that would get around it?
>



CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies
  WHERE movie_id NOT NULL AND movie_name NOT NULL;
$$

Osvaldo

> CREATE FUNCTION get_movies ()
> RETURNS SETOF get_movies_t
> LANGUAGE sql STABLE
> AS
> $$
> SELECT movie_id, movie_name FROM movies
>   WHERE movie_id NOT NULL AND movie_name NOT NULL;
> $$

Hey,

Thanks for the suggestion.  Unfortunately, it still doesn't work.
Here is what Postgresql is telling the client for two different
statements, one using a direct SELECT, the other using the get_movies
function:

SELECT movie_id, movie_name FROM movies;
=> returns a SETOF of (int4 NOT NULL, text NOT NULL)

SELECT movie_id, movie_name FROM get_movies ();
=> returns a SETOF of (int4 NULL, text NULL)


One note: I know this information because the client is using the
"describe" feature of Postgresql to retrieve the types returned
by a statement.

So, is this a bug or what?

Cheers,
C.S.



      ____________________________________________________________________________________
Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get listings, and more!
http://tv.yahoo.com/collections/3658

>
> I haven't tried it with a view yet - so this may or may not work. But try
> giving it a shot by declaring a view
>
> create view vmovies as
> select movie_id,movie_text from movies
>
> and let your function return setof vmovies
>
> Maybe that works - I think it should.

Hey,

Thanks for the help.  But no, not even by declaring a view it works.
It follows pretty much the same pattern that I just described in
response to Osvaldo Kussama's message.  If you do a "naked" SELECT
on the movies table, Postgresql correctly tells the client that
the types are NOT NULL.  However, if you do the same via the
function get_movies, the types are transformed into NULL.
This is some very odd behaviour...

Cheers,
C.S.

P.S. The code using the view:


SELECT movie_id, movie_name FROM movies;
=> returns a SETOF of (int4 NOT NULL, text NOT NULL)

SELECT movie_id, movie_name FROM get_movies ();
=> returns a SETOF of (int4 NULL, text NULL)


CREATE TABLE movies
        (
        movie_id        int4 UNIQUE NOT NULL,
        movie_name      text NOT NULL,
        PRIMARY KEY (movie_id)
        );

CREATE VIEW view_get_movies AS
        SELECT movie_id, movie_name FROM movies;

CREATE FUNCTION get_movies ()
RETURNS SETOF view_get_movies
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies;
$$;



      ____________________________________________________________________________________
Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV.
http://tv.yahoo.com/

--- Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
> > CREATE FUNCTION get_movies ()
> > RETURNS SETOF get_movies_t
> > LANGUAGE sql STABLE
> > AS
> > $$
> > SELECT movie_id, movie_name FROM movies
> >   WHERE movie_id NOT NULL AND movie_name NOT NULL;
> > $$
>
>
> SELECT movie_id, movie_name FROM get_movies ();
> => returns a SETOF of (int4 NULL, text NULL)

I don't know if this will work, but here is another idea:

SELECT movie_id, movie_name
  FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );

Regards,
Richard Broersma Jr.

> I don't know if this will work, but here is another idea:
>
> SELECT movie_id, movie_name
>   FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
>

Hi,

Nope.  That's not even valid syntax...
But thanks for effort, anyway!

Cheers,
C.S.




____________________________________________________________________________________
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&sid=396545469

--- Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:

> > I don't know if this will work, but here is another idea:
> >
> > SELECT movie_id, movie_name
> >   FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
> >
>
> Hi,
>
> Nope.  That's not even valid syntax...

It isn't valid SQL spec syntax but it is a Postgresql-ism for functions return types:
http://www.postgresql.org/docs/8.2/interactive/sql-select.html

notice this "from-type" listing for functions.

Regards,
Richard Broersma Jr.

Cultural Sublimation <cultural_sublimation@yahoo.com> writes:
> This bug seems to obvious to have been generally missed.

It's not a bug: there is no mechanism enforcing that the result of a
function can't be NULL.

For functions returning scalars you can get the effect by declaring the
result as being of a domain with a not null constraint, but those
things are actually quite unpleasant to use for anything other than
a shorthand for making a table constraint.  Check the archives for loud
gripes from people who tried to use not-null-constrained domains in
plpgsql since plpgsql started to actually enforce that.  (Some of them
seemed to have this curious idea that the constraint should only be
enforced when it was convenient for them ... and not, say, instantly
when the variable was declared.  But I digress.)

In any case, it appears to me that your gripe has little to do with
whether there's actually any enforcement of the not-null condition,
and much to do with whether some unspecified client-side software
thinks the query result column is guaranteed not null.  Most likely
you're going to have to fix the client-side software, because it's
extrapolating things that Postgres does not tell it.

            regards, tom lane

Richard Broersma Jr wrote:
>
> --- Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
>
> > > I don't know if this will work, but here is another idea:
> > >
> > > SELECT movie_id, movie_name
> > >   FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
> > >
> >
> > Hi,
> >
> > Nope.  That's not even valid syntax...
>
> It isn't valid SQL spec syntax but it is a Postgresql-ism for functions return types:
> http://www.postgresql.org/docs/8.2/interactive/sql-select.html
>
> notice this "from-type" listing for functions.

Yeah, but it doesn't allow the NOT NULL specification.

I don't think there's any way to do what the OP wants ATM.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

On Wed, Sep 12, 2007 at 02:22:46PM -0700, Cultural Sublimation wrote:
> SELECT movie_id, movie_name FROM get_movies ();
> => returns a SETOF of (int4 NULL, text NULL)

I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:

> One note: I know this information because the client is using the
> "describe" feature of Postgresql to retrieve the types returned
> by a statement.

The describe clearly can't tell if the result is always going to be
NULL or not.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Hi Tom,

> In any case, it appears to me that your gripe has little to do with
> whether there's actually any enforcement of the not-null condition,
> and much to do with whether some unspecified client-side software
> thinks the query result column is guaranteed not null.  Most likely
> you're going to have to fix the client-side software, because it's
> extrapolating things that Postgres does not tell it.

That's not entirely true.  The client software obtains the type
information by querying Postgresql, namely by checking the attnotnull
column in the pg_attribute catalog.  Therefore, this is not an
inference error on the client side, but instead a case of Postgresql
providing wrong information.

Well, we could argue all day on whether this is a bug or a feature,
but the fact is that it is a huge setback for me.  I wanted my clients
to access the database indirectly, via a function such as get_movies,
but this problem makes that impossible.

I also thought that instead of using a function, I could create a view,
and the clients could use it to access the database.  But there's also
a problem with this solution: as far as I'm aware, views in Postgresql
cannot accept parameters.  While the simple example does not need
parametrized views, the real world app would need them.

So, barring functions and views, is there any other way to encapsulate
the inner workings of a query away from clients?

Thanks again for your help,
C.S.





____________________________________________________________________________________
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&sid=396545469

Hi,

> I presume you mean that the server is saying the column can be NULL,
> not that it is actually NULL, since:

Yeah, that is the correct semantics: "it can be NULL".  It does make
a world of difference on the client side, because an "int4 never NULL"
is a different type from "int4 possibly NULL".  The type mismatch
means the program won't even compile.

Cheers,
C.S.



      ____________________________________________________________________________________
Shape Yahoo! in your own image.  Join our Network Research Panel today!
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 

Cultural Sublimation wrote:
> Hi,
>
> > I presume you mean that the server is saying the column can be NULL,
> > not that it is actually NULL, since:
>
> Yeah, that is the correct semantics: "it can be NULL".  It does make
> a world of difference on the client side, because an "int4 never NULL"
> is a different type from "int4 possibly NULL".  The type mismatch
> means the program won't even compile.

Interesting.  What language are you using?

> Interesting.  What language are you using?

OCaml.  The type-safety comes from the PG'OCaml bindings, which basically
check if the types in the database are consistent with the types in the
program.  It's very neat technology, but unfortunately sometimes it seems
too advanced for its own good, since it exposes quite a few weaknesses
and inconsistencies in Postgresql...

Cheers,
C.S.



      ____________________________________________________________________________________
Check out the hottest 2008 models today at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html

Cultural Sublimation <cultural_sublimation@yahoo.com> writes:
>> Interesting.  What language are you using?

> OCaml.  The type-safety comes from the PG'OCaml bindings, which basically
> check if the types in the database are consistent with the types in the
> program.

Unfortunately for you, they are not different types.  If the OCaml
binding thinks they are, it's the binding's problem; especially since
the binding seems to be using a completely lame method of trying to tell
the difference.

            regards, tom lane

Cultural Sublimation wrote:

[has a problem because a SETOF RECORD function can return NULLs in
record attributes]

>                            The client software obtains the type
> information by querying Postgresql, namely by checking the attnotnull
> column in the pg_attribute catalog.  Therefore, this is not an
> inference error on the client side, but instead a case of Postgresql
> providing wrong information.
>
> Well, we could argue all day on whether this is a bug or a feature,
> but the fact is that it is a huge setback for me.  I wanted my clients
> to access the database indirectly, via a function such as get_movies,
> but this problem makes that impossible.

The thing behind the RETURNS in a function is always a data type,
regardless if it is one that has been explicitly declared with
CREATE TYPE or implicitly by CREATE TABLE.

There are no NOT NULL conditions for data types.

NOT NULL only exists for table columns.

So if your function returns "movies", this is the data type
"movies" and not the table "movies". The data type does not have
constraints.
If you check attnotnull of pg_attribute, that is a column of
the table "movies", not the data type.
Hence your confusion, which is quite understandable, because
type and table have the same name.

> So, barring functions and views, is there any other way to encapsulate
> the inner workings of a query away from clients?

Have you considered an ON SELECT ... DO INSTEAD rule?
http://www.postgresql.org/docs/current/static/rules.html

You could create a table that represents the query and
define a SELECT rule on it.

Yours,
Laurenz Albe

> Unfortunately for you, they are not different types.  If the OCaml
> binding thinks they are, it's the binding's problem; especially since
> the binding seems to be using a completely lame method of trying to tell
> the difference.

Hi,

In OCaml and in other languages with strong type systems, "int4 never NULL"
and "int4 possibly NULL" are definitely different types.  I think the source
of the problem here is that SQL has a different philosophy, one where type
constraints are not seen as creating new types.

But anyway if you think that checking pg_attribute is a lame method of
obtaining type information, what do you suggest should be done instead?
What would you do if it were you creating the bindings?

Thanks,
C.S.



      ____________________________________________________________________________________
Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel and lay it on us.
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 

Hi,

> The thing behind the RETURNS in a function is always a data type,
> regardless if it is one that has been explicitly declared with
> CREATE TYPE or implicitly by CREATE TABLE.
>
> There are no NOT NULL conditions for data types.
>
> NOT NULL only exists for table columns.

Thanks for the informative reply.  As I mentioned in the message
to Tom, I think I understand the source of the problem.  In SQL,
type constraints are not seen as creating different types, whereas
in OCaml they do.  (I still maintain that OCaml's way of doing
things is more correct on a fundamental level, though).

> Have you considered an ON SELECT ... DO INSTEAD rule?
> http://www.postgresql.org/docs/current/static/rules.html
>
> You could create a table that represents the query and
> define a SELECT rule on it.

Thanks, I'll investigate that...

Regards,
C.S.




____________________________________________________________________________________
Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow

On Thu, Sep 13, 2007 at 05:02:10AM -0700, Cultural Sublimation wrote:
> In OCaml and in other languages with strong type systems, "int4 never NULL"
> and "int4 possibly NULL" are definitely different types.  I think the source
> of the problem here is that SQL has a different philosophy, one where type
> constraints are not seen as creating new types.

Well, what SQL thinks has little to do with it (it has DOMAINs for
example). The problem is also that postgres doesn't distinguish between
the two in normal usage. Just about *every* function can return NULL if
given the right circumstances. There is really no way postgres can say
something is never null, unless it comes from a column declared as
such.

> But anyway if you think that checking pg_attribute is a lame method of
> obtaining type information, what do you suggest should be done instead?
> What would you do if it were you creating the bindings?

Firstly, the output of most queries is of a type not represented
anywhere in the catalogs. It's mostly going to be an undeclared record
whose members are listed in pg_type. So using pg_attribute for anything
like this is probably completely wrong.

If I were writing it I would ignore the attisnull flag altogether and
assume that any column can be NULL. If you like you could use the
typisnull column in pg_type, that *is* enforced since that's an actual
constraint on the type.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> Firstly, the output of most queries is of a type not represented
> anywhere in the catalogs. It's mostly going to be an undeclared record
> whose members are listed in pg_type. So using pg_attribute for anything
> like this is probably completely wrong.

Right --- it's incapable of working for any query column that's not a
syntactically-trivial reference to a table column (which is the basis
of the OP's original complaint).  The fact that PG even attempts to
report that much is just to satisfy some rather limited requirements
of the JDBC spec.

> If I were writing it I would ignore the attisnull flag altogether and
> assume that any column can be NULL. If you like you could use the
> typisnull column in pg_type, that *is* enforced since that's an actual
> constraint on the type.

Unfortunately that won't go far either.  typisnull could only be true
for a domain type, and the SELECT-output code reports the base type
not the domain type of any domain column.

Another little problem is that not-null-constrained domains don't
actually work, if by "work" you mean that a column putatively of such
a type can never contain any nulls.  The counterexample here is a LEFT
JOIN with such a column on the right side.  The SQL spec is silent on
what to do in such a case, but PG just goes ahead and performs the left
join.  I'm of the opinion that not-null-constrained domains were simply
a Bad Idea that should never have got into the spec at all.

Bottom line is that you should probably never assume that a query result
column can't be null.

            regards, tom lane

Cultural Sublimation wrote:
>> Unfortunately for you, they are not different types.  If the OCaml
>> binding thinks they are, it's the binding's problem; especially since
>> the binding seems to be using a completely lame method of trying to tell
>> the difference.
>
> Hi,
>
> In OCaml and in other languages with strong type systems, "int4 never NULL"
> and "int4 possibly NULL" are definitely different types.  I think the source
> of the problem here is that SQL has a different philosophy, one where type
> constraints are not seen as creating new types.

There's no such a thing as a 'type constraint' in SQL, and there's no
point in defining a new type. Constraints are on table rows, sometimes
not even on the values of columns per se, but on combinations of values...

Think something like (table.col1 > table.col2)... is that 'creating a
new type'? How'd you define this new type, even in OCaml, assuming that
originally both are int4? Is '4' a valid value for that type?

Now, some _table_ constraints may be similar to _type_ constraints, but
that's a corner case, in SQL. It's much more than "a different
philosophy", we're speaking of apples and oranges here. Why should SQL
recognize a very limited kind of constraints, and treat them specially
by defining a new type?

> But anyway if you think that checking pg_attribute is a lame method of
> obtaining type information, what do you suggest should be done instead?
> What would you do if it were you creating the bindings?

I think the bindings get it right, the type *is* "int4 possibly NULL",
because that't what the integer type in SQL means.

The problem here is that not every language type maps perfectly on a
database type (and of course the converse it true). "int4 never NULL"
may be stored into a table with appropriate constraints, but still some
code is needed at application level to convert it back, because there's
no such a native type in PG.

Think of dates and times, I believe no language bindings handle them in
a totally consistent way with PG types (unless they define
special-purpose types with the exact same semantics, which is hardly
worth it).

So, the application is wrong in expecting a SQL database to return
values of type "int4 never NULL". Just write a small conversion layer,
changing "int4 possibly NULL" into "int4 never NULL", after reading the
data.

.TM.