[SOLVED] How to set postgres and psycopg2 so that it always searches the schema without having to explicitly mention it?

How to set postgres and psycopg2 so that it always searches the schema without having to explicitly mention it?

I have a postgres DB where I have run this command to avoid having to mention schema explicitly:

ALTER DATABASE ibkr_trader SET search_path TO public;

However, when I connect using psycopg2 in python, I still have to type this to access my tables:

select count(*) from "public"."MY_TABLE"

I even tried setting options in psycopg2.connect but it didn't work:

        return psycopg2.connect(
            dbname=self.dbname,
            user=self.user,
            password=self.password,
            port=self.port,
            host=self.host,
            options="-c search_path=public"
        )

What is the most elegant way to set this network up so that I don't have to type "public":"MY_TABLE" for each query? I do not have any other schemas in my DB and I don't wanna have to mention it explicitly.

Solution

Your ALTER DATABASE and your options="-c search_path=public" both work for me. But then again public is usually already in your search path, so neither of them should be needed at all unless you went out of your way to break something.

I suspect you are misinterpreting something. If you try select count(*) from MY_TABLE, that won't work. Not because you are missing the "public", but because you are missing the double quotes around "MY_TABLE" and therefore are searching for the table by a down-cased name of "my_table".