insert...select statement in postgresql combining inserted values from php with a select statement

Question

I have seen some SO posts on how to insert selected values, but they are always in cases where all inserted values will come from the select statement. I am struggling to insert data that is partially provided by a php call and partially provided by a select statement. Here is what I would like to do:

$query = "INSERT INTO deals (latitude, longitude, interested, id, the_geom) VALUES ($1, $2, $3, $4, SELECT the_geom FROM userlocs WHERE id=$3)";
$rs = pg_query_params($con, $query, array($latitudeS, $longitudeS, $id_pg , $id_add));

This is giving me the following not very helpful error:

 pg_query_params(): Query failed: ERROR: syntax error at or near "SELECT" LINE 1: ...interested, id, the_geom) VALUES ($1, $2, $3, $4, SELECT the... ^ in /var/www/html/join.php on line 30

Is it possible to do what I would like to do? If so, how do I modify my syntax?

Many thanks for any suggestions.

try $query = "INSERT INTO deals (latitude, longitude, interested, id, the_geom) VALUES SELECT $1, $2, $3, $4, the_geom FROM userlocs WHERE id=$3"; — Sean
– Sean, Commented Jan 27, 2015 at 1:58
@Sean thanks for the suggestion, but this is giving me the same "syntax error" message. — Beeb
– Beeb, Commented Jan 27, 2015 at 2:04
it looks like postgresql does not use the VALUES when doing INSERT ... SELECT, so try $query = "INSERT INTO deals (latitude, longitude, interested, id, the_geom) SELECT $1, $2, $3, $4, the_geom FROM userlocs WHERE id=$3"; — Sean
– Sean, Commented Jan 27, 2015 at 2:08

peterm · Accepted Answer · 2015-01-27 02:21:30Z

1

Probably the easiest fix is to just put your SELECT statement in parentheses

INSERT INTO deals (latitude, longitude, interested, id, the_geom) 
VALUES ($1, $2, $3, $4, (SELECT the_geom FROM userlocs WHERE id = $3))

Here is a SQLFiddle demo

Depending on whether you always expect a value coming from userlocs or not you can also use INSERT ... SELECT syntax in one of two ways

INSERT INTO deals (latitude, longitude, interested, id, the_geom) 
SELECT $1, $2, $3, $4, the_geom FROM userlocs WHERE id = $3

or

INSERT INTO deals (latitude, longitude, interested, id, the_geom) 
SELECT $1, $2, $3, $4, (SELECT the_geom FROM userlocs WHERE id = $3)

edited Jan 27, 2015 at 2:21

answered Jan 27, 2015 at 2:08

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jun Rikson Over a year ago

maybe max(the_geom) helping.

peterm Over a year ago

@ColourDalnet For what possible reason one would want to use an aggregate in this case?

Jun Rikson Over a year ago

if have more than 1 results.

peterm Over a year ago

@ColourDalnet Fair enough. But it only make sense if id for some reason is not the PK. A better alternative in that situation might be to use LIMIT 1.

Jun Rikson Over a year ago

Yes, that is just the case id is not primary key, for example many-to-many relationship table. LIMIT 1 is better combined with ORDER BY :)

Collectives™ on Stack Overflow

insert...select statement in postgresql combining inserted values from php with a select statement

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related