Postgresql Insert If Not Exists

Postgresql insert if not exists

Don't put the columns in parentheses.

If you look at the full error message you get, then Postgres actually tells you what was wrong.

ERROR: INSERT has more target columns than expressions

Hint: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?

The expression ('Wow', 'wow') is just a single column, an anonymous "record" with two variables (See the manual for details)

INSERT INTO tags (name, slug)
SELECT 'Wow', 'wow'
WHERE NOT EXISTS (SELECT id FROM tags WHERE slug = 'wow')
RETURNING id;

In general it's a good idea to add parentheses only if they are really required

SQL Postgres - INSERT INTO WHERE NOT EXISTS

Use a SELECT as the source of the INSERT:

with data as (
  SELECT id_client, 212121 as opr_wpr, now() as data_wpr
  FROM tableB 
  WHERE id = 272
)
INSERT INTO tablea(id_client, opr_wpr, data_wpr)
SELECT *
FROM data
WHERE not exists (select * 
                  from tablea
                  where id_client in (select id_client
                                      from data));

The common table expression is used so that the source condition only needs to be provided once.

---

Alternatively if you have a unique constraint on tablea.id_client, then you can simply do:

INSERT INTO tablea(id_client, opr_wpr, data_wpr)
SELECT id_client, 212121, now()
FROM tableB 
WHERE id = 272
on conflict (id_client) do nothing;

Insert values if records don't already exist in Postgres

In Postgres, there is a really nice way to do that:

INSERT INTO keys(name, value) 
    SELECT 'blah', 'true'
WHERE NOT EXISTS (
    SELECT 1 FROM keys WHERE name='blah'
);

Postgresql insert if does not exist

Instead of nesting the INSERTs, you could use a CTE
to perform the INSERTs one after the other but as a single statement:

WITH tmp AS (
    INSERT INTO test_city (city) VALUES ('somecity')
    ON CONFLICT (lower(city)) DO UPDATE SET city = excluded.city
    RETURNING id, city
)
INSERT INTO test_address (house_number, street, city_id)
SELECT house_number, street, id
FROM (VALUES (11, 'test st', 'somecity')) val (house_number, street, city)
LEFT JOIN tmp USING (city)
RETURNING *

---

Using this setup:

DROP TABLE IF EXISTS test_address;
DROP TABLE IF EXISTS test_city;
CREATE TABLE test_address (
    house_number int
    , street text
    , city_id int
    );
CREATE TABLE test_city (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
    , city text 
    );
CREATE UNIQUE INDEX test_city_uniq_idx ON test_city USING btree (lower(city));
INSERT INTO test_city (city) VALUES ('Somecity');

and with the INSERT above, the query

SELECT * FROM test_address;

yields

| house_number | street  | city_id |
|--------------+---------+---------|
|           11 | test st |       1 |

and

SELECT * FROM test_city;

yields

| id | city     |
|----+----------|
|  1 | somecity |

---

Note that the CTE replaces

(select id from city where LOWER(city) = LOWER('somecity'))

with an INSERT .. ON CONFLICT .. DO UPDATE statement:

INSERT INTO test_city (city) VALUES ('somecity')
ON CONFLICT (lower(city)) DO UPDATE SET city = excluded.city
RETURNING id, city

I used DO UPDATE instead of DO NOTHING so that RETURNING id, city will always return something. If you use DO NOTHING, then nothing is returned when there is a conflict.

Note however that a consequence of using city = excluded.city is that the original 'Somecity'
gets replaced by 'somecity'. I'm not sure you'll find that behavior acceptable, but unfortunately I haven't figured out how to do nothing when there is a conflict and yet return id and city at the same time.

---

Another issue you may have with the above solution is that I used a unique index on lower(city):

CREATE UNIQUE INDEX test_city_uniq_idx ON test_city USING btree (lower(city));

This allows you to use the identical condition in the INSERT statement:

INSERT ... ON CONFLICT (lower(city))

as a substitute for the condition LOWER(city) = LOWER('somecity') which appeared in your SELECT statement. It produces the desired effect, but the trade-off is that now you have a unique index
on (lower(city)).

---

Regarding the followup question
of how to insert into more than 2 tables:

You can chain together more than one CTE, and the subsequent CTEs can even reference the prior CTEs. For example,

CREATE UNIQUE INDEX city_uniq_idx ON city USING btree (lower(city));
CREATE UNIQUE INDEX state_uniq_idx ON state USING btree (lower(state_code));

WITH tmpcity AS 
(
   INSERT INTO
      city (city) 
   VALUES
      (
         'Miami'
      )
      ON CONFLICT (lower(city)) DO 
      UPDATE
      SET
         city = excluded.city RETURNING id, city
)
, tmpstate as 
(
   INSERT INTO
      state (state_code) 
   VALUES
      (
         'FL'
      )
      ON CONFLICT (lower(state_code)) DO 
      UPDATE
      SET
         state_code = excluded.state_code RETURNING id, state_code
)
INSERT INTO
   address (house_number, street, city_id, state_id) 
   SELECT
      house_number,
      street,
      tmpcity.id,
      tmpstate.id 
   FROM
      (
      VALUES
         (
            12,
            'fake st.',
            'Miami',
            'FL'
         )
      )
      val (house_number, street, city, state_code) 
      LEFT JOIN
         tmpcity USING (city) 
      LEFT JOIN
         tmpstate USING (state_code)
         ON CONFLICT (street) DO NOTHING

Insert if not exists, else return id in postgresql

Yes there is returning

INSERT INTO tag ("key", "value")
SELECT 'key1', 'value1'
WHERE NOT EXISTS (
    SELECT id, "key", "value"
    FROM node_tag
    WHERE key = 'key1' AND value = 'value1'
    )
returning id, "key", "value"

To return the row if it already exists

with s as (
    select id, "key", "value"
    from tag
    where key = 'key1' and value = 'value1'
), i as (
    insert into tag ("key", "value")
    select 'key1', 'value1'
    where not exists (select 1 from s)
    returning id, "key", "value"
)
select id, "key", "value"
from i
union all
select id, "key", "value"
from s

If the row does not exist it will return the inserted one else the existing one.

BTW, if the pair "key"/"value" makes it unique then it is the primary key, and there is no need for an id column. Unless one or both of the "key"/"value" pair can be null.

---

Related Topics