t18n/Postgres Cheatsheet.md

## Postgres Cheatsheet.md

      
    Raw
  

              Postgres Cheatsheet.md
            
          
Usage
Command


Open postgres with postgres user permission
psql -U postgres


Check Postgres version
select version();


Show all users with permissions
\du


List all databases
\l


Create a database
CREATE DATABASE db_name;


Grant all permission of a database to Postgres user
GRANT ALL PRIVILEGES ON DATABASE db_name TO pg_user;


Connect to a database
\c db_name


Connect to a database as specific user
\c db_name pg_user


Switch back to default database
\c postgres postgres


Create a table
CREATE TABLE table_name;


List all index from public schema
SELECT schemaname, tablename, indexname, indexdef 
FROM pg_indexes WHERE schemaname = 'public';


List all constraints of a table from public schema
SELECT con.conname, con.contype, con.conrelid, con.conkey
  FROM pg_catalog.pg_constraint con
  INNER JOIN pg_catalog.pg_class rel
  ON rel.oid = con.conrelid
  INNER JOIN pg_catalog.pg_namespace nsp
  ON nsp.oid = connamespace
  WHERE nsp.nspname = 'public'
  AND rel.relname = '%TableName%';
Usage	Command
Open postgres with `postgres` user permission	psql -U postgres
Check Postgres version	select version();
Show all users with permissions	\du
List all databases	\l
Create a database	CREATE DATABASE db_name;
Grant all permission of a database to Postgres user	GRANT ALL PRIVILEGES ON DATABASE db_name TO pg_user;
Connect to a database	\c db_name
Connect to a database as specific user	\c db_name pg_user
Switch back to default database	\c postgres postgres
Create a table	CREATE TABLE table_name;
List all index from `public` schema	SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public';
List all constraints of a table from `public` schema	SELECT con.conname, con.contype, con.conrelid, con.conkey FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = 'public' AND rel.relname = '%TableName%';