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

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'
  • 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 '15 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 '15 at 11:44
  • @Julo0sS sorry, it sounds like it's some other problem with pgadmin, which I'm not familiar with. – DerfK Sep 4 '15 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.