db-migrate / db-migrate/db-migrate-base

addIndex doesn't work with JSONB indexes

Open
#16 1 comment 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.