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.

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

This code produces the error below

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

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)

I've also tried this

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)

which produces the error

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)

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 []

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.

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

This code produces the error below

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

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)

I've also tried this

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)

which produces the error

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)

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 []

• javascript
• node.js
• postgresql
• node-postgres

5 Answers 5

just execute in a separated line:

db.query("SET search_path TO '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)

if someone still interested in, you can do that using connection string and passing options, not just schema.

postgres://postgres:postgres@localhost:5432/my_db?options=-c search_path=my_schema

Maybe the uppercase is the problem. Did you tried with a lowercase schema name? Or add quotation marks around the table names:

const query = 'SELECT * FROM "DOCUMENT"."document_metadata" m 
               LEFT OUTER JOIN "DOCUMENT"."document_attributes" a 
               ON a.document_id = m.id'

Define the schema in an environment variable to the Client/Pool

Anyway, if you would like to define the schema in node js and no need to switching between different schemas, then this solution is a good choice. The default schema can come from environment variable.

const Pool = require('pg').Pool
const Client = require('pg').Client

class EnhancedClient extends Client {
  getStartupConf() {
    if (process.env.PG_OPTIONS) {
      try {
        const options = JSON.parse(process.env.PG_OPTIONS);
        return {
          ...super.getStartupConf(),
          ...options,
        };
      } catch (e) {
        console.error(e);
        // Coalesce to super.getStartupConf() on parse error
      }
    }

    return super.getStartupConf();
  }
}

const pool = new Pool({
  Client: EnhancedClient,
  user: 'postgres',
  host: 'localhost',
  database: 'postgres',
  password: 'postgres',
  port: 5432
})

In this example, PG_OPTIONS is stringified JSON, e.g. provided via the command line, a PM2 ecosystem file, etc.

For example: PG_OPTIONS='{"search_path":"DOCUMENT"}' node app.js

I start from windows command prompt (sorry for that :) )

SET PG_OPTIONS={"search_path":"DOCUMENT"}
node app.js

When you create the DB URL, you can append the schema to it like so:

`postgres://${dbUser}:${dbPass}@${dbHost}:${dbPort}/${dbName}?schema=${schema}`

Issue #1123 in node-postgres, which has been open since 2016, is a request to add support to the connection request to specify the current schema.

This obviously doesn't solve your issue in the immediate term, but if this feature request ever gets implemented, it could be used assuming all queries should be against the given schema.