In my table I have a column location that is a point. I tried this:

SELECT ST_X(location), ST_Y(location) FROM locations;

But I still get the error message:

ERROR: function st_x(point) does not exist
  • 1
    If you execute the query SELECT ST_X(ST_GeomFromEWKT('POINT(1 2 3 4)')); does it work or get any error?
    – juanluisrp
    Jun 26, 2013 at 9:46
  • it returns "1". I have the postgis extension added Jun 26, 2013 at 12:09
  • what do you get from "\d locations" and "select * from locations"? paste here the first 5 lines of the latter query
    – Gery
    Jun 26, 2013 at 12:17
  • 1
    The best answer is here gis.stackexchange.com/a/57020 Jul 14, 2021 at 7:51

5 Answers 5


you need to use:

SELECT ST_X(geom), ST_Y(geom) FROM locations;

I suppose that locations is a table of points with the geometry named as "geom".

Hope this helps,

  • Property Value Name location Position 35 Data type point Collation Default Sequence Not NULL? No Primary key? No Foreign key? No Storage PLAIN Inherited No Statistics -1 System column? No I don't understand what you mean with this comment
    – Gery
    Jun 26, 2013 at 12:21
  • my "location" column field table properties...I dont know how to edit this nicely :( Jun 26, 2013 at 13:07

None of these talk about how to extract lat/long with the native point type. Clearly you can't use ST_X() and ST_Y like you can do with a PostGIS Geometery; so here with the native point simply use array-deference syntax [0] and [1]. For example, if you have a native type and you want to go to PostGIS type you can do it like this (from my answer on dba.se).

SELECT ST_MakePoint(p[0],p[1])
FROM ( VALUES (point(-71.1043443253471,42.3150676015829)) ) AS t(p);
  • This helped me because I was looking for Postgres native solution
    – Yuseferi
    Sep 20, 2022 at 23:25

It looks like your column location type is point (PostgreSQL reference). This is a native PostgreSQL type, not an Postgis datatype.

You have to change location column data type to geometry or add a new geometry column with the Postgis function AddGeometryColumn.

  • AddGeometryColumn is dated now you can just ALTER TABLE ADD COLUMN Dec 1, 2016 at 21:10

You can treat the point type like an array to access the x and y components like this:

SELECT location[0], location[1] FROM locations;

Here is the answer:

SELECT ST_X(location::geometry), ST_Y(location::geometry) FROM locations;
  • Thanks for a straight working solution Aug 10, 2023 at 1:06

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.