PostGIS `COPY` geometry with SRID?

Question

Is it possible to use COPY on a file full of WKT POINTs without dropping the explicit SRID on the destination column?

I'm trying to insert several million rows of WGS84 points and am using COPY for better performance over individual INSERTs.

Content of foo.txt:

POINT(0 0)
POINT(0 1)
POINT(0 2)

Table:

CREATE TABLE foo (
    geom GEOMETRY(POINT, 4326)
);

Command:

COPY foo FROM 'foo.txt';

Error message:

ERROR:  Geometry SRID (0) does not match column SRID (4326)
CONTEXT:  COPY foo, line 1, column geom: "POINT(0 0)"

Versions:

• PostgreSQL 9.2
• PostGIS 2.3

Answer 1

To solve your question, use the following syntax:

Content of foo.txt:

SRID=4326; POINT(0 0)
SRID=4326; POINT(0 1)
SRID=4326; POINT(0 2)

with respect, :-)

Answer 2

To change the inserted data, you can use an INSTEAD OF trigger on a view:

CREATE VIEW bar AS
SELECT ST_AsText(geom) AS geom FROM foo;

CREATE FUNCTION insert_bar() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO foo(geom) VALUES('SRID=4326;' || NEW.geom);
  RETURN NEW;
END;
$$;

CREATE TRIGGER insert_bar
INSTEAD OF INSERT ON bar
FOR EACH ROW
EXECUTE PROCEDURE insert_bar();

COPY bar FROM 'foo.txt';