如何使用node-postgres设置架构 [英] How to set schema with node-postgres
本文介绍了如何使用node-postgres设置架构的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用
I'm trying to query against a schema called DOCUMENT
in my postgres db using node-postgres.
我似乎无法使查询针对指定的架构运行。
I can't seem to get a query to run against the specified schema.
此查询使用psql直接针对postgres运行很好
This query runs fine directly against postgres using psql
SELECT * FROM "DOCUMENT".document_metadata m
LEFT OUTER JOIN "DOCUMENT".document_attributes a
ON a.document_id = m.id
此代码会产生以下错误
const query = `SELECT * FROM "DOCUMENT".document_metadata m
LEFT OUTER JOIN "DOCUMENT".document_attributes a
ON a.document_id = m.id`
const metadata = await db.query(query)
错误
error: relation "DOCUMENT.document_metadata" does not exist
at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:602:11)
at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:399:19)
at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:121:22)
at Socket.emit (events.js:189:13)
at addChunk (_stream_readable.js:284:12)
at readableAddChunk (_stream_readable.js:265:11)
at Socket.Readable.push (_stream_readable.js:220:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
我也尝试过
const query = `SET search_path TO 'DOCUMENT';
SELECT * FROM document_metadata m
LEFT OUTER JOIN document_attributes a
ON a.document_id = m.id;`
const metadata = await db.query(query)
会产生错误
error: relation "document_metadata" does not exist
at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:602:11)
at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:399:19)
at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:121:22)
at Socket.emit (events.js:189:13)
at addChunk (_stream_readable.js:284:12)
at readableAddChunk (_stream_readable.js:265:11)
at Socket.Readable.push (_stream_readable.js:220:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
更新
当我直接在psql中运行这些相同的查询时,我看到带有表名的10行。当我通过节点代码运行此命令时,我看不到任何行:
Update When I run these same queries directly in psql I see like 10 rows with table names. When I run this through my node code I see no rows:
const metadata4 = await db.query('SHOW search_path;')
console.log('search_path after setting = ', metadata4.rows) // prints [ { search_path: '"DOCUMENT"' } ]
const tables = await db.query(`SELECT * FROM information_schema.tables where table_schema = 'DOCUMENT';`)
console.log('tables = ', tables.rows) // prints []
推荐答案
只需在单独的行中执行:
just execute in a separated line:
db.query("SET search_path TO 'DOCUMENT';")
DOCUMENT是架构的名称,然后执行常规查询:
DOCUMENT is the name of your schema, then execute the normal query:
const query = `SELECT * FROM document_metadata m
LEFT OUTER JOIN document_attributes a
ON a.document_id = m.id;`
db.query(query)
这篇关于如何使用node-postgres设置架构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文