Describe the bug
Maybe I'm holding it wrong, but I was trying to create an index on a JSON column's field:
pgm.createIndex('functions', `contentSub->>'id'`, {
name: 'functions_contentsub_id_idx',
where: `"deletedAt" IS NULL AND "contentSub"->>'id' IS NOT NULL`,
})But this yields an error during migration.
Seems like quoting of the expression as column name is a bit whacky.
I ended up simply using pg.sql instead 🤷
Steps to reproduce
- create a table with a JSON column
- try to create an index on a property within the JSON column using the
->> operator:
pgm.createIndex('functions', `contentSub->>'id'`, {
name: 'functions_contentsub_id_idx',
where: `"deletedAt" IS NULL AND "contentSub"->>'id' IS NOT NULL`,
})Logs
Migrating DB at 127.0.0.1:58101/functions (direction: up)...
> Migrating files:
> - 1767177600000_optimize-indexes
### MIGRATION 1767177600000_optimize-indexes (UP) ###
Error executing:
CREATE INDEX "functions_contentsub_id_idx" ON "functions" ("contentSub->>'id'") WHERE "deletedAt" IS NULL AND
"contentSub"->>'id' IS NOT NULL;
error: column "contentSub->>'id'" does not exist
Error executing:
SELECT pg_advisory_unlock(7241865325823964) AS "lockReleased"
error: current transaction is aborted, commands ignored until end of transaction block
current transaction is aborted, commands ignored until end of transaction block
/Users/filmaj/src/functions/node_modules/pg/lib/client.js:545
Error.captureStackTrace(err)
^
error: column "contentSub->>'id'" does not exist
at /Users/filmaj/src/functions/node_modules/pg/lib/client.js:545:17
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
at async Object.query
(file:///Users/filmaj/src/functions/node_modules/node-pg-migrate/dist/esm/index.mjs:2649:14)
at async runner (file:///Users/filmaj/src/functions/node_modules/node-pg-migrate/dist/esm/index.mjs:2965:7)
at async file:///Users/filmaj/src/functions/scripts/db-migrate.js:19:1 {
length: 107,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'indexcmds.c',
line: '1894',
routine: 'ComputeIndexAttrs'
}System Info
System:
OS: macOS 15.6.1
CPU: (12) arm64 Apple M4 Pro
Memory: 246.81 MB / 24.00 GB
Shell: 5.9 - /bin/zsh
Binaries:
Node: 22.20.0 - /nix/store/0fsycz0q43zxxi2b8kgza2x48y05j61j-nodejs-22.20.0/bin/node
Yarn: 1.22.22 - /opt/homebrew/opt/nvm/versions/node/v22.15.0/bin/yarn
npm: 10.9.3 - /nix/store/0fsycz0q43zxxi2b8kgza2x48y05j61j-nodejs-22.20.0/bin/npm
pnpm: 10.16.1 - /opt/homebrew/opt/nvm/versions/node/v22.15.0/bin/pnpm
Describe the bug
Maybe I'm holding it wrong, but I was trying to create an index on a JSON column's field:
But this yields an error during migration.
Seems like quoting of the expression as column name is a bit whacky.
I ended up simply using
pg.sqlinstead 🤷Steps to reproduce
->>operator:Logs
System Info
System: OS: macOS 15.6.1 CPU: (12) arm64 Apple M4 Pro Memory: 246.81 MB / 24.00 GB Shell: 5.9 - /bin/zsh Binaries: Node: 22.20.0 - /nix/store/0fsycz0q43zxxi2b8kgza2x48y05j61j-nodejs-22.20.0/bin/node Yarn: 1.22.22 - /opt/homebrew/opt/nvm/versions/node/v22.15.0/bin/yarn npm: 10.9.3 - /nix/store/0fsycz0q43zxxi2b8kgza2x48y05j61j-nodejs-22.20.0/bin/npm pnpm: 10.16.1 - /opt/homebrew/opt/nvm/versions/node/v22.15.0/bin/pnpm