0

Here is my problem :

Actually I am using postgresql 9.4 and postGIS extension.

I am creating a database, and adding new tables with no problems... with geometry data types... until now...

Now, I don't know why, I suddently have to "choose schema" to get my data-type...

I am using pgadminIII to create my table, so I add a new table, and when I try to add a new column, I write the column name, then in data-type I try to type "geometry" and it does not work anymore... i have now to write "public.geometry" since the geometry type is registered in the public schema... I'm at the 30th table, and on the previous ones I didn't have to do that, the manager recognized directly the geometry type...

The postgis extension is effective and registered on my db (functions & extension ok),

Anybody has an idea on :

  • How I got this... why is it happening now...
  • How to solve that... (it's quite boring to type that, AND I'm afraid that when I will have to make queries on this table I will have troubles calling postgis functions without giving the schema name...)

Thanks for reading/help

Edit :

Here is the search path :

result of SHOW search_path

Here is what I mean when I say "public."dataType :

DataTypes list

1 Answer 1

1

It sounds like your search_path has somehow gotten changed. The default schema search path is "$user",public which means if you use an unqualified identifier, it first looks for it in a schema named the same as your user, and if not found, tries the public schema.

You can check your search path with

SHOW search_path;

And set the search path back to the default with

SET search_path TO DEFAULT;

The culprit that is changing your search_path might be hard to identify, it's likely a function that is calling SET search_path... instead of SET LOCAL search_path, but using SET is restricted to a single session, if you start a new session, it should be reset back to the default. If that doesn't fix it, you might need to check the postgresql.conf file to see if the search_path default has somehow been changed from

search_path = '"$user",public'
3
  • When I do a show search_path on my current database, it shows "$user",public,topology (topology is the postGIS extension schema)... looks like it's ok but even with that, I have to select dataType with pre "public." on it... :/
    – Julo0sS
    Sep 3, 2015 at 11:38
  • Please check my edit, I added 2 screenshots that show clearly what the problem is... Thanks already for your search_path idea :)
    – Julo0sS
    Sep 3, 2015 at 11:44
  • @Julo0sS sorry, it sounds like it's some other problem with pgadmin, which I'm not familiar with.
    – DerfK
    Sep 4, 2015 at 19:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .