PostgreSQL foreign data wrapper mimic Constraints

Question

Using PostgreSQL 9.6.9

It appears that constraints are not available when using the Postgres foreign data wrapper https://github.com/postgres/postgres/blob/e1ae40f381d0582981b1e63856bd4b060cfe2d53/src/test/regress/expected/foreign_data.out#L692-L711.

primary key constraints are not supported on foreign tables

foreign key constraints are not supported on foreign tables

unique constraints are not supported on foreign tables

I want to run a query that groups by a primary key and selects some other columns in the same table. The query does not work when the primary key constraint is absent. https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY.

A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

The easy solution would be to add the other columns to the GROUP BY but tech debt makes this another challenge.

Even if the constraint cannot be enforced, is there a way to mimic or partially enable the constraint so the query works?

Laurenz Albe · Accepted Answer · 2021-03-31 07:00:26Z

1

No, you will have to add the other columns to the GROUP BY clause. I assume by "tech debt" you are referring to the need to type all the columns. Use a smart text editor!

answered Mar 31, 2021 at 7:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harsha Laxman Over a year ago

tech_debt == bad_orm

Laurenz Albe Over a year ago

My condolences.

Collectives™ on Stack Overflow

PostgreSQL foreign data wrapper mimic Constraints

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related