PostgreSQL
learn-pgsql.rc.pdx.edu
PostgreSQL Overview
PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
How to Connect
1. Open the terminal on your machine (or remote connection to RC System) and run the following command (substituting the appropriate user and database):
$ psql -h learn-pgsql.rc.pdx.edu -U username databaseName
Password for user username:
psql (12.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
databaseName=>
Note: If the command hangs (does NOT prompt for password), you probably need to connect to the VPN first.
Full Tunnel VPN: vpn.pdx.edu/full is required for certain systems.
Basic Commands and Usage
Some commands have MySQL specific syntax (management, navigation, etc.).
General SQL commands (i.e. queries) should be very similar, if not identical across database management systems (i.e. PostgreSQL).
Database Management (MySQL commands)
Show all databases:
databaseName=> \list
Choose a database to use (to change databases)
databaseName=> \c databaseName
Show all tables in database:
databaseName=> \d
Describe a table:
databaseName=> \d tableName;
Table Management (SQL Commands)
CREATE, INSERT, UPDATE, DELETE, TRUNCATE, DROP
Create a new table (in current database):
CREATE TABLE products (
id SERIAL NOT NULL PRIMARY KEY,
item TEXT NOT NULL,
quantity INT,
weight FLOAT
);
Insert new row(s) into table:
INSERT INTO products(item, quantity, weight)
VALUES('apple', 25, 0.3),
('orange', 12, 0.5),
('lemon', 30, 0.2);
Update row(s) in table:
UPDATE products SET weight = 0.4
WHERE item = 'apple';
Delete row(s) in table:
DELETE FROM products WHERE id = 1;
Remove all rows from table:
TRUNCATE TABLE products;
Delete entire table from database:
DROP TABLE products;
Common Queries (SQL Commands)
SELECT, WHERE, ORDER BY, LIMIT
The basic SELECT command is formatted like this:
SELECT column, another-column, third-column
FROM tableName
WHERE some-column = value;
Examples:
Show all rows/columns in table:
SELECT * FROM products;
Show specific attributes (columns):
SELECT id, item FROM products;
Count rows in table:
SELECT count(*) FROM products;
Sort rows by column (ascending):
SELECT * FROM products ORDER BY weight ASC;
Sort rows by column (descending):
SELECT * FROM products ORDER BY weight DESC;
Limit results (number of rows):
SELECT * FROM products LIMIT 2;
Filter results using predicates (WHERE clause):
SELECT * FROM products WHERE weight > 0.25;
Related Articles
PostgreSQL Command Cheat sheets
SQL Practice Tutorials
GUI Tools (Don't want to use the command line? Try these graphical applications.)
DataGrip (FREE for students/faculty - Get education license here)
Other