4 ms·
> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DD
by JoelJacobson 5y ago
> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed.
Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1]
INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
[1] https://www.postgresql.org/docs/current/sql-insert.html https://www.postgresql.org/docs/current/sql-insert.html
- bradleyjg 5y agoPostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.
- JoelJacobson 5y agoThe INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemented_in_practice https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente... There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link: * The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT. * The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.
- deleted 5y ago[deleted]
- yunohn 5y ago> PostgresSQL isn’t SQL though What is SQL acc. to you? MS-SQL, MySQL, SQLITE, Bigquery “Standard SQL”?
- bradleyjg 5y agoStandards published by ISO/IEC.
- yunohn 5y agoSure, but where is that pure standard implemented?
- bradleyjg 5y agoWhy does it need to be implemented somewhere?
- yunohn 5y agoWhat do you mean? If the ISO SQL is not implemented by any SQL DB, and you denounce PostgreSQL/etc version of SQL - what is left over?
- bradleyjg 5y agoI’m not denouncing PostgreSQL. There’s things that are appropriate for a standard and other, sometimes far less elegant, things that are appropriate for a concrete implementation. The linked article propose a change to the standard. If it had only proposed a non-standard vendor extension for some or other database we wouldn’t be having this discussion.
- deleted 5y ago[deleted]
- cryptonector 5y agoThat's DML, not DQL. Still, I disagree with GP's arguments (see separate comment).
- JoelJacobson 5y ago> That's DML, not DQL. Thanks. I incorrectly assumed there were only two sublanguages. But now when you say it, I've heard both DML and DDL before, but never DQL. Curious if there were even more categories, I found this Wikipedia article: https://en.wikipedia.org/wiki/Data_query_language https://en.wikipedia.org/wiki/Data_query_language Interesting to read, what a SELECT statement is, depends on if having FROM or WHERE "data manipulators". Quote from Wikipedia: "Although often considered part of DML, the SQL SELECT statement is strictly speaking an example of DQL. When adding FROM or WHERE data manipulators to the SELECT statement the statement is then considered part of the DML."