CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
rrlamichhane
rrlamichhane

Reputation: 1675

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.

Upvotes: 1

Views: 2339

Answers (2)

jjanes
jjanes

Reputation: 44147

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".

Upvotes: 1

Haleemur Ali
Haleemur Ali

Reputation: 28243

The default search path should look like `"$user", public'", meaning that tables / views in the public schema may be referenced without specifying the schema name, and the server will first search through the schema that matches the role name, and then the public schema. Evidently this has been altered on your server.

It is possible to set the search_path for each user. The alter user command would look like.

alter user username set search_path = 'public'

Upvotes: 1

Related Questions