Difference between revisions of "Short Notes on PSQL"

From PaskvilWiki
Jump to: navigation, search
(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...")
 
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>

Revision as of 13:10, 2 October 2020

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:
$ pg_restore -h host -p 5432 -U user -d db_name -v /tmp/db_name.backup

Delete Duplicate Lines

Before you can add a unique constraint to a table, you have to make sure it does satisfy this criteria.

With table_T and columns criteria_1, ..., criteria_N.

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