4 ms·

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

JoelJacobson 5y ago
The 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 ago
  Standards published by ISO/IEC.
  - yunohn 5y ago
    Sure, but where is that pure standard implemented?
    - bradleyjg 5y ago
      Why does it need to be implemented somewhere?
      - yunohn 5y ago
        What 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 ago
        I’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.

JoelJacobson 5y ago

The 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 ago
Standards published by ISO/IEC.
- yunohn 5y ago
  Sure, but where is that pure standard implemented?
  - bradleyjg 5y ago
    Why does it need to be implemented somewhere?
    - yunohn 5y ago
      What 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 ago
        I’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.