Set Search Path at database level for PostgreSQL

There are quite a lot examples on how to set search_path at user level, here is one at the database level.

Check the setting before change

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole | setconfig
-------------+---------+-----------
(0 rows)

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

Change the default search path at database level

The setting can be overwritten by user level setting and session level setting.

dev=> alter database dev set search_path="$user",appuser,public;
ALTER DATABASE

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole |                 setconfig
-------------+---------+--------------------------------------------
       16400 |       0 | {"search_path=\"$user\", appuser, public"}
(1 row)

-- the setting doesn't take effect without reconnection
dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Reconnect

dev=> \c dev

psql (14.3, server 14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "dev" as user "postgres".
dev=> show search_path;
       search_path
--------------------------
 "$user", appuser, public
(1 row)