I am trying to add a geometry column to an existing table in PostgreSQL inside a plpgsql function using

EXECUTE format('ALTER table %I add column geom geometry(Point, 102003)', target_table);

Its raising an error "type "geometry" does not exist". enter image description here

There is PostGIS extension installed in the database which I am running. Only thing is my table are not in public schema.

  • can you edit this post to share the screenshot of error? – Asad Abbas May 17 '19 at 7:36
  • Don't you need to prefix the type with the schema if you choose other than public? – Vince May 17 '19 at 10:37
  • Or you have to open the SQL Query window from within the public schema ... that helps sometimes (in pgAdmin III) – leole May 17 '19 at 10:45
  • it could be the search path gis.stackexchange.com/questions/284067/… – JGH May 17 '19 at 11:17

You can solve this issue by executing the following query in pgAdmin


Hope this answers your question.

  • Already thats done and extension was created – User123 May 17 '19 at 8:15
  • perhaps there were some issues while creating postgis extension you need to drop postgis extension using "drop extension postgis" and re create it. – Asad Abbas May 17 '19 at 8:19
  • Well, i tried that too. Extension is getting created successfully – User123 May 17 '19 at 8:52
  • are you still getting the same error? I have tried the query you shared and it works fine. If you are still getting error you can use AddGeometryColumn function of postgis, you can refer to postgis.net/docs/AddGeometryColumn.html for further details – Asad Abbas May 17 '19 at 9:20

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