StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>PostgreSQL since version 9.5 has <a href="http://www.postgresql.org/docs/devel/static/sql-insert.html" rel="noreferrer">UPSERT</a> syntax, with <strong><a href="http://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT" rel="noreferrer">ON CONFLICT</a> clause.</strong> with the following syntax (similar to MySQL)</p> <pre><code>INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2; </code></pre> <hr> <p>Searching postgresql's email group archives for "upsert" leads to finding <a href="http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE" rel="noreferrer">an example of doing what you possibly want to do, in the manual</a>:</p> <blockquote> <p><strong>Example 38-2. Exceptions with UPDATE/INSERT</strong></p> <p>This example uses exception handling to perform either UPDATE or INSERT, as appropriate:</p> </blockquote> <pre><code>CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key -- note that "a" must be unique UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis'); </code></pre> <hr> <p>There's possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the <a href="http://archives.postgresql.org/pgsql-hackers/2011-06/msg01826.php" rel="noreferrer">hackers mailing list</a>:</p> <pre><code>WITH foos AS (SELECT (UNNEST(%foo[])).*) updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id) INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id) WHERE updated.id IS NULL; </code></pre> <p>See <a href="https://stackoverflow.com/a/8702291/642706">a_horse_with_no_name's answer</a> for a clearer example.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload