út 30. 3. 2021 v 10:49 odesílatel Joel Jacobson <j...@compiler.org> napsal:
> On Tue, Mar 30, 2021, at 10:24, Pavel Stehule wrote: > > > > I think this is a good example of where this improves the situation the > most, > when you have multiple joins of the same table, forcing you to come up > with multiple aliases > for the same table, keeping them all in memory while writing and reading > such queries. > > > ... > I remember multiple self joins only when developers used an EAV model. > This is an antipattern, and today we have better tools, and we don't need > it. It is scary, because it is completely against the relational model. > > > No, you are mistaken. There are no self-joins in any of the examples I > presented. > I merely joined in the same table multiple times, but not with itself, so > it's not a self join. > > Here is the query again, it doesn't contain any self-joins: > > SELECT > format > ( > 'ALTER TABLE %I.%I RENAME CONSTRAINT %I TO %I;', > conrel_nsp.nspname, > conrel.relname, > pg_constraint.conname, > confrel.relname > ) AS sql_cmd, > COUNT(*) OVER (PARTITION BY pg_constraint.conrelid, > pg_constraint.confrelid) > AS count_foreign_keys_to_same_table > FROM pg_constraint > JOIN pg_class AS conrel > ON conrel.oid = pg_constraint.conrelid > JOIN pg_class AS confrel > ON confrel.oid = pg_constraint.confrelid > JOIN pg_namespace AS conrel_nsp > ON conrel_nsp.oid = conrel.relnamespace > WHERE pg_constraint.contype = 'f' > > Where would the antipattern be here? > > ok, this is not EAV. /Joel >
