🐜 This seems to be an execution error, which means that your request syntax seems okay, but the resulting statement cannot be executed → Probably not a pg-mem error.
*️⃣ Failed SQL statement: SELECT "obj" FROM test_table WHERE obj#>>'{id}' = 'first';
It also failed on variants:
SELECT "obj" FROM test_table WHERE (obj#>>'{id}')::text = 'first';
SELECT "obj" FROM test_table WHERE obj::jsonb#>>'{id}' = 'first';
SELECT "obj" FROM "test_table" WHERE (obj#>>'{age}')::int = 1;
To Reproduce
CREATE TABLE IF NOT EXISTS test_table (
pk SERIAL PRIMARY KEY,
obj JSONB NOT NULL,
unique_key VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW());
CREATE INDEX IF NOT EXISTS idx_unique_key ON test_table (unique_key);
DELETE FROM test_table;
INSERT INTO test_table (obj, unique_key) VALUES ('{"id": "first", "name": "Bob", "age": 6}', 'first');
SELECT "obj" FROM "test_table" WHERE (obj#>>'{age}')::int = 1;
pg-mem version
2.8.1
Functional equivalence
>> is a simpler nesting syntax for "->>".
E.g. {"children": {"Bob": {"age": 1}}}
>>{children,Bob,age} vs ->children->Bob->>age
pg-mem does correctly support the other syntax (but I'm using a library that is giving me SQL in the form of #>>).
Describe the bug
operator does not exist: jsonb #>> text
🐜 This seems to be an execution error, which means that your request syntax seems okay, but the resulting statement cannot be executed → Probably not a pg-mem error.
*️⃣ Failed SQL statement: SELECT "obj" FROM test_table WHERE obj#>>'{id}' = 'first';
It also failed on variants: SELECT "obj" FROM test_table WHERE (obj#>>'{id}')::text = 'first'; SELECT "obj" FROM test_table WHERE obj::jsonb#>>'{id}' = 'first'; SELECT "obj" FROM "test_table" WHERE (obj#>>'{age}')::int = 1;
To Reproduce
CREATE TABLE IF NOT EXISTS test_table ( pk SERIAL PRIMARY KEY, obj JSONB NOT NULL, unique_key VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW());
CREATE INDEX IF NOT EXISTS idx_unique_key ON test_table (unique_key);
DELETE FROM test_table; INSERT INTO test_table (obj, unique_key) VALUES ('{"id": "first", "name": "Bob", "age": 6}', 'first');
SELECT "obj" FROM "test_table" WHERE (obj#>>'{age}')::int = 1;
pg-mem version
2.8.1
Functional equivalence
>> is a simpler nesting syntax for "->>".
E.g. {"children": {"Bob": {"age": 1}}}
>>{children,Bob,age} vs ->children->Bob->>age
pg-mem does correctly support the other syntax (but I'm using a library that is giving me SQL in the form of #>>).