PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to change the `search_path` of my postgres database?

TLDR

Running

set search_path to "$user",public,extensions,gis;

doesn’t have any effect.

I’m new to the world of databases so excuse me if this is a simple question but I’m developing an app for personal use using Python, especifically Fast-API and SQLalchemy, my database is in supabase. One of my tables in the public schema has a postgis geography column. To install postgis I followed this supabase tutorial where they say to install the extension in the gis schema. Everything works ok but when I try to add or retrieve data using SQLalchemy (also using geoalchemy2 an extension especifically designed to work with postgis) it runs queries not taking into account the schema where the extension is located. One example of this is:

SELECT
      my_table.id AS my_table_id,
      ST_AsBinary (my_table.punto) AS my_table_punto,
    FROM
      my_table
    WHERE
      my_table.id = 40

The ST_AsBinary function is from postgis. This query returns the following error:

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

If I run the query in supabase with gis.st_asbinary it runs ok, the problem is that I can’t modify the queries that SQLalchemy do.

I have read the postgre documentation and run the following query:

    show search_path;

and the result is: "$user", public, extensions as expected but then running

    set search_path = '"$user",public,extensions,gis';

Tells me Success. No rows returned but If I run the show search_path; query again I see that nothing happends.

Also I have run this

set search_path to "$user",public,extensions,gis;

but the result is the same.

Tags:

2

Answers


  1. 0

    You should use

    SET search_path TO "$user", public, extensions, gis;

    to change the search path for the current session. If you want to make the change persistent, you have to change the value in the database

    ALTER DATABASE xxx SET search_path TO "$user", public, extensions, gis;

    where xxx is the name of the database.

    Note that a parameter changed with ALTER DATABASE only takes effect for database sessions started after the setting was changed, so you need to reconnect to get the changed parameter.

    Login or Signup to reply.
  2. 0

    When you use SET search_path, it applies only to the current session and does not persist across new connections. set search path for user and Database accordingly for example:

    ALTER ROLE USER SET search_path TO "$user", public, extensions, gis;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search