Postgres Lab Starting database… Playground

What is PostgreSQL? Databases, tables and SQL

PostgreSQL (often “Postgres”) is a free, open-source relational database management system. It stores data in tables made of rows (records) and columns (fields with a fixed data type). Tables are linked through keys: a primary key identifies each row, and a foreign key points to a primary key in another table.

Postgres is a client–server system. The server process owns the data files; clients (psql, pgAdmin, your web app) connect and send SQL. One server holds many databases; each database holds schemas (namespaces, public by default); each schema holds tables, views, functions and more.

The families of SQL commands

  • DQL (query): SELECT.
  • DML (change data): INSERT, UPDATE, DELETE.
  • DDL (define structure): CREATE, ALTER, DROP, TRUNCATE.
  • DCL (permissions): GRANT, REVOKE.
  • TCL (transactions): BEGIN, COMMIT, ROLLBACK, SAVEPOINT.

psql shortcuts and their SQL equivalents

In the real psql client you type backslash commands such as \l (list databases), \c shop (connect), \dt (list tables), \d products (describe a table), \df (list functions) and \q (quit). Those are client features, so they don’t work in this browser console, but every one of them is really a query against the system catalog, which you can run yourself (see the examples).

SQL keywords are case-insensitive (select = SELECT). Unquoted identifiers are folded to lower case, so Products and products are the same table; "Products" in double quotes would be a different one. Strings always use single quotes.

Syntax

-- a comment runs to the end of the line
/* a block comment
   can span lines */
SELECT ... ;   -- statements end with ;

Examples

Edit the code and run it. Output is printed the way psql prints it.

2.1Which server am I talking to?

SELECT version();
SELECT current_database(), current_user, now();

Two statements run one after the other; you’ll see two result tables. version(), current_database(), current_user and now() are built-in functions. now() returns the current timestamp with time zone.

2.2List the tables (like \dt)

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

information_schema is a standard set of views that describe the database itself. Filtering on table_schema = 'public' hides PostgreSQL’s own system tables.

2.3Describe a table (like \d products)

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;

Each column of products with its type, whether it allows NULL, and its default value. ordinal_position keeps the columns in the order they were defined.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 2.1

List the column names and data types of the customers table in their defined order.
Hint

Query information_schema.columns and order by ordinal_position.

Solution
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'customers' ORDER BY ordinal_position;