Postgres: Insert If Does Not Exist Already

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

hope that helps.-

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

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

Select query, insert into table, and return initial query in postgres

A set-returning PL/pgSQL function builds the return stack while processing the function body. There is no way to access that return stack from within the same function. You could nest the function. Or use a temporary table.

But using a CTE is probably the simplest way for the cas at hand. Going out on a limb, you may be looking for something like this:

CREATE OR REPLACE FUNCTION demo(query_user_id int, query_domain_url text)
RETURNS TABLE (c1 int, c2 int)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY
WITH sel AS (
SELECT d.id, w.id as page_id ...
FROM webpages w
JOIN domains d ON d.id = w.domain_id
LEFT JOIN domain_settings ds ON ds.domain_id = d.id
LEFT JOIN subscriptions s ON s.page_id = w.id
AND s.user_id = query_user_id -- origin?
AND s.comment_id IS NULL
WHERE d.domain_address = query_domain_url -- origin?
)
, ins AS (
INSERT INTO tbl (col1, col2)
SELECT main.id, sel.page_id
FROM (SELECT 'foo') AS main(id)
LEFT JOIN sel USING (id) -- LEFT JOIN ?
)
TABLE sel;

IF NOT FOUND THEN
-- do something
END IF;
END
$func$;

Remember, if the transaction does not commit successfully, the INSERT is also rolled back.

The final TABLE sel is just short syntax for SELECT * FROM sel. See:

  • Is there a shortcut for SELECT * FROM?


Related Topics



Leave a reply



Submit