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?
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
