Skip to content

PostgreSQL Quick Reference Commands

User Administration

Task Command Example Notes
Create User create user {user}
Change User's Password \password {user} Refer to pg_hba.conf if connectivity issues.
Add Schema Permissions grant select, update, delete, insert on all tables in schema program to "user-name";
Drop User drop user {user}

Alter

Task Command Example Notes
Add Auto Increment alter table tasks alter column task_id set default nextval('tasks_task_id_seq') The sequence must be created before hand.
Reset Auto Increment alter SEQUENCE channel.listings_geographics_listing_geographic_id_seq RESTART WITH 1;
Add Constraint Primary Key alter table tasks add constraint tasks_task_id_pkey primary key using index tasks_task_id_idx; The index must be created before hand.
Add Constraint Unique alter TABLE program.sports_teams ADD CONSTRAINT sports_teams_cat_team_name UNIQUE(category_id, team_name);
Drop Constraint alter table tasks drop constraint test
Drop Column Default alter table channel.listings ALTER COLUMN iso_639_3 DROP DEFAULT;
Drop Column Not Null ALTER TABLE channel.listings ALTER COLUMN iso_639_3 DROP NOT NULL;
Drop Database drop database {database}
Drop Trigger drop trigger replace_disabled_channel on listings_accounts_enabled;

Create

Task Command Example Notes
Create Database create database {database}
Create Sequence create sequence tasks_task_id_seq A sequence is required to set a column to auto increment the value.
Create Unique Index create unique index concurrently tasks_task_id_idx on tasks (task_id) A unique index is required in order to set a column as a primary key.

Delete

Task Command Example Notes
Delete delete from prog_categories where id IN (select id from (select id, ROW_NUMBER() over (partition by description order by id) as row_num from prog_categories) t where t.row_num > 1);

Meta-Command

Task Command Example Notes
Connect to a Database \c {database}
List Databases with Owners \l
List Data (enum) Types \dT
List Functions \df
List Sequences \ds
List Tables \dt
List Tables, Views and Sequences \d
Import SQL Data \copy db.categories from /home/username/categories.sql with (delimiter '|', NULL 'NULL');
List Tables Permissions \dp program.programs
List Views \dv
List Roles with Permissions \du
Show Function Definition \sf channel.parent_channel_disabled
Show View Definition \sv channel.channels_days_of_data
Show Current User and Connected Database \c

Show

Task Command Example Notes
Show pg_hba File Being Used show hba_file; Outputs the location of the current pg_hba.conf file.
Show Configuration File Being Used show config_file;
Show Interval Style show intervalstyle;

Select

Task Command Example Notes
Find Total Rows select schemaname, relname, n_live_tup FROM pg_stat_user_tables;
List Available Extensions SELECT * FROM pg_available_extensions;
List Installed Extensions SELECT * FROM pg_extension;
List All ENUM Types select n.nspname as enum_schema, t.typname as enum_name, e.enumlabel as enum_value from pg_type t join pg_enum e on t.oid = e.enumtypid join pg_catalog.pg_namespace n ON n.oid = t.typnamespace;
List Triggers select * from information_schema.triggers;
List User Defined Functions select pp.proname, pl.lanname, pn.nspname, pg_get_functiondef(pp.oid) from pg_proc pp inner join pg_namespace pn on (pp.pronamespace = pn.oid) inner join pg_language pl on (pp.prolang = pl.oid) where pl.lanname NOT IN ('c','internal') and pn.nspname NOT LIKE 'pg_%' and pn.nspname <> 'information_schema';
Search for Column Name select table_name, column_name, data_type from information_schema.columns WHERE column_name='province_state_alpha_id';
Search for Data Type select table_schema, table_name, column_name, data_type from information_schema.columns WHERE data_type = 'character' and table_catalog = 'dsi';
Select Duplicate Records select category_id from (select category_id, name, row_number() over(partition by name) as row from program.categories) dups where dups.row > 1; Returns all of the duplicate records leaving one record for each duplicate row.

Set

Task Command Example Notes
Set search_path SET search_path TO schema_name;

Update

Task Command Example Notes
Update Table: jsonb properties UPDATE dsihd.units SET settings = settings || '{"reboot": true, "max_subs": 10, "timezone": "America/Chicago"}' WHERE uuid = '123'; JSON Functions and Operators
Update Table Select update channel.listings AS t1 set call_sign_origin = t2.call_sign FROM (SELECT call_sign FROM channel.listings) as t2 where t1.call_sign=t2.call_sign;