My PostgreSQL Notes - Revision history

Paul: /* PostgreSQL Resources */

2018-08-31T23:39:41Z

PostgreSQL Resources

← Older revision		Revision as of 18:39, 31 August 2018
Line 2:		Line 2:

	[https://www.postgresql.org/ PostgreSQL] official site		[https://www.postgresql.org/ PostgreSQL] official site

			[https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools GUI Database Design Tools]

	== INSERT & UPDATE Stuff ==		== INSERT & UPDATE Stuff ==

Paul: /* INSERT & UPDATE Stuff */

2018-07-18T15:58:03Z

INSERT & UPDATE Stuff

← Older revision		Revision as of 10:58, 18 July 2018
Line 15:		Line 15:
	e.g. given setup:		e.g. given setup:

	CREATE TABLE tablename (a integer primary key, b integer, c integer);		<pre>CREATE TABLE tablename (a integer primary key, b integer, c integer);
	INSERT INTO tablename (a, b, c) values (1, 2, 3);		INSERT INTO tablename (a, b, c) values (1, 2, 3);</pre>

	the MySQL query:		the MySQL query:

	INSERT INTO tablename (a,b,c) VALUES (1,2,3)		<pre>INSERT INTO tablename (a,b,c) VALUES (1,2,3)
	ON DUPLICATE KEY UPDATE c=c+1;		ON DUPLICATE KEY UPDATE c=c+1;</pre>

	becomes:		becomes:

	INSERT INTO tablename (a, b, c) values (1, 2, 10)		<pre>INSERT INTO tablename (a, b, c) values (1, 2, 10)
	ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;		ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;</pre>

	Differences:		Differences:

	You must specify the column name (or unique constraint name) to use for the uniqueness check. That's the ON CONFLICT (columnname) DO		*You must specify the column name (or unique constraint name) to use for the uniqueness check. That's the ON CONFLICT (columnname) DO
			*The keyword SET must be used, as if this was a normal UPDATE statement
	The keyword SET must be used, as if this was a normal UPDATE statement

	It has some nice features too:		It has some nice features too:

	You can have a WHERE clause on your UPDATE (letting you effectively turn ON CONFLICT UPDATE into ON CONFLICT IGNORE for certain values)		*You can have a WHERE clause on your UPDATE (letting you effectively turn ON CONFLICT UPDATE into ON CONFLICT IGNORE for certain values)
			*The proposed-for-insertion values are available as the row-variable EXCLUDED, which has the same structure as the target table. You can get the original values in the table by using the table name. So in this case EXCLUDED.c will be 10 (because that's what we tried to insert) and "table".c will be 3 because that's the current value in the table. You can use either or both in the SET expressions and WHERE clause.
	The proposed-for-insertion values are available as the row-variable EXCLUDED, which has the same structure as the target table. You can get the original values in the table by using the table name. So in this case EXCLUDED.c will be 10 (because that's what we tried to insert) and "table".c will be 3 because that's the current value in the table. You can use either or both in the SET expressions and WHERE clause.

	For background on upsert see [https://stackoverflow.com/q/17267417/398670 How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?]		For background on upsert see [https://stackoverflow.com/q/17267417/398670 How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?]

	<center>[[Computing\|To Computing]]</center>		<center>[[Computing\|To Computing]]</center>

Paul: /* PostgreSQL Resources */

2018-07-18T15:55:59Z

PostgreSQL Resources

← Older revision		Revision as of 10:55, 18 July 2018
Line 1:		Line 1:
	== PostgreSQL Resources ==		== PostgreSQL Resources ==

			[https://www.postgresql.org/ PostgreSQL] official site

	== INSERT & UPDATE Stuff ==		== INSERT & UPDATE Stuff ==

Paul: Created page with "== PostgreSQL Resources == == INSERT & UPDATE Stuff == [https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291 Stack Overflow I..."

2018-07-18T15:55:27Z

Created page with "== PostgreSQL Resources == == INSERT & UPDATE Stuff == [https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291 Stack Overflow I..."

New page

== PostgreSQL Resources ==

== INSERT & UPDATE Stuff ==

[https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291 Stack Overflow INSERT on DUPLICATE UPDATE discussion]

In PostgreSQL 9.5 and newer you can use INSERT ... ON CONFLICT UPDATE.

[http://www.postgresql.org/docs/devel/static/sql-insert.html#SQL-ON-CONFLICT See the documentation].

A MySQL INSERT ... ON DUPLICATE KEY UPDATE can be directly rephrased to a ON CONFLICT UPDATE. Neither is SQL-standard syntax, they're both database-specific extensions. There are good reasons MERGE wasn't used for this, a new syntax wasn't created just for fun. (MySQL's syntax also has issues that mean it wasn't adopted directly).

e.g. given setup:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

the MySQL query:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

becomes:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

Differences:

You must specify the column name (or unique constraint name) to use for the uniqueness check. That's the ON CONFLICT (columnname) DO

The keyword SET must be used, as if this was a normal UPDATE statement

It has some nice features too:

You can have a WHERE clause on your UPDATE (letting you effectively turn ON CONFLICT UPDATE into ON CONFLICT IGNORE for certain values)

The proposed-for-insertion values are available as the row-variable EXCLUDED, which has the same structure as the target table. You can get the original values in the table by using the table name. So in this case EXCLUDED.c will be 10 (because that's what we tried to insert) and "table".c will be 3 because that's the current value in the table. You can use either or both in the SET expressions and WHERE clause.

For background on upsert see [https://stackoverflow.com/q/17267417/398670 How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?]

<center>[[Computing|To Computing]]</center>