INSERT using SELECT from potentially empty table

Question

I have a postgres database schema where newly inserted rows reference other rows. The skeleton of the table is:

CREATE TABLE table_1 (
    f1    UUID PRIMARY KEY,
    f2    UUID NOT NULL,
    f3    text NOT NULL,
    f4    text NOT NULL DEFAULT '',
    f5    text NOT NULL DEFAULT '',
    f6    UUID NULL,
);

This is the INSERT query I'm using:

INSERT INTO table_a (f1, f2, f3, f4, f5, f6)
SELECT               $1, $2, $3, f4, f5, f2
FROM        table_a
WHERE       <conditionals>
ORDER BY    <ordering>
LIMIT       1

Basic idea is that I look for an existing row that satisfies the conditions. If one exists, use fields f4, f5, and f2 to fill fields f4, f5, and f6 in the new row being inserted. If one does not exist, those fields should instead take on their default values ('', '', and NULL, respectively).

Problem is, if no row is found, the SELECT itself seems to return no results, and the entire insertion is simply skipped. It works fine if a row matches the conditionals, but not if no rows are returned by that SELECT. How can I get the desired behavior?

Clodoaldo Neto · Accepted Answer · 2016-02-02 18:57:38Z

4

insert into table_a (f1, f2, f3, f4, f5, f6)
select f1, f2, f3, f4, f5, f6
from (
    (
        select $1, $2, $3, f4, f5, f2, 1
        from table_a
        where <conditionals>
        order by <ordering>
        limit 1
    ) s
    union all
    select $1, $2, $3, '', '', null, 2
) s (f1, f2, f3, f4, f5, f6, o)
order by o
limit 1

edited Feb 2, 2016 at 18:57

answered Feb 2, 2016 at 18:51

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

Abelisto · Accepted Answer · 2016-02-02 19:39:39Z

1

Just another way if you want to use default value from table DDL instead of hardcoded in the query:

WITH t AS (
  INSERT INTO table_a (f1, f2, f3, f4, f5, f6)
  SELECT               $1, $2, $3, f4, f5, f2
  FROM        table_a
  WHERE       <conditionals>
  ORDER BY    <ordering>
  LIMIT       1
  RETURNING *) -- This query will return inserted values
INSERT INTO table_a (f1, f2, f3)
SELECT $1, $2, $3 WHERE (SELECT count(*) FROM t) = 0; -- Insert if no insertion was happened at WITH clause

answered Feb 2, 2016 at 19:39

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Collectives™ on Stack Overflow

INSERT using SELECT from potentially empty table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related