Parse Error on PostgreSQL when using ST_AsText

Question

I am new to using POSTGIS and PostgreSQL.

I am trying to convert PostGIS coordinates into lat and long values. When I copy one of the values and query like

SELECT ST_AsText('0101000000A7E8482EFFC55B402063EE5A42064140');

The query runs without an error. But when I try the query for the entire table column like

SELECT ST_AsText(GPS) from schema.table;

I am getting an error saying

" 0101000000A7E8482EFFC" <-- parse error at position 22 within geometry

The column data type is text. I am unsure why the ST_AsText is unable to read the whole text and stops at position 21.

Additional information:
PostgreSQL version : 10.7
pgAdmin : 4.5
PostGIS: 2.4.3

I am using Python SQLAlchemy to Insert the tables directly into PostgreSQL.

Answer 1

The error message mentions " 0101000000A7E8482EFFC", with a trailing space, which makes the geometry invalid.

The dirty solution is to trim your data

SELECT ST_AsText(TRIM(GPS)) from schema.table;

with src as (select ' 0101000000A7E8482EFFC55B402063EE5A42064140'::text txt)
select st_asText(txt::geometry) from src;

ERROR:  parse error - invalid geometry
HINT:  " 0101000000A" <-- parse error at position 12 within geometry

with src as (select ' 0101000000A7E8482EFFC55B402063EE5A42064140'::text txt)
select st_asText(trim(txt)) from src;

        st_astext
-------------------------
 POINT(111.0937 34.0489)
(1 row)

The clean solution, as suggested in the comments by @ThingumBob, is to have a real geometry column and to sanitize what you put in it.