Postgres Schema Issue

Question

This is the default behavior for PostgreSQL is a search path like so:

SHOW search_path;

search_path
--------------
"$user",public

Is there a way to make it so the "$user" is case insensitive?

For instance I have a user tp1, and a schema TP1... I'd like them both to be seen as "equal"

Is this even possible?

Answer 1

Don't ever create case-sensitive stuff, period. Ever. It is ultimately confusing and will force you to put them in quotes when you use them. You can be sure your stuff isn't case-sensitive by just not using double quotes at all. This is like a cardinal rule in Pg.

In short no you can't, but you can add to your search_path.

test=# create schema "ECARROLL";
CREATE SCHEMA
test=# create table "ECARROLL".foo ( foobar int );
CREATE TABLE
test=# select * from foo;
ERROR:  relation "foo" does not exist

If you wish you can hard code your other schema: SET search_path TO "$user","ECARROLL",public;

You can script a query of the case-insensitive named schemas and set them in the scripts environment.

test=# SELECT schema_name FROM information_schema.schemata WHERE schema_name ILIKE current_user;
 schema_name 
-------------
 ECARROLL