I'm trying to use Postgres as a document store and am running into a problem when I'm trying to effectively upsert a document where the Postgres parser doesn't seem to like the JSONB operator.
I have a table:
CREATE TABLE tbl (data jsonb NOT NULL);
CREATE UNIQUE INDEX ON tbl ((data->>'a'));
and I try to insert data with:
INSERT INTO tbl (data) VALUES ('{ "a": "b" }'::jsonb)
ON CONFLICT (data->>a)
DO UPDATE SET data = data || '{ "a": "b" }'::jsonb
I get this error message:
ERROR: syntax error at or near "->>"
I've tried data->>a, data->>'a', data->a, and maybe data->'a'. All of those are
I'd like to leave the identifier column (a in the example) within the JSON and not make it a column on the table.
Is what I'm trying to do currently supported?
There are two issues you have:
1) You need to add additional parenthesis, like so:
ON CONFLICT ((data->>'a'))
2) You need to preface the last data reference with your table alias, like so:
DO UPDATE SET data = tbl.data || '{ "a": "b" }'::jsonb
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加