admin管理员组

文章数量:1125915

I have a JSONB column which contains JSON data like this

[
  {
    "url": ";,
    "vendor": "County"
  }
]

When I try to query it with "for update"

select
    id,
    updated_at,
    jsonb_array_elements(data_src)->>'url' as vendor,
    in_use
from
    myschema.table a
where
    data_src @> '[{"vendor": "County"}]' limit 1 for update;

I get the following error

ERROR: FOR UPDATE is not allowed with set-returning functions in the target list

Is there anyway around this? Or would I need to modify my JSON format so jsonb_array_elements is not used?

I have a JSONB column which contains JSON data like this

[
  {
    "url": "https://example.com",
    "vendor": "County"
  }
]

When I try to query it with "for update"

select
    id,
    updated_at,
    jsonb_array_elements(data_src)->>'url' as vendor,
    in_use
from
    myschema.table a
where
    data_src @> '[{"vendor": "County"}]' limit 1 for update;

I get the following error

ERROR: FOR UPDATE is not allowed with set-returning functions in the target list

Is there anyway around this? Or would I need to modify my JSON format so jsonb_array_elements is not used?

  • postgresql
Share Improve this question asked 2 days ago AryaArya 8,95532 gold badges114 silver badges187 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 1

Rewrite your query:

SELECT a.id,
       a.updated_at,
       e.j->>'url' as vendor,
       a.in_use
FROM myschema.table a
   CROSS JOIN LATERAL jsonb_array_elements(a.data_src) AS e(j)
WHERE a.data_src @> '[{"vendor": "County"}]'
LIMIT 1
FOR NO KEY UPDATE OF a;

Now the set returning function jsonb_array_elements() is in the FROM clause, where it should be.

Unless you intend to delete the row or modify a unique key or primary key column, FOR NO KEY UPDATE is the correct lock level.

本文标签：

版权声明：本文标题：postgresql - Using jsonb_array_elements with for update "ERROR: FOR UPDATE is not allowed with set-returning functions 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736657035a1946282.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。