Tables

What Are Tables in PostgreSQL?

A table is a named collection of data organized in rows with named columns of specific data types.

A table can be stored in a database but also calculated at query time (e.g. subquery, view).

A table is sometimes being referred to as a "relation" (however the extent of equivalence of both terms is being disputed). Therefore the word "relational" in "relational database" refers rather to tables than to relations between rows of tables.

The order of rows in a table is not guaranteed in any way however the rows can be ordered explicitly in an SQL query.

Creating Tables

To create a new table use the CREATE TABLE command appended with the table name and optional column data in parenthesis. Each row of the optional column data should include the required column name, the required column type and the optional column constraint.

CREATE TABLE clients ();

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR (42) NOT NULL,
  last_name VARCHAR (42),
  email VARCHAR (128) UNIQUE NOT NULL
);

Listing Tables

Listing Tables Using SQL Queries

To list all tables in the currently connected database use the SELECT * FROM information_schema.tables; SQL query.

To list all tables in the currently connected database in a particular schema use the SELECT * FROM information_schema.tables WHERE table_schema = 'foo'; SQL query.

Listing Tables Using psql

To list all tables in the currently connected database in psql use the \dt (\dt+ for extended information) internal command.

To list all tables in the currently connected database in a particular schema in psql use the same \dt (\dt+ for extended information) internal command but appended with the schema name and the select all operator (*).

\dt+ foo.*

Renaming Tables

To rename a table use the ALTER TABLE ... RENAME TO ... SQL command.

ALTER TABLE users RENAME TO clients;

Dropping tables

To drop (aka delete) a table use the DROP TABLE SQL command.

DROP TABLE users;