User Tools

Site Tools


postgres:postgresql

PostGreSQL

Open sql cli interface

psql -U [username] -W [database]

Creating/Deleting users

createuser -S -D -R -P -h localhost -U postgres [username]
dropuser [username]

Creating a database

createdb -O [owner] -h localhost [dbname]

Import/export a database

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.txt · Last modified: 2011/01/25 07:44 by deva