0

I am running postgres: 9.5.19

I have already point values as separate records in a table. I want to create a new record which represents a line geom derived from the two points above. I have lots of point records but I want to be able to select by id each point to link together.

I am trying this syntax:

insert into locations (geom,type,label,user) 
select st_makeline(a.geom,b.geom) as line from (select * from locations where id = 144) as a, (select * from locations where id = 145) as b, 
'Link','test','ADMIN';

and get this error:

ERROR: syntax error at or near "'Link'"
LINE 1: ...a, (select * from locations where id = 145) as b, 'Link','te...

Can someone point me to a working syntax?

1

You have to put the literal strings in the right spot. You have them as part of the FROM clause, which doesn't make sense. Put them in the select-list.

insert into locations (geom,type,label,user) select 
    st_makeline(a.geom,b.geom) as line, 'Link','test','ADMIN'
from (select * from locations where id = 144) as a, 
     (select * from locations where id = 145) as b;
| improve this answer | |
0

Same answer as @jianes, but I prefer ANSI joins instead:

insert into locations 
    (geom, type, label, user) 
select st_makeline(a.geom, b.geom) as line, 'Link','test','ADMIN'
from locations as a
join locations as b
    on a.id = 144
   and b.id = 145;

alternative:

insert into locations 
    (geom, type, label, user) 
select st_makeline(a.geom, b.geom) as line, 'Link','test','ADMIN'
from locations as a
cross join locations as b
where a.id = 144
 and b.id = 145;

For efficiency reasons it may be desirable to push the predicate inside a sub-select (like @jianes does), with an ansi-join that would be:

insert into locations 
    (geom, type, label, user) 
select st_makeline(a.geom, b.geom) as line, 'Link','test','ADMIN'
from ( select geom from locations where id = 144 ) as a
cross join ( select geom from locations where id = 145 ) as b;
| improve this answer | |
0

The a and b subqueries look up completely independent rows, so it is not actually necessary to join them in any way.

You can use them as scalar subqueries instead:

insert into locations (geom, type, label, user) 
select st_makeline((select geom from locations where id = 144),
                   (select geom from locations where id = 145)),
       'Link', 'test', 'ADMIN';

You could also use VALUES() instead of a single-row query:

insert into locations (geom, type, label, user) 
values (st_makeline((select geom from locations where id = 144),
                    (select geom from locations where id = 145)),
        'Link', 'test', 'ADMIN');
| improve this answer | |

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.