Unable to use upsert when joining two PostGIS tables

[m-kuhn]

I have two tables for which I added a join and used the upsert option.

One table zones and one table status. For each zone, there must be a status defined. Usually, features will be added on zones and corresponding statuses need to be created.

When I add a new zone, it tries to insert a new status indeed, but fails because the id of it is NULL. It seems not to be taking the primary key for the new status from the database serial.

Looking at #33148 which looks very, very similar but it's actually inverted. There, the user starts with the "parent" table and joins a "child" (analogue here: add a status and upsert a zone).

From what I understand, currently the default value needs to be defined on the "main" table field (zones.status_id) while in most of the scenarios I can come up with, the opposite would be the case.

The documentation is silent on default value handling for upsert joins, so I wonder if this requirement here would rather be a new feature than a bug.

CREATE SEQUENCE public.zone_id_seq;

CREATE TABLE public.zones
(
    id integer NOT NULL DEFAULT nextval('public.zone_id_seq'::regclass),
    type character varying,
    status_id integer,
    CONSTRAINT zone_pkey PRIMARY KEY (id)
    CONSTRAINT zone_status_id_fkey FOREIGN KEY (status_id)
        REFERENCES public.status (id) MATCH SIMPLE
);

CREATE SEQUENCE public.status_id_seq;

CREATE TABLE public.status
(
    id integer NOT NULL DEFAULT nextval('public.status_id_seq'::regclass),
    name character varying,

    CONSTRAINT status_pkey PRIMARY KEY (id),
);

[Pedro-Murteira]

@m-kuhn Hello, is this issue still valid on recent releases?

[m-kuhn]

Pretty sure, yes

[alexbruy]

Another similar issue #34239