Conversion failed: "(-122.763091,49.04676)" to geography (location)

Question

I am trying to import a CSV file into PostgreSQL with a column named location that contains coordinates in the format (-longitude,latitude). However, I am getting the following error:

2:36846: conversion failed: "(-122.763091,49.04676)" to geography (location)
3:37257: conversion failed: "(-123.141289,49.272057)" to geography (location)
4:36495: conversion failed: "(-122.850334,49.189992)" to geography (location)

I have checked the following:

• The SRID is set to 4326.
• The values in the location column are in the correct format (-longitude,latitude).
• There are no invalid or incomplete values in the location column.

I am using PostgreSQL 14 and PostGIS 3.2.

Example:

The following is an example of the data in my CSV file:

-122.763091,49.04676
-123.141289,49.272057
-122.850334,49.189992

What am I doing wrong?

Answer 1

If this example resembles how you keep those geographies in the CSV file:

id,description,geog
1,"description1","(-122.850334,49.189992)"

Then reading the third field to geography type column won't work because that's not a valid geography constant. None of these would work either:

id,description,geog
1,"description1","(-122.850334 49.189992)"
1,"description1","-122.850334,49.189992"
1,"description1","-122.850334 49.189992"

This would:

id,description,geog
1,"description1","point(-122.850334 49.189992)"

You could change your column to text, import it as is, prepend the point, swap out the comma , for a space , then alter the type back to geography(point,4326). Demo at db<>fiddle:

create table my_table (id int, description text, geog geography(Point,4326));
--preparing a test file:
copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';

alter table my_table 
  alter column geog type text;

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

select *,pg_typeof(geog) from my_table;

id	description	geog	pg_typeof
1	abc	(-122.850334,49.189992)	text

update my_table 
  set geog='point'||replace(geog,',',' ')
  returning *,pg_typeof(geog);

id	description	geog	pg_typeof
1	abc	point(-122.850334 49.189992)	text

alter table my_table 
  alter column geog type geography(point,4326) 
  using geog::geography(point,4326);

select *,pg_typeof(geog),st_astext(geog) from my_table;

id	description	geog	pg_typeof	st_astext
1	abc	0101000020E6100000522B4CDF6BB65EC0354069A851984840	geography	POINT(-122.850334 49.189992)

---

You could also leverage the fact that those values are valid point type constants - that's PostgreSQL built-in point type, not to be confused with PostGIS geometry(point). Postgres point can accept those values directly, and then there' a predefined cast from that to PostGIS geometry(point), and from that to geography(point):

truncate my_table;
copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';
alter table my_table
  drop column if exists geog,
  add column geog point;

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

alter table my_table 
  alter column geog type geography(point,4326) 
  using geog::geometry(point)::geography(point,4326);