How you can browse depends on the primary key constraint in postgres

Question

Sometimes when loading bulk data, it is recommended to use time limits for the drop and indexes in the table. But when I do this, I run into some addiction problems. My simplified example:

CREATE TABLE public.t_place_type ( id serial NOT NULL, c_name character varying(100), CONSTRAINT pk_t_place_type PRIMARY KEY (id) ); CREATE TABLE public.t_place ( id serial NOT NULL, c_name character varying(50), id_place_type integer, CONSTRAINT pk_t_place PRIMARY KEY (id), CONSTRAINT fk_t_place_t_place_type FOREIGN KEY (id_place_type) REFERENCES public.t_place_type (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE OR REPLACE VIEW public.v_place AS SELECT p.id, p.c_name, pt.c_name AS c_place_type FROM t_place p LEFT JOIN t_place_type pt ON pt.id = p.id_place_type GROUP BY p.id, pt.id, p.c_name; 

My script:

 ALTER TABLE public.t_place DROP CONSTRAINT fk_t_place_t_place_type; ALTER TABLE public.t_place DROP CONSTRAINT pk_t_place; ALTER TABLE public.t_place_type DROP CONSTRAINT pk_t_place_type; 

When I run it, I get an error message:

ERROR: unable to remove pk_t_place_type constraint in t_place_type table because other DETAIL objects depend on it: viewing v_place depends on pk_t_place_type constraint on t_place_type table

It is strange that this opinion depends on some restriction. AFAIK postgres does not cache the execution plan for the view.

When I change my mind in this way:

 CREATE OR REPLACE VIEW public.v_place AS SELECT p.id, p.c_name AS c_name, pt.c_name AS c_place_type FROM t_place p LEFT JOIN t_place_type pt ON pt.id = p.id_place_type; 

the dependency is gone and my script runs successfully.

So my question is: what is the reason for this relationship between representation and restriction.

EDIT

Here https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-GROUPBY postgres docs say:

When GROUP BY is present or any aggregate functions are present, it is not valid for SELECT list expressions to refer to ungrouped columns, except for aggregate functions and for a non-group column it functionally depends on grouped columns, because otherwise it can be more than one possible return value for a non-group column. A functional dependency exists if grouped columns (or a subset of them) are the primary key of a table containing an ungrouped column.

Is this the reason for this behavior? Although I don't have ungrouped columns in my opinion?

Answer 1

Your request from the view will not work if you put PK on public.t_place_type .

This will result in this error:

 ERROR: column "pt.c_name" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: pt.c_name AS c_place_type ^ 

This is because, from your documentation link, A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column .

Postgres knows that PK is a unique row, so as soon as you group it, you can group all the columns from this table and get the same result.

Those give the same result: with ungrouped strings:

 SELECT * FROM public.t_place; SELECT * FROM public.t_place GROUP BY id; SELECT * FROM public.t_place GROUP BY id, c_name; SELECT * FROM public.t_place GROUP BY id, c_name, id_place_type; SELECT * FROM public.t_place GROUP BY id, id_place_type; 

And you use this dependency when choosing pt.c_name AS c_place_type , because you grouped this table with the primary key pt.id , with a nonexistent PC after deleting it, so grouping by it makes pt.c_name , which are not used together and are not used in group by . That's why Postgres complains about viewing dependency - its request will no longer work as soon as you drop this PC.

You can try it yourself with a modified example from your question:

 CREATE TABLE public.t_place_type ( id serial NOT NULL, c_name character varying(100) ); CREATE TABLE public.t_place ( id serial NOT NULL, c_name character varying(50), id_place_type integer, CONSTRAINT pk_t_place PRIMARY KEY (id) ); SELECT p.id, p.c_name, pt.c_name AS c_place_type FROM t_place p LEFT JOIN t_place_type pt ON pt.id = p.id_place_type GROUP BY p.id, pt.id, p.c_name; /* RESULT: ERROR: column "pt.c_name" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: pt.c_name AS c_place_type ^ */