postgres

Postgres SQL Scripts - Shorthand Commands for PSQL

Use of .psqlrc

The .psqlrc file allows you to configure personalized settings, add shortcuts and add custom commands. It is stored in the users home directory and will automatically be read upon launch of psql.

An example of a .psqlrc file can be found here:

Sample .psqlrc file for example .psqlrc content.

Ignore .psqlrc

psql --username=<username> --dbname=<database> --host=<hostname> --port=<port> --no-psqlrc

General psql navigation

Connect

Password can be exported at the command line by using:

export PGPASSWORD=<somepassword>

General connections can be made using:

psql -U <username> -d <database> -h <hostname> -p <port>

psql --username=<username> --dbname=<database> --host=<hostname> --port=<port>

psql -U <username> -d <database> -h <host> -f <local_file>

psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>

Disconnect

\q
\!

Clear scrollback

(CTRL + L)

Set Resultset to "Unaligned"

\a

Set Resultset to "Extended"

\x

Information about the connected system

Server version

SHOW SERVER_VERSION;

Show connection information (SSL USED? PSQL VERSION?)

\conninfo

Show environment variables

SHOW ALL;

List all roles in the instance

SELECT rolname FROM pg_roles;

Show currently connected user

SELECT current_user;

Show current user permissions

\du
\du+

Show current user's session settings

\drds

show current database

SELECT current_database();

show all tables in database

\dt
\dt+

list functions

\df <schema>
\df+ <schema>

Databases

list databases with size information

\l
\l+

Connect to database

\c <database_name>

Connect to database as different user

\c <database_name> <username>

Users

create user

http://www.postgresql.org/docs/current/static/sql-createuser.html

CREATE USER <user_name> WITH PASSWORD '<password>';

drop user

http://www.postgresql.org/docs/current/static/sql-dropuser.html

DROP USER IF EXISTS <user_name>;

alter user password

http://www.postgresql.org/docs/current/static/sql-alterrole.html

ALTER ROLE <user_name> WITH PASSWORD '<password>';

Permissions

Grant all permissions on database

http://www.postgresql.org/docs/current/static/sql-grant.html

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

Grant connect permission on database

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

Grant individual permissions on schema

GRANT USAGE ON SCHEMA public TO <user_name>;

Grant permissions to functions

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

Grant permissions to select, update, insert, delete, on all tables in a schema

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

Grant all permissions on an individual table

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

Grant a single permission on an individual table

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

Schema

List schemas

\dn

\dn+

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

Create a schema

http://www.postgresql.org/docs/current/static/sql-createschema.html

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Drop a schema

http://www.postgresql.org/docs/current/static/sql-dropschema.html

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

Tables

List all tables, in current db (limited by your search_path)

\dt
\dt+

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

List tables, globally (not limited by search_path)

\dt *.*

SELECT * FROM pg_catalog.pg_tables

List table schema

\d <table_name>
\d+ <table_name>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

Columns

Add a column

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

Change a column datatype

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];

Delete a column (beware of table locks)

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

Scripting

Export table into CSV file

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> TO '<file_path>' CSV

Export table, only specific columns, to CSV file

\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' CSV

Import CSV file into table

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> FROM '<file_path>' CSV

Import CSV file into table, only specific columns

\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' CSV

Name		Name	Last commit message	Last commit date
parent directory ..
.psqlrc		.psqlrc
README.md		README.md
postgres_active_connections.sql		postgres_active_connections.sql
postgres_active_parallel_workers.sql		postgres_active_parallel_workers.sql
postgres_alloy_columnar_engine_queries.sql		postgres_alloy_columnar_engine_queries.sql
postgres_average_row_size.sql		postgres_average_row_size.sql
postgres_blocking_lock_pid_tree.sql		postgres_blocking_lock_pid_tree.sql
postgres_blocking_lock_sql.sql		postgres_blocking_lock_sql.sql
postgres_blocking_lock_wait_events.sql		postgres_blocking_lock_wait_events.sql
postgres_check_table_reloptions.sql		postgres_check_table_reloptions.sql
postgres_database_user_settings.sql		postgres_database_user_settings.sql
postgres_determine_oldest_xmin_age.sql		postgres_determine_oldest_xmin_age.sql
postgres_determine_oldest_xmin_by_pid.sql		postgres_determine_oldest_xmin_by_pid.sql
postgres_duplicate_indexes.sql		postgres_duplicate_indexes.sql
postgres_find_unused_indexes.sql		postgres_find_unused_indexes.sql
postgres_google_db_advisor_example.sql		postgres_google_db_advisor_example.sql
postgres_idle_connection_times.sql		postgres_idle_connection_times.sql
postgres_index_bloat_status.sql		postgres_index_bloat_status.sql
postgres_index_build_status.sql		postgres_index_build_status.sql
postgres_index_cache_ratio.sql		postgres_index_cache_ratio.sql
postgres_index_check_usage_frequency.sql		postgres_index_check_usage_frequency.sql
postgres_index_information_details.sql		postgres_index_information_details.sql
postgres_index_object_size.sql		postgres_index_object_size.sql
postgres_index_usage_stats.sql		postgres_index_usage_stats.sql
postgres_indexes_supporting_pk_constraints.sql		postgres_indexes_supporting_pk_constraints.sql
postgres_list_pg_buffercache_blocks_for_object.sql		postgres_list_pg_buffercache_blocks_for_object.sql
postgres_list_pg_buffercache_for_all_objects.sql		postgres_list_pg_buffercache_for_all_objects.sql
postgres_monitor_waits.sql		postgres_monitor_waits.sql
postgres_non_default_params.sql		postgres_non_default_params.sql
postgres_object_sizes.sql		postgres_object_sizes.sql
postgres_queries_running_in_parallel.sql		postgres_queries_running_in_parallel.sql
postgres_ranked_waits.sql		postgres_ranked_waits.sql
postgres_report_high_water_mark_wasted_space.sql		postgres_report_high_water_mark_wasted_space.sql
postgres_table_and_index_bloat_stats.sql		postgres_table_and_index_bloat_stats.sql
postgres_table_bloat_check.sql		postgres_table_bloat_check.sql
postgres_table_cache_hit_rate.sql		postgres_table_cache_hit_rate.sql
postgres_table_cache_hit_vs_disk_hit_ratio.sql		postgres_table_cache_hit_vs_disk_hit_ratio.sql
postgres_table_constraint_info.sql		postgres_table_constraint_info.sql
postgres_table_index_used_pct.sql		postgres_table_index_used_pct.sql
postgres_table_object_size.sql		postgres_table_object_size.sql
postgres_table_reloptions.sql		postgres_table_reloptions.sql
postgres_table_stats_status.sql		postgres_table_stats_status.sql
postgres_top_10_long_running_queries.sql		postgres_top_10_long_running_queries.sql
postgres_top_10_objects_using_pg_buffercache.sql		postgres_top_10_objects_using_pg_buffercache.sql
postgres_top_10_sql_by_execution.sql		postgres_top_10_sql_by_execution.sql
postgres_top_10_sql_by_mean_exec_time.sql		postgres_top_10_sql_by_mean_exec_time.sql
postgres_top_10_sql_by_total_exec_time.sql		postgres_top_10_sql_by_total_exec_time.sql
postgres_top_10_table_seq_scans.sql		postgres_top_10_table_seq_scans.sql
postgres_transaction_age_by_table.sql		postgres_transaction_age_by_table.sql
postgres_unindexed_foreign_keys.sql		postgres_unindexed_foreign_keys.sql
postgres_vacuum_activity_estimate_stats.sql		postgres_vacuum_activity_estimate_stats.sql
postgres_vacuum_progress_basic.sql		postgres_vacuum_progress_basic.sql
postgres_vacuum_progress_detailed.sql		postgres_vacuum_progress_detailed.sql
postgres_waits_by_individual_pid.sql		postgres_waits_by_individual_pid.sql
postgres_wal_generation_over_known_time.sql		postgres_wal_generation_over_known_time.sql

FilesExpand file tree

postgres

Directory actions

More options

Directory actions

More options

Latest commit

History

postgres

Folders and files

parent directory

README.md

Postgres SQL Scripts - Shorthand Commands for PSQL

Use of .psqlrc

Ignore .psqlrc

General psql navigation

Connect

Disconnect

Clear scrollback

Set Resultset to "Unaligned"

Set Resultset to "Extended"

Information about the connected system

Server version

Show connection information (SSL USED? PSQL VERSION?)

Show environment variables

List all roles in the instance

Show currently connected user

Show current user permissions

Show current user's session settings

show current database

show all tables in database

list functions

Databases

list databases with size information

Connect to database

Connect to database as different user

Users

create user

drop user

alter user password

Permissions

Grant all permissions on database

Grant connect permission on database

Grant individual permissions on schema

Grant permissions to functions

Grant permissions to select, update, insert, delete, on all tables in a schema

Grant all permissions on an individual table

Grant a single permission on an individual table

Schema

List schemas

Create a schema

Drop a schema

Tables

List all tables, in current db (limited by your search_path)

List tables, globally (not limited by search_path)

List table schema

Columns

Add a column

Change a column datatype

Delete a column (beware of table locks)

Scripting

Export table into CSV file

Export table, only specific columns, to CSV file

Import CSV file into table

Import CSV file into table, only specific columns