1

I'm figuring out how to use the PostGIS extension, and I found a nice tutorial: http://postgis.net/workshops/postgis-intro/geometries.html.

First the tutorial directs to create a table:

CREATE TABLE geometries (
    name varchar,
    geom geometry
);

Then insert into that table

insert into geometries (name, geom)
values
    ('Point', 'point(0 0)');

Then, looking at what other column types I can use with the PostGIS addon, I found that I can use the point type directly:

create table points (
    name varcar,
    point point
);
insert into points (name, point)
values 
    ('point 1', point(0, 1));

(Notice that the inserted value into the point column is NOT wrapped in quotes)

Trying to insert a point into the geometries table without wrapping the value in a quote fails:

insert into geometries (name, geom)
values
    ('Point', point(0 0));

SQL Error [42601]: ERROR: syntax error at or near "0"

This seems like it might be significant... If a column of type point allows for inserting values defined via that type (point(0 0)) - I assume this isn't a normal SQL function call? -, why can't I do the same for a column of type geometry?

  • 1
    you mix up a few things; the plain POINT type is a native PostgreSQL geometric type, independent from PostGIS and incompatible with it's functionality. Most types come with built in (implicit) type cast from common input types; this is why PostGIS GEOMETRY type accepts a valid WKT string representation. – ThingumaBob Jul 18 at 13:39
2

the point is not a PostGIS type, but a native PostgresSQL type. The two are not compatible.

Creating a geometry from a string is one way. You can of course create a (PostGIS) point geometry via ST_Point or ST_MakePoint

select st_point(1,1);
1

For either type, you could insert with or without quotes. What caused the error was the missing comma (which is required only in SQL):

postgres=# select point(0 0);
ERROR:  syntax error at or near "0"
LINE 1: select point(0 0);
                       ^
postgres=# select point(0, 0);
 point
-------
 (0,0)
(1 row)

(For the geometry type, the point value would be automatically converted.)

  • According to the answers above... this is misleading as it implies the point type and 'point' geometry type are comparable. which the other answers say isn't correct – Zach Smith Jul 19 at 12:49
  • You could force the cast (point(0,0)::geometry) but it is not automatic – JGH Jul 19 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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