Short Notes on PSQL - Revision history

Admin: /* Set Sequence Value to Max of Table's ID */

2021-09-17T10:50:05Z

‎Set Sequence Value to Max of Table's ID

Admin at 15:33, 9 July 2021

2021-07-09T15:33:50Z

Admin at 12:10, 2 October 2020

2020-10-02T12:10:19Z

Admin: Created page with "PSQL aka Postgres aka PostgreSQL... I prefer psql. == Dump and Restore ==

dump: $ pg_dump -h host -p 5432 -U user -F c -b -v -f /tmp/db_name.backup db_name restore: $ p..."

2020-04-08T15:46:30Z

Created page with "PSQL aka Postgres aka PostgreSQL... I prefer psql. == Dump and Restore == <pre>dump: $ pg_dump -h host -p 5432 -U user -F c -b -v -f /tmp/db_name.backup db_name restore: $ p..."

New page

PSQL aka Postgres aka PostgreSQL... I prefer psql.

== Dump and Restore ==

<pre>dump:
$ pg_dump -h host -p 5432 -U user -F c -b -v -f /tmp/db_name.backup db_name
restore:
$ pg_restore -h host -p 5432 -U user -d db_name -v /tmp/db_name.backup</pre>

@@ Line 49: / Line 49: @@
 <pre>
 SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));
 </pre>

@@ Line 43: / Line 43: @@
      ...
      AND a.criteria_N = b.criteria_N;
 </pre>

← Older revision		Revision as of 12:10, 2 October 2020
Line 7:		Line 7:
	restore:		restore:
	$ pg_restore -h host -p 5432 -U user -d db_name -v /tmp/db_name.backup</pre>		$ pg_restore -h host -p 5432 -U user -d db_name -v /tmp/db_name.backup</pre>
		+
		+	== Delete Duplicate Lines ==
		+
		+	Before you can add a <tt>unique</tt> constraint to a table, you have to make sure it does satisfy this criteria.
		+
		+	With <tt>table_T</tt> and columns <tt>criteria_1</tt>, ..., <tt>criteria_N</tt>.
		+
		+	<pre>
		+	;;
		+	;; list rows that do not satisfy the uniqueness constraint
		+	;;
		+	SELECT
		+	criteria_1,
		+	...
		+	criteria_N,
		+	COUNT(*)
		+	FROM
		+	table_T
		+	GROUP BY
		+	criteria_1, ..., criteria_N
		+	HAVING
		+	COUNT(*) > 1
		+	ORDER BY
		+	criteria_1, ..., criteria_N;
		+
		+	;;
		+	;; delete all rows that do not satisfy the constraint, keeping the ones with lowest id value
		+	;;
		+	DELETE FROM
		+	table_T a
		+	USING table_T b
		+	WHERE
		+	a.id > b.id
		+	AND a.criteria_1 = b.criteria_1
		+	...
		+	AND a.criteria_N = b.criteria_N;
		+	</pre>