I'm trying to connect to my postgres database.
Everything is working fine, but in my database I've got 2 schemas. One of them is created by default by PgAdmin (public), and the second one, ZTB, which I have created.
When I connect to my database using NodeJS, it by default connects to the "public" schema, and therefore I cannot query tables from "ZTB".
Is there any solution to switch the current working schema in the NodeJS server?
Regards
I found solution myself - client.query("SET search_path TO 'ZTB';"); did the job exactly as @a_horse_with_no_name said later.
First of all, change your schema name to lower case. Having upper case may create problems.
Next, whatever you want to query just do [your schema name].[your table name], and you should be fine.
client.query("select * from ztb.[your table name]"))
If you using pg-promise, then choosing the schema is available right out of the box.
Within Initialization Options, there is option schema that supports flexible values:
const pgp = require('pg-promise')({
schema: ['public', 'ZTB'] // access tables from "public" + "ZTB"
});
And you can set schema even to a function that returns the schema(s).
set search_path ...postgresql.org/docs/current/static/…client.query("SET search_path TO 'ZTB';");did the job. Update: exactly as @a_horse_with_no_name said.