db-migrate / db-migrate/db-migrate-base
addIndex doesn't work with JSONB indexes
- Dominant language
- JavaScript
- Stars
- 7
- Forks
- 16
- PR merge metrics
- No merged PRs in 30d
Description
In PostgreSQL, there's a JSONB data type that you can use, allowing you to use PostgreSQL kind of like a document database. In order to make this efficient, you can tell PostgreSQL to create indexes on keys and nested keys in JSONB documents. This can be done with:
```
CREATE INDEX "idx_TABLE_on_KEYS" ON "TABLE" ((data::jsonb->'KEY1'->>'KEY2'))
```
Using `db.addIndex`, I'd have thought I could do the following:
```
await db.addIndex(
'TABLE',
'idx_TABLE_on_KEYS',
`(data->'KEY1'->>'KEY2')`
);
```
To produce the same index as the above SQL. However, it turns out that `db.addIndex` automatically (and perhaps sensibly) attempts to escape the values you're passing, which means you get the following query being executed:
```
CREATE INDEX "idx_TABLE_on_KEYS" ON "TABLE" ("(data->'KEY1'->>'KEY2')")
```
The additional quotes in this case actually break the creation of the index.
```
> [ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: column "(data->'KEY1'->>'KEY2')" does not exist
```
I'm not sure what the right fix is, but perhaps it'd be an idea to allow the user to say "actually, I know what I'm doing, please don't escape this" whilst giving the default of escaping column names.
Thoughts?
---
Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/81467891-addindex-doesn-t-work-with-jsonb-indexes?utm_campaign=plugin&utm_content=tracker%2F11792942&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F11792942&utm_medium=issues&utm_source=github).
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.