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.
Going forward I'll assume you have a database and a normal database role: 'demodb1' owned by role 'demorole1'.
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.
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.