7

I'm quite new to postgresql and postgis so this error will probably due to some rookie mistake.

I want to run the spatial query ST_within. When I describe the query like this

SELECT nw.entity_id AS NavElem_id, nw.featuretype, nw.processingstatus AS NavElem_ProcessingStatus, nw.frc, nw.geometry AS NavElem_geom, aaa.processingstatus AS AA_processingstatus, aaa.networktype, aaa.geometry AS AA_geom, aaa.entity_id AS AA_id FROM sml.network nw, sml.aa_geo_att aaa WHERE ST_Within (nw.geometry, aaa.geometry);

It works fine, however I do not want to provide the schema name everytime (in this case sml.), I want to make it generic so that I can use it in an sql file.

When I try to set the schema path in a first query and the ST_within in second query

SET search_path TO sml;
SELECT nw.entity_id AS NavElem_id, nw.featuretype, nw.processingstatus AS NavElem_ProcessingStatus, nw.frc, nw.geometry AS NavElem_geom, aaa.processingstatus AS AA_processingstatus, aaa.networktype, aaa.geometry AS AA_geom, aaa.entity_id AS AA_id
FROM network nw, aa_geo_att aaa
WHERE ST_Within (nw.geometry, aaa.geometry);

I get the following error:

ERROR:  function st_within(public.geometry, public.geometry) does not exist
LINE 5: WHERE ST_Within (nw.geometry, aaa.geometry);
          ^
HINT:  No function matches the given name and argument types. You might need to add   explicit type casts.
********** Error **********

ERROR: function st_within(public.geometry, public.geometry) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 311

Even though I set the schema, ST within is looking in the public schema, which I don't want.

Improve this question
1
  • Piggybacking on a comment by LR1234567 on a previous answer, the proper syntax for the ALTER DATABASE command as of PostgreSQL 9.x is: ALTER DATABASE my_database_name SET search_path = "$user", public, schema1, schema2; See postgresql.org/docs/9.6/static/sql-alterdatabase.html (I would have simply commented after his comment, but I don't have enough reputation.)
    – MartinThurn
    Commented Mar 10, 2018 at 16:39

2 Answers 2

Reset to default
14

Your problem is that you have excluded the public schema from the search_path. All functions and types is in the public schema (by default)

So what you have to do is just include the public schema in the search_path

SET search_path TO sml, public;

means that the database will write to sml if you don't give any schema

SET search_path TO public, sml;

means that the database will write to public if no schema is given.

But in both cases both schemas will be searched for tables, functions and so on

Improve this answer
3
  • 1
    So everytime I change the search_path to a schema, I also need to include the ,public? It works now, thank you
    – freakyfreak
    Commented Jan 28, 2015 at 10:21
  • 1
    @freakyfreak Yes. In fact, that mirrors the default search path used by PostgreSQL: $user, public.
    – Scro
    Commented Jan 28, 2015 at 13:17
  • 1
    @Freakyfreak: If you are not happy with the database search path, you can change it with: ALTER DATABASE SET search_path TO "$user", public, schema1, schema2, ...; Then PostgreSQL will search the paths in that order. You could also prefix the public schema on the function with something like public.ST_Within (though I personally don't do that).
    – Regina Obe
    Commented Jan 28, 2015 at 15:21
1

CREATE EXTENSION postgis_sfcgal;

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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