PostgreSQL is a flavor of object-relational database system that is more than 30 years old. "psql" is the program command you use to access it through the terminal.
First of all, let's make sure that you have PostgreSQL installed. Type:
postgres -v or postgres --version
If you're not sure where your postgres files are located, you can also type:
locate /bin/postgres
Next, if you have it installed, it's handy to know if it's running.
sudo service postgres status to check status
sudo service postgres start to start it on a designated port
sudo service postgres stop to stop the service
sudo service postgres restart to stop then start the service in one line
Note: you may need to add sudo to the beginning of the following scripts
brew services info postgresql to get status and info
brew services start postgresql to start
brew services stop postgresql to stop the service
You'll be using a lot of different commands as the days go on, but these are going to be the most common queries and commands while you seed, query, inspect, and "CRUD" your data.
Simply type psql in most cases to enter the program. If you get a prompt like postgresrole=# then you're in.
To log in as a specific user, basic commands :
psql -U <username> log in as user
psql -d <database> -U <user> -W log in as db and user
to log in with admin rights, you need to log in as postgres user:
sudo -u postgres psql -U potsgres
sudo -u postgres psql
Most of the things you'll use to list items falls under a \d command.
\d --list tables views and sequences
\du --list roles (users) and their permissions
\dt --list tables of your database
\l --list all databasesIn your shell, outside of psql you can type a command:
psql -l [-U <user>] [-W] where everything in [] is optional.
The -W flag or option forces psql to prompt for a password before connecting to a database, even if it won't be used.
To connect to a database from within psql you can type:
\c <database> or \connect <database>
In SQL, all statements, queries, commands must end with ; or it will not know where the end of your queries, statements, and commands are. In other words, you can write multiline commands, if that's easier for your eyes.
For example SELECT * FROM name WHERE condition; becomes:
SELECT *
FROM name
WHERE condition;For some, this is more human-readable and easier to write.
CREATE ROLE name [options]; this creates a role with a name you supply and optional attributes. The attributes you can give are:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
The most useful ones you'll probably need to use are SUPERUSER, CREATEDB, and CREATEROLE.
To change a role that already exists:
ALTER ROLE name [options];
To delete a role that already exists:
DROP ROLE name
To delete users, it's a similar workflow, just replace the word ROLE with USER.
Below are some handy queries and operators. Enjoy this cheatsheet!
-- Query data in columns c1, c2 from a table
SELECT c1, c2 FROM t;
-- Query distinct rows from a table
SELECT DISTINCT c1
FROM t
WHERE condition;
-- Sort the result set in ascending or descending order
SELECT c1, c2
FROM t
ORDER BY c1 ASC [DESC];
-- Skip offset of rows and return the next n rows
SELECT c1, c2
FROM t
ORDER BY c1
LIMIT n
OFFSET offset;
-- Group rows using an aggregate function
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;
-- Filter groups using HAVING clause
SELECT c1, aggregate(c2) FROM t
GROUP BY c1
HAVING condition;-- Inner join t1 and t2
SELECT c1, c2
FROM t1
INNER JOIN t2
ON condition;
-- Left join t1 and t1
SELECT c1, c2
FROM t1
LEFT JOIN t2
ON condition;
-- Right join t1 and t2
SELECT c1, c2
FROM t1
RIGHT JOIN t2
ON condition;
-- Perform full outer join
SELECT c1, c2
FROM t1
FULL OUTER JOIN t2
ON condition;
-- Produce a Cartesian product of rows in tables
SELECT c1, c2
FROM t1
CROSS JOIN t2;
-- Another way to perform cross join
SELECT c1, c2
FROM t1, t2;
-- Join t1 to itself using INNER JOIN clause
SELECT c1, c2
FROM t1 A
INNER JOIN t2 B ON condition-- Combine rows from two queries
SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2;
-- Return the intersection of two queries
SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;
-- Subtract a result set from another result set
SELECT c1, c2 FROM t1
EXCEPT
SELECT c1, c2 FROM t2;
-- Query rows using pattern matching %, _
SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;
-- Query rows in a list
SELECT c1, c2
FROM t
WHERE c1
[NOT] IN value_list;
-- Query rows between two values
SELECT c1, c2
FROM t
WHERE c1
BETWEEN low AND high;
-- Check if values in a table is NULL or not
SELECT c1, c2 FROM t
WHERE c1 IS [NOT] NULL;