query two point values from a table then insert a new record after creating a line from the two points

Question

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?

Answer 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;

Answer 2

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;

Answer 3

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');