An Interest In:
Web News this Week
- April 19, 2024
- April 18, 2024
- April 17, 2024
- April 16, 2024
- April 15, 2024
- April 14, 2024
- April 13, 2024
PostgreSQL Cheat Sheet
We gathered a list of handy PostgreSQL commands and queries when working with PostgreSQL databases. Please ping us @ForestAdmin if youd like to add something to the cheat sheet!
Getting started with PostgreSQL
Login and enter PostgreSQL command line utility psql
$ sudo su - postgres psql
List postgres databases
\l
Connect to postgres database
\c databaseName
Exit postgreSQL command line utility psql
\q
Managing PostgreSQL
Check PostgreSQL version
SELECT version();
Check if PostgreSQL is installed
$ which psql
Check if PostgreSQL is running
$ pgrep -fa -- -D | grep postgres
Restart PostgreSQL on Linux
# systemctl restart postgresql// or# service postgresql restart// or# /etc/init.d/postgresql restart// or# /sbin/service postgresql restart
Restart PostgreSQL on OSX
# brew services restart postgres// or# pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart
Restart PostgreSQL on Windows
Winkey + RType "services.msc"Click "restart"
Change PostgreSQL user password
$ sudo -u postgres psql\password postgres
Exit from psql
\q
Interacting with databases in PostgreSQL
List postgres databases
\l
Connect or switch to postgres database
\c databaseName
Create new postgres database
CREATE DATABASE databaseName
Delete postgres database
DROP DATABASE databaseName
Rename postgres database
ALTER DATABASE old_databaseName TO new_databaseName
Query postgres JSON data
SELECT *FROM mytableWHERE myfield @> '{"key1":"valueA"}'
Interacting with tables in PostgreSQL
List postgres tables
\dt
Describe postgres table
\d tableName
Create postgres table
CREATE TABLE tableName( columnName columnType, columnName columnType);
Delete postgres table
DROP TABLE tableName CASCADE
Backup and restore PostgreSQL database
Backup postgres database
$ pg_dump -d databaseName -U userName > backupFile
Restore postgres database
psql databaseName < backupFile
Managing roles and permissions in PostgreSQL
List postgres roles
SELECT rolname FROM pg_roles;
Create postgres user
CREATE USER userName WITH PASSWORD 'userPassword';
Delete postgres user
DROP USER userName;
Change postgres user password
ALTER ROLE userName WITH PASSWORD 'userPassword';
List all assigned roles postgres
SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplicationFROM pg_catalog.pg_roles rORDER BY 1;
Grant all permissions on postgres database
GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;
Grant connection permissions on postgres database
GRANT CONNECT ON DATABASE databaseName TO userName;
List permissions for specific role postgres
SELECT table_catalog, table_schema, table_name, privilege_typeFROM information_schema.table_privilegesWHERE grantee = userName ORDER BY table_name;
ProstgreSQL psql commands recap
\?
List all available psql commands
\h COMMAND
Get help on specific command
\l
List databases
\c
databaseName Connect to database
\dt
List tables
\d
tableName Describe table
\d+
tableName Describe table with details
\dn
List schemas
\df
List functions
\dv
List views
\du
List users
\dy
List events
\di
List indexes
\q
Exit
Need to build admin panels or a GUI tool for PostgreSQL? Check out Forest Admin for PostgreSQL
Original Link: https://dev.to/forestadmin/postgresql-cheat-sheet-38j3
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To