oradrs

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

-E: will describe the underlaying queries of the \ commands (cool for learning!)
-l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

Update: 2023/11/30 Bruce Momjian's take on things: https://momjian.us/main/blogs/pgblog/2023.html#November_22_2023

Given a table...

CREATE TABLE foo (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  ...
);

git clone [email protected]:YOUR-USERNAME/YOUR-FORKED-REPO.git

cd into/cloned/fork-repo
git remote add upstream git://github.com/ORIGINAL-DEV-USERNAME/REPO-YOU-FORKED-FROM.git
git fetch upstream

	REM http://www.dbspecialists.com/files/scripts/compare_schemas.sql
	REM
	REM compare_schemas.sql
	REM ===================
	REM
	REM This script is provided by Database Specialists, Inc.
	REM (http://www.dbspecialists.com) for individual use and not for sale.
	REM Database Specialists, Inc. does not warrant the script in any way
	REM and will not be responsible for any loss arising out of its use.
	REM

	create table deps_saved_ddl
	(
	deps_id serial primary key,
	deps_view_schema varchar(255),
	deps_view_name varchar(255),
	deps_ddl_to_run text
	);

	create or replace function deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
	$$

	-- Shared Pool
	select
	event,
	time_waited "time_waited(s)",
	case when time_waited = 0 then
	0
	else
	round(time_waited*100 / sum(time_waited) Over(), 2)
	end "percentage"
	from

	-- show running queries (pre 9.2)
	SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- show running queries (9.2)
	SELECT pid, age(clock_timestamp(), query_start), usename, query
	FROM pg_stat_activity
	WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'

	# the following two lines give a two-line status, with the current window highlighted
	hardstatus alwayslastline
	hardstatus string '%{= kG}[%{G}%H%? %1`%?%{g}][%= %{= kw}%-w%{+b yk} %n*%t%?(%u)%? %{-}%+w %=%{g}][%{B}%m/%d %{W}%C%A%{g}]'

	# huge scrollback buffer
	defscrollback 5000

	# no welcome message
	startup_message off

	#!/bin/bash

	usage()
	{
	cat << EOF
	usage: $0 options

	This script set ownership for all table, sequence and views for a given database

	Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto