1

I want to do a INSERT with parameters and some optional values from another table. (With optional I mean that if the subquery fails I want to insert only the parameter values instead of inserting nothing.)

My first attempt was:

INSERT INTO my_table (foo, bar, something)
VALUES (:param, (SELECT bar, something FROM other_table WHERE (foo = :param));

but did not work. The error message was "subselect must have only one field".

Another approach, inspired by this question was:

INSERT INTO my_table (foo, bar, something)
SELECT :param, o.bar, o.something FROM other_table o WHERE o.foo = :param;

But this only performs an INSERT if it finds something for the WHERE clause. So it behaves differently from a real subquery. So I came up with this one:

INSERT INTO my_table (foo, bar, something)
VALUES (:param,
       (SELECT bar FROM other_table WHERE (foo = :param),
       (SELECT something FROM other_table WHERE (foo = :param));

Which works but of course looks unnecessary slow because of two subselects.

So my question is: Can I somehow get this to work with only one subselect.

Sebastian
  • 5,721
  • 3
  • 43
  • 69

2 Answers2

1

One way to do it:

INSERT INTO my_table (foo, bar, something)
SELECT :param, bar, something
FROM generate_series(1, 1)
LEFT JOIN (
    SELECT bar, something 
    FROM other_table 
    WHERE foo = :param
) x ON TRUE

This is assuming you always want 1 record inserted, and you will have either no or one result for the other_table query (or you actually do want that same param to be inserted for all the results, if the subquery returns more than 1 result).

404
  • 8,022
  • 2
  • 27
  • 47
1

You can left join a values subquery with a regular select:

insert into my_table (foo, bar, something)
select new_foo, bar, something
from ( 
    values (:param)
    ) param (new_foo)
left join (
    select foo, bar, something
    from other_table 
    ) other on new_foo = foo
klin
  • 112,967
  • 15
  • 204
  • 232