Tables in PostgreSQL

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;

We use cookies and similar technologies to enhance the quality of services, maintain statistics and adjust marketing content. You will find more information in the Cookies Policy.

By clicking OK you grant consent to processing of your personal data by us and our Trusted Partners with the purpose of maintain statistics and adjustment of the marketing content pursuant to the Privacy Policy. If you wish to not grant that consent and/or limit its extent click Settings.