PostgreSQL - creating and dropping tables

This article will explain how to create and drop database tables using the psql client.

Many web frameworks, such as Ruby on Rails, handle creation and access to database tables automatically. But it's a good idea to learn the basics of working with them manually; also, not everyone running a postgres server will use it in conjunction with a web framework.


Preparation

Going forward I'll assume you have a database and a normal database role: 'demodb1' owned by role 'demorole1'.

See the creating and dropping roles and creating and dropping databases articles if you need help with role and database creation.

Connect with psql

Logged into my demo slice as a Linux user named "mike" I'll make a local TCP connection with psql:

mike@demo:~$ psql -U demorole1 -d demodb1 -h localhost
Password for user demorole1: 
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

demodb1=>

Once we're connected with psql we can get started with the SQL commands. If you need help connecting to your postgres server, please see the three making connections articles in this series.

Creating a table

The first table within 'demodb1' will house a list of clients associated with our web app.

We'll need a unique ID number and an email address for each client. The following code can be pasted into our psql session; then we can hit enter to execute the command:

CREATE TABLE clients (
    id serial PRIMARY KEY,
    email varchar(50)
    );

Success! We now have a table named "clients" with two columns:

The "id" column will consist of unique, automatically incrementing integers (the 'serial' data type) and will act as the primary key for the table.

The "email" column will contain text strings no more than 50 characters long.

For a complete explanation see CREATE TABLE in the official documentation.

Note that postgres responded with two (expected) notices regarding the consequences of our command:

NOTICE:  CREATE TABLE will create implicit sequence "clients_id_seq" for serial column "clients.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "clients_pkey" for table "clients"

Listing all tables

We can get a list of all tables in the database to which we're connected using the '\dt' command in psql:

demodb1=> \dt
          List of relations
 Schema |  Name   | Type  |   Owner   
--------+---------+-------+-----------
 public | clients | table | demorole1
(1 row)

Great, it shows the one table we've created so far.

We can use the '\d' command with a table's name to inspect it more closely:

demodb1=> \d clients
                                Table "public.clients"
 Column |         Type          |                      Modifiers                       
--------+-----------------------+------------------------------------------------------
 id     | integer               | not null default nextval('clients_id_seq'::regclass)
 email  | character varying(50) | 
Indexes:
    "clients_pkey" PRIMARY KEY, btree (id)

Nice, we can see the column names, the data types we specified, and any modifiers — the data type and modifiers listed for the "id" column are a consequence of the 'serial PRIMARY KEY' argument in our first 'CREATE TABLE' command above.

Any indexes for the table will be reported too — note the one listed here matches up with the notices that appeared following our 'CREATE TABLE' command.

Renaming a table

We may occasionally need to rename a table, for clarity's sake or just personal preference; this is easily accomplished:

ALTER TABLE clients
    RENAME TO customers;

We'll paste it into psql, hit enter, and then rerun the '\dt' command to verify it worked:

demodb1=> \dt
           List of relations
 Schema |   Name    | Type  |   Owner   
--------+-----------+-------+-----------
 public | customers | table | demorole1
(1 row)

Fanstastic! Note this 'ALTER' command doesn't change any data inside the table.

Dropping a table

Tables can be dropped (deleted) from our database using the 'DROP TABLE' command:

DROPT TABLE customers;

NOTE: This deletion is irreversible; a dropped table and all the data it contains is permanently erased from the database.

Summary

Manual creation and management of tables in postgres is a useful skill. Future articles in this series will explore other common commands for manipulating table objects and the data they contain.

Mike

Article Comments:

Jon commented Sun Dec 20 20:53:36 UTC 2009:

There is a typo on the "Dropping a table"

DROPT TABLE customers;

should be

DROP TABLE customers;

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)