Postgresql snippets

postgresl_hints.sql

-- show tables
\dt

-- create a new database
# CREATE DATABASE mydb;

-- create user

# CREATE USER abhinay with password 'secret';

-- use database / connect to database
\c database

-- list databases / show all databases
\l

-- create table: http://www.postgresql.org/docs/8.1/static/sql-createtable.html
CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

-- describe table
\d+ tablename

-- delete table
DROP TABLE IF EXISTS table;

-- insert into table
INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');


-- select size of tables and indices in random order
SELECT relname, reltuples, relpages FROM pg_class ;

-- select size of tables and indices in descending order of size
SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC ;

-- select size of tables and indices in descending order of tuple- / recordcount
SELECT relname, reltuples, relpages FROM pg_class ORDER BY reltuples DESC ;

-- Change admin password
su - postgres
psql template1
alter user postgres with password 'postgres_password';

-- Show users
SELECT * FROM "pg_user";

-- change user for all tables
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

--change user for all seqs
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

-- change user for all views
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

-- Copy a database
create database NEWDB with template OLDDB;

-- Change DB owner/name
ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner

-- Showing transaction status in the psql prompt
\set PROMPT1 '%/%R%x%# '
-- from http://sql-info.de/postgresql/notes/transaction-status-in-the-psql-prompt.html

-- Show table colums

SELECT attname FROM pg_attribute, pg_type
WHERE typname = 'table_name'
AND attrelid = typrelid
AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax');

-- Backup / Restore Database using Dump
-- BACKUP
$ pg_dump dbname > outfile
$ pg_dump dbname | gzip > filename.gz

-- RESTORE
$ psql dbname < infile
$ createdb dbname && gunzip -c filename.gz | psql dbname

-- UPDATE
update auth_user set is_superuser = 't' where username='abhiomkar';