From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: UPDATE-FROM and INNER-JOIN |
Date: | 2024-08-05 15:09:20 |
Message-ID: | CAFCRh-8RrdEpi+iWKuUcYSBhWJo5YJojXxKAFD0bW9aK_fxz2A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Aug 5, 2024 at 5:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
> > The reason I find the restriction damaging is that `FROM t1, t2 WHERE
> > t1.c1 = t2.c2`
> > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2
> > ON t1.c1 = t2.c2`
> > which IMHO better separates "filtering" from "joining" columns. FWIW.
>
> But the whole point of that syntax is to be explicit about which
> tables the ON clause(s) can draw from. If we had a more complex
> FROM clause, with say three or four JOINs involved, which part of
> that would you argue the UPDATE target table should be implicitly
> inserted into?
Wherever an update-target-column was referenced in an ON clause.
Like SQLite used to support. I.e. possibly multiple times even, I guess.
Yes that does imply the update-target table in not explicitly named
in the FROM clause, specifically in the UPDATE-FROM case.
Personally I don't find that "offensive", it's explicitly part of an UPDATE.
> The only thing that would be non-ambiguous would
> be to require the target table to be explicitly named in FROM
> (and not treat that as a self-join, but as the sole scan of the
> target table). Some other RDBMSes do it like that, but it seems
> like too much of a compatibility break for us.
The (old for now) SQLite way would be lifting a restriction,
so that wouldn't be a backward incompatible change IMHO
> Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE,
> so they offer no guidance.
And that's precisely why SQLite and PostgreSQL agreeing on a precedent
would be nice.
> But I doubt we are going to change
> this unless the standard defines it and does so in a way that
> doesn't match what we're doing.
OK. Fair enough. I'm just expressing a personal opinion above.
Which the SQLite Forum OP also supports I'd guess. FWIW.
Thanks for your input Tom. --DD
From | Date | Subject | |
---|---|---|---|
Next Message | Wiwwo Staff | 2024-08-05 18:46:06 | Check psql parameter is passed, if not ask for it |
Previous Message | Tom Lane | 2024-08-05 15:01:50 | Re: UPDATE-FROM and INNER-JOIN |