Re: Cannot declare record members NOT NULL

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.