Skip to content

Instantly share code, notes, and snippets.

@abhiomkar
Forked from luisuribe/Postgresl hints
Created June 24, 2011 20:19
Show Gist options
  • Save abhiomkar/1045591 to your computer and use it in GitHub Desktop.
Save abhiomkar/1045591 to your computer and use it in GitHub Desktop.
Postgresql snippets
-- 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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment