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?
/Joel
