There is a column named <xxx> in table <xxx>, but it cannot be referenced from this part of the query

[MichalJana]

I have PostgreSQL database with two tables Person and Address. Address table has FK person_id. When I try to insert a new entity in the address, I get an error message: There is a column named "person_id" in table "address", but it cannot be referenced from this part of the query. I found out that when I change the name from person_id to address_person_id_fkey in the C# model (or in FluentMapper), the insert works. Why is it hapenning? Thx.

Table:

create table address( 
address_id bigserial not null primary key,
person_id bigserial,
street varchar(50) not null,
city varchar(50) not null )

Model:

public record AddressEntity
{
        public int address_id { get; set; }

        public int person_id { get; set; }

        public string street { get; set; } = string.Empty;

        public string city { get; set; } = string.Empty;
}

RepoDb Version:
<PackageReference Include="RepoDb.PostgreSql" Version="1.1.4" />

Postgre Version:
PostgreSQL 14.0, compiled by Visual C++ build 1914, 64-bit

[mikependon]

There is a column named "person_id" in table "address", but it cannot be referenced from this part of the query.

I do not think specifically that this error is being thrown by RepoDB. We will investigate this one first before providing a feedback to you. If this errors comes with Npgsql, we will contact the their team otherwise.

[mikependon]

We have investigated this and found out the root cause. Since you had made the address.person_id as foreign key reference to person.person_id, you should atleast not have a default value into it.

We strongly recommend to simply set the type to bigint rather than bigserial. (see below)

CREATE TABLE IF NOT EXISTS public.address
(
    address_id bigint NOT NULL DEFAULT nextval('address_address_id_seq'::regclass),
    person_id bigint NOT NULL, -- DEFAULT nextval('address_person_id_seq'::regclass), -- Must be removed
    street character varying(50) COLLATE pg_catalog."default" NOT NULL,
    city character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT address_pkey PRIMARY KEY (address_id),
    CONSTRAINT address_person_id FOREIGN KEY (person_id)
        REFERENCES public.person (person_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.address
    OWNER to postgres;

The reason why it is failing is the fact that RepoDB had identified the column address.person_id as an identity column and that has been removed to the list of input parameters to be passed.

[mikependon]

We have decided to place this as part of the limitation instead.