postgres:postgresql
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
| postgres:postgresql [2009/04/02 16:56] – created deva | postgres:postgresql [2011/01/25 07:44] (current) – deva | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ======PostGreSQL====== | ======PostGreSQL====== | ||
| + | =====Open sql cli interface====== | ||
| + | < | ||
| + | psql -U [username] -W [database] | ||
| + | </ | ||
| + | |||
| =====Creating/ | =====Creating/ | ||
| < | < | ||
| Line 9: | Line 14: | ||
| < | < | ||
| createdb -O [owner] -h localhost [dbname] | createdb -O [owner] -h localhost [dbname] | ||
| + | </ | ||
| + | |||
| + | =====Import/ | ||
| + | < | ||
| + | psql -U [username] -W [dbname] < dump.sql | ||
| + | pg_dump -U [username] -h localhost [-t [table]] [dbname] > dump.sql | ||
| + | </ | ||
| + | |||
| + | =====Cut prefix from table===== | ||
| + | < | ||
| + | ALTER TABLE data ALTER COLUMN filename TYPE character varying(255) USING substring(filename from 20); | ||
| + | </ | ||
| + | |||
| + | =====Find missing foreign key values===== | ||
| + | < | ||
| + | SELECT foo.xcpr FROM (SELECT a.personid AS xcpr, b.personid AS ycpr FROM examinations AS a LEFT OUTER JOIN person AS b ON a.personid = b.personid) AS foo WHERE foo.ycpr IS NULL; | ||
| + | </ | ||
| + | |||
| + | =====Disable pager (less/more) in psql===== | ||
| + | < | ||
| + | \pset pager | ||
| </ | </ | ||
postgres/postgresql.1238684172.txt.gz · Last modified: 2009/04/02 16:56 by deva