DELETE ... USING LATERAL

Поиск
Список
Период
Сортировка
От Nikhil Benesch
Тема DELETE ... USING LATERAL
Дата
Msg-id CAPWqQZQQWDX2A4zzEyJ_rqks0zCOaC2r2yjN4bAcEVhKb0V5AQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: DELETE ... USING LATERAL  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: DELETE ... USING LATERAL  (Michael Lewis <mlewis@entrata.com>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres
Следующее
От: Rob Sargent
Дата:
Сообщение: Re: Growth planning