0

I'm logging in to my Postgres 12 (running on Windows 10) db using an alias, which resolves to

PGPASSWORD=$DB_PASSWORD psql -U${DB_USER} $DB_NAME

I would like to find a way to tell Postgres which schema to use by default when logging in, either on the command line or another way. I tried setting the "search_path" ...

> login_to_my_db
psql (12.11)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

my_db_name=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)


my_db_name=> set search_path = 'my_schema';
SET
my_db_name=> show search_path;
 search_path
-------------
 my_schema
(1 row)

But as soon as I log out and log back in, the search path setting is gone

my_db_name=> \q
> login_to_my_db
psql (12.11)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

my_db_name=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

How can I preserve it or at least tell psql what schema to use when logging in?

1
  • 1
    Just change the user alter user ... set search_path = ..
    – user1822
    Jul 20, 2022 at 14:34

1 Answer 1

1

To formalize the answer given in comments, I logged in as the "postgres" (admin) user and ran

ALTER USER myuser SET SEARCH_PATH = 'my_schema';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.