CODEWITHSUNDEEP
sqlpostgresql
Marcin Konopka
Marcin Konopka

Reputation: 57

How to change default/current schema in sql

Preety basic question from my end that I unfortunately could not find an answer to. In my database I have 2 schemas with their respective tables. Schema X and schema Y. While I can simply pull the data easily from schema X with

SELECT * FROM employees

I cannot do the same with schema Y, forcing me to use

SELECT * FROM SchemaY.customers

My question is, how can I change the current or default schema from X to Y so I can query the tables without the SchemaY. in the table names begining.

Kindest

Marcin

ps: I utilize Postgres and play around in DataGrip.

Upvotes: 3

Views: 5088

Answers (2)

bcag2
bcag2

Reputation: 2439

To do it permanently after be connected with psql -U my_admin_user I use this request :

ALTER DATABASE my_database SET SEARCH_PATH TO my_schema;

NOTE, if you use postgis that public schema is used by postgis.

Upvotes: 0

user330315
user330315

Reputation:

Change the search path

set search_path = schemay, public;

You can also do that for your user permanently:

alter user current_user 
  set search_path = schemay, public;

Upvotes: 11

Related Questions