List of commands to remember using postgres DB managment system.
Login, Create user and password
# login to psql client
psql postgres # OR
psql -U postgres
create database mydb; # create db
create user abhilash with SUPERUSER CREATEDB CREATEROLE encrypted password 'abhilashPass!';
grant all privileges on database mydb to myuser; # add privileges
Connect to DB, List tables and users, functions, views, schema
\l # lists all the databases
\c dbname # connect to db
\dt # show tables
\d table_name # Describe a table
\dn # List available schema
\df # List available functions
\dS [your_table_name] # List triggers
\dv # List available views
\du # lists all user accounts and roles
\du+ # is the extended version which shows even more information.
Show history, save to file, edit using editor, execution time, help
SELECT version(); # version of psql
\g # Execute the previous command
\s # Command history
\s filename # save Command history to a file
\i filename # Execute psql commands from a file
\? # help on psql commands
\h ALTER TABLE # To get help on specific PostgreSQL statement
\timing # Turn on/off query execution time
\e # Edit command in your own editor
\e [function_name] # It is more useful when you edit a function in the editor. Do \df for functions
\o [file_name] # send all next query results to file
\o out.txt
\dt
\o # switch
\dt
Change output, Quit psql
# Switch output options
\a command switches from aligned to non-aligned column output.
\H command formats the output to HTML format.
\q # quit psql
Reference: https://www.postgresqltutorial.com/postgresql-administration/psql-commands/
PostgreSQL Cheat Sheet
CREATE DATABASE
CREATE DATABASE dbName;
CREATE TABLE (with auto numbering integer id)
CREATE TABLE tableName (
id serial PRIMARY KEY,
name varchar(50) UNIQUE NOT NULL,
dateCreated timestamp DEFAULT current_timestamp
);
Add a primary key
ALTER TABLE tableName ADD PRIMARY KEY (id);
Create an INDEX
CREATE UNIQUE INDEX indexName ON tableName (columnNames);
Backup a database (command line)
pg_dump dbName > dbName.sql
Backup all databases (command line)
pg_dumpall > pgbackup.sql
Run a SQL script (command line)
psql -f script.sql databaseName
Search using a regular expression
SELECT column FROM table WHERE column ~ 'foo.*';
The first N records
SELECT columns FROM table LIMIT 10;
Pagination
SELECT cols FROM table LIMIT 10 OFFSET 30;
Prepared Statements
PREPARE preparedInsert (int, varchar) AS
INSERT INTO tableName (intColumn, charColumn) VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
DEALLOCATE preparedInsert;
Create a Function
CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer
AS 'SELECT date_part(''month'', $1)::integer;'
LANGUAGE 'sql';
Table Maintenance
VACUUM ANALYZE table;
Reindex a database, table or index
REINDEX DATABASE dbName;
Show query plan
EXPLAIN SELECT * FROM table;
Import from a file
COPY destTable FROM '/tmp/somefile';
Show all runtime parameters
SHOW ALL;
Grant all permissions to a user
GRANT ALL PRIVILEGES ON table TO username;
Perform a transaction
BEGIN TRANSACTION
UPDATE accounts SET balance += 50 WHERE id = 1;
COMMIT;
Basic SQL
Get all columns and rows from a table
SELECT * FROM table;
Add a new row
INSERT INTO table (column1,column2)
VALUES (1, 'one');
Update a row
UPDATE table SET foo = 'bar' WHERE id = 1;
Delete a row
DELETE FROM table WHERE id = 1;
From: https://www.petefreitag.com/cheatsheets/postgresql/
The Rails Drop 