postgres: random useful things

Run sql statements from the command line, use the -c flag

psql your_db -c "update users set is_active = 'f';"

view raw postgres_sql_command.sql.sh hosted with ❤ by GitHub

create a random value

	select md5(random()::text)

view raw postgres.random_value.sql hosted with ❤ by GitHub

crazy updates using regexp_matches

	-- changes joe.smith@gmail.com -> joe.smith@qa.com
	update users set email = replace(replace(regexp_matches(email, '.*@')::varchar, '{', ''), '}', '') || 'qa.com';
	-- there must be a better way to get rid of the {} chars returned by regexp_matches

view raw postgres.regexp_matches.sql hosted with ❤ by GitHub

postgres: terminate all database connections

Using psql from the command line you can terminate all connections to a database

	# replace your_db with the name of your database
	psql postgres -c "select pg_terminate_backend(procpid) from pg_stat_activity where datname='your_db';"

view raw postgres_disconnect.sql.sh hosted with ❤ by GitHub

Comes in handy when you want to do things like restore your staging or development database

	psql postgres -c "select pg_terminate_backend(procpid) from pg_stat_activity where datname='your_db';"
	dropdb your_db
	createdb --template=template0 --encoding=unicode your_db

view raw postgres_rebuild.sql.sh hosted with ❤ by GitHub

Resources

• http://www.postgresql.org/docs/9.1/static/reference-client.html 
• http://blog.gahooa.com/2010/11/03/how-to-force-drop-a-postgresql-database-by-killing-off-connection-processes/