DELETE ... USING LATERAL

From: Nikhil Benesch <nikhil(dot)benesch(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: DELETE ... USING LATERAL
Date: 2021-10-04 16:29:36
Message-ID: CAPWqQZQQWDX2A4zzEyJ_rqks0zCOaC2r2yjN4bAcEVhKb0V5AQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is it intentional that LATERAL elements in a USING clause of a DELETE
statement can't reference the table declared in the FROM clause?

Here's a somewhat contrived example. Suppose I have a table with one
jsonb column:

create table int_arrays (int_array jsonb);
insert into int_arrays values ('[1]'), ('[1, 2]'), ('[3, 4, 5]'),
('[1, 1, 1]');

If I want to delete every row whose array contains a value greater
than one, I would expect the following query to work:

delete from int_arrays using jsonb_array_each(int_array) _ (val)
where val::integer > 1;

But that fails with:

ERROR: invalid reference to FROM-clause entry for table "int_arrays"
LINE 1: delete from int_arrays using jsonb_array_each(int_array) _ (...
^
HINT: There is an entry for table "int_arrays", but it cannot be
referenced from this part of the query.

So, ok, fine, the FROM and USING clauses are different scopes or
something. Except that doesn't quite explain the situation, because
you can't reuse the FROM table name in the USING clause:

# delete from int_arrays using int_arrays;
ERROR: table name "int_arrays" specified more than once

Can someone shed some light on the situation here? Is there a reason
that LATERAL elements in the USING clause must be prevented from
accessing the FROM table or is the restriction just emergent behavior?

Nikhil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2021-10-04 16:46:31 Re: Growth planning
Previous Message Rob Sargent 2021-10-04 16:25:12 Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres