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.)

Other