PostgreSQL psql Commnads
ghdrako edited this page May 3, 2024
·
4 revisions
\l -- list database
\l+
\l+ <db_name> -- show info about db ex size
select pg_database_size('<db_name>');
SELECT datname FROM pg_database;
\c <db_name> -- connect to db or if not exist create
\conninfo -- display information about current connection
CREATE DATABASE my_database WITH OWNER my_user;
CREATE DATABASE test_db WITH OWNER = test_usr ENCODING = 'UTF8' TABLESPACE = test_tbs LC_COLLATE = 'pl_PL.utf8' LC_CTYPE = 'pl_PL.utf8' CONNECTION LIMIT = -1;
createdb <db_name> -- Note that outside the PostgreSQL console you can also create a database with createdb
ALTER USER my_user CREATEDB; -- give a user the ability to create new databases
ALTER DATABASE name RENAME TO new_name; # change db name - nie moze byc polaczen do bazy
ALTER DATABASE moja_baza OWNER TO postgres; # change db owner
select pg_size_pretty(pg_database_size('dvdrental')); # specified db size
SELECT pg_database_size('dvdrental');
select current_database();
----------------
CREATE TABLESPACE test_tbs OWNER test_usr LOCATION '/pgqdata/cdl/PostgreSQL/9.2/data';
ALTER DATABASE test_baza SET TABLESPACE test_tbs;
CREATE SCHEMA test_schema;
\dn
\dn+
select current_schema();
\dn - list schemas
\du - list users
\d - list object in curent database
\dt - list tables in current db in public schema
\dt+ - get more information on tables
\dt <schema> - list tables in schematu
\dt <schema>.*
\dt *.* - List tables from all schemas
\df <schema> - list stored procedures
\df+
\dv <schema> - list views
\di - list indexes
select * from pg_indexes where tablename not like 'pg%';
select tablename,indexname,tablespace,indexdef from pg_indexes where tablename = 'your_table_name';
SELECT * FROM pg_indexes WHERE schemaname = 'public';
\dx+ pg_trgm - show info about extension
psql -V - Check psql Client version
Select version(); - Check Postgres server version
\g - seeing previous command
\timing
\e - execute last command
\x - Show query output in the pretty-format - extended mode
\h CREATE DATABASE - show help for command
\? - list postgres commands
\q - quit console
\pset format csv - format outpu as csv
select ...
\d <table> - describe table
\d+ <table> - Get detailed information on a table
\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV - Copy table data to CSV file
SELECT * FROM pg_indexes WHERE tablename='<table>' AND schemaname='<schema>'; - Check indexes for a table
ANALYZE [<table>] - Collects statistics about the contents of tables in pg_statistic system catalog
SELECT reltuples AS card FROM pg_class WHERE relname = '<table_name>'; - Approximate Table Row count / Table Cardinality
SELECT
table_name,
column_name,
data_type
FROM
information_schema.columns
WHERE
table_schema = 'schema_name'
AND
table_name = 'city';
service postgresql <stop|start|restart>
show all - Display configuration parameters
select * from pg_settings; - Display configuration parameters using sql
SELECT current_setting('max_connections'); - Show current setting from “max_connections”
show config_file; - Show Postgres config file location
\df+ - List functions
\ef myfunction - Edit function
$$
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
$$
select add(5,9);
\connect opensim Opensim_Tester localhost;
\password username - Set/Reset postgres user password
\du - Show all users
select * from pg_user;
psql -U testuser mytest - Login & enter postgres terminal
CREATE ROLE my_user WITH LOGIN PASSWORD 'my_password';
DROP ROLE [ IF EXISTS ] <user>;
# if some object block drop
drop owned by <user> cascade;
# or
reassign owned by <user> to <other user>;
ALTER USER user_name WITH PASSWORD 'new_password';
# list users
SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN
CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN
CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN
CAST('create database' AS pg_catalog.text)
ELSE
CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
PK Constraints in schema
SELECT conrelid::regclass AS table_name,
conname AS primary_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'p' -- p - primary key u - unique f -foreign key
AND connamespace = '<schema_name>'::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;
ALTER TABLE ONLY TEST
ADD CONSTRAINT con_id PRIMARY KEY (con_id);
alter table TEST drop constraint IF exists con_id;
Use quote_ident()
, or format()
with %I
, instead. These apply correct escaping as necessary.
postgres=# SELECT format('GRANT SELECT ON TABLE %I TO someuser;', tablename) FROM pg_tables WHERE tablename~'^pgbench';
format
-----------------------------------------------------
GRANT SELECT ON TABLE pgbench_accounts TO someuser;
GRANT SELECT ON TABLE pgbench_branches TO someuser;
postgres=# gexec
GRANT
GRANT
Test