Re: ERROR: failed to find conversion function from key_vals_nn to record[]

On 2022-06-16 23:58:23 -0700, Bryn Llewellyn wrote:
>     david.g.johnston@gmail.com wrote:
>
>         bryn@yugabyte.com wrote:
>
>         Can anybody show me an implementation of a realistic use case that
>         follows proper practice — like "every table must a primary key", "a
>         foreign key must refer to a primary key", and "joins may be made only
>         "on" columns one of which has a PK constraint and the other of which
>         has a FK constraint" — where using a not nullable data type brings a
>         problem that wouldn't occur if the column were defined with a nullable
>         data type and an explicit "not null" constraint?
[...]
>     -- "\d genres" shows "gk" with a "not null" constraint, whether I write it
>     -- or not. And convention seems to say "don't clutter you code by writing
>     it".
>     create table genres(
>       gk  int   primary key,
>       gv  text  not null
>       );
>
>     "Primary Key" is defined to be the application of both UNIQUE and NOT NULL
>     constraints...
>
> Yes, I know what "primary key" implies. I meant only to emphasize that the
> source column for what the "outer join" projects has a not null constraint,
> that it doesn't apply to the projection of that column, that this is perfectly
> understandable, and that this isn't a problem. Never mind.

But it would be a problem if there was an actual type which wouldn't
include NULL.

The NOT NULL attribute is an attribute of the column, not the type.
When you use the primary key (or any other column marked as NOT NULL)
the type of the result is just the type of that column, the NOT NULL is
dropped.

For example:

hjp=> \d genres
                             Table "public.genres"
╔════════╤═════════╤═══════════╤══════════╤════════════════════════════════════╗
║ Column │  Type   │ Collation │ Nullable │              Default               ║
╟────────┼─────────┼───────────┼──────────┼────────────────────────────────────╢
║ id     │ integer │           │ not null │ nextval('genres_id_seq'::regclass) ║
║ name   │ text    │           │ not null │                                    ║
╚════════╧═════════╧═══════════╧══════════╧════════════════════════════════════╝
Indexes:
    "genres_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "books" CONSTRAINT "books_genre_fkey" FOREIGN KEY (genre) REFERENCES genres(id)

hjp=> \d books
                             Table "public.books"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════════╗
║ Column │  Type   │ Collation │ Nullable │              Default              ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────────╢
║ id     │ integer │           │ not null │ nextval('books_id_seq'::regclass) ║
║ title  │ text    │           │ not null │                                   ║
║ genre  │ integer │           │ not null │                                   ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════════╝
Indexes:
    "books_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "books_genre_fkey" FOREIGN KEY (genre) REFERENCES genres(id)

hjp=> create table books_by_genre as
      select g.name as genre, b.title
      from genres g left join books b on g.id = b.genre;
SELECT 4
Time: 14.046 ms
hjp=> \d books_by_genre
          Table "public.books_by_genre"
╔════════╤══════╤═══════════╤══════════╤═════════╗
║ Column │ Type │ Collation │ Nullable │ Default ║
╟────────┼──────┼───────────┼──────────┼─────────╢
║ genre  │ text │           │          │         ║
║ title  │ text │           │          │         ║
╚════════╧══════╧═══════════╧══════════╧═════════╝

As you can see, the type of the two columns is just "text" not "text not
null". And this is as is should be because the result indeed contains a
NULL value:

hjp=> select * from books_by_genre ;
╔═════════════╤══════════════════════════╗
║    genre    │          title           ║
╟─────────────┼──────────────────────────╢
║ Non-Fiction │ Mastering PostgreSQL 9.6 ║
║ SF          │ Idoru                    ║
║ SF          │ Network Effect           ║
║ Romance     │ (∅)                      ║
╚═════════════╧══════════════════════════╝
(4 rows)

Now, if title actually had a type which didn't include a null value,
this wouldn't be possible. Either the database would have to lie
(declare the column with a type but store a value which is impossible in
that type) or the query would have to fail or the database would have to
figure out a super type which does include that value. Neither
possibility seems very attractive to me (the first one is completely
unacceptable, the second one would be annoying, the third one might be
hard to implement).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"