How To Create, Remove, & Manage Tables in PostgreSQL on a Cloud Server


What is PostgreSQL?

PostgreSQL is a database management system that uses the SQL querying language. It is a very stable and feature-rich database system that can be used to store the data from other applications on your server.

In this article, we will discuss how to create and manage tables within the postgreSQL interface. You will learn how to properly configure tables and use them to store your information.

How to Install and Log Into PostgreSQL on Ubuntu

In this guide, we will install PostgreSQL on Ubuntu 12.04, but it should be available in most other distributions' default repositories.

Type the following commands to install:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
After installation, create a new user to manage the database we'll be creating:
sudo adduser postgres_user
Log into the default PostgreSQL user (called "postgres") to create a database and assign it to the new user:
sudo su - postgres
psql
You will be dropped into the PostgreSQL command prompt.

Create a new user that matches the system user you created. Then create a database managed by that user:
CREATE USER postgres_user WITH PASSWORD 'password';
CREATE DATABASE my_postgres_db OWNER postgres_user;
Exit out of the interface with the following command:
\q
Exit out of the default "postgres" user account and log into the user you created with the following commands:
exit
sudo su - postgres_user
Sign into the database you created with the following command:
psql my_postgres_db
We are now ready to learn about table management.

Table Creation Syntax in PostgreSQL

Our database does not have any tables yet. We can verify this by asking PostgreSQL to give us a listing of the available tables with this command:
\d
No relations found.
We can create a new table by adhering to the following syntax:
CREATE TABLE new_table_name (
	table_column_title TYPE_OF_DATA column_constraints,
	next_column_title TYPE_OF_DATA column_constraints,
	table_constraint
	table_constraint
) INHERITS existing_table_to_inherit_from;
The part after the closing parenthesis up until the semi-colon is an optional construction to inherit all columns from an existing table in addition to the columns listed in the earlier definition.

The part inside of the parentheses is divided into two parts: column definitions and table constraints.

PostgreSQL Column and Table Definitions

Column definitions follow this syntax pattern:
column_name data_type (optional_data_length_restriction) column_constraints
The column name should be self-explanatory.

PostgreSQL Data Types

The data type can be any of the following:

PostreSQL Column and Table Constraints

Column definitions can also have constraints that provide rules for the type of data found in the column. The following can be used as space-separated values following the data type:
After the columns are defined, table-wide constraints may be declared. Table-wide constraints can be either UNIQUE, PRIMARY KEY, CHECK, or REFERENCES.

How to Create a Table in PostgreSQL

Let's create a test table to practice on. We will create a table called "pg_equipment" that defines various pieces of playground equipment. Type the following table definition:
CREATE TABLE pg_equipment (
	equip_id serial PRIMARY KEY,
	type varchar (50) NOT NULL,
	color varchar (25) NOT NULL,
	location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
	install_date date
	);
NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
CREATE TABLE
We can see our new table by typing "\d" into the prompt:
\d
                       List of relations
 Schema |           Name            |   Type   |     Owner     
--------+---------------------------+----------+---------------
 public | pg_equipment              | table    | postgres_user
 public | pg_equipment_equip_id_seq | sequence | postgres_user
(2 rows)
The table is listed, as well as the sequence created by the "equip_id" serial data type declaration.

How to Change Table Data in PostgreSQL

We can change the definition of our tables with the following general syntax:
ALTER TABLE table_name Action_TO_Take;
For example, we can add a column to our "pg_equipment" table by entering this command:
ALTER TABLE pg_equipment ADD COLUMN functioning bool;
ALTER TABLE
We can see the extra column by typing:
\d pg_equipment
    Column    |         Type          |                            Modifiers                            
--------------+-----------------------+-----------------------------------------------------------------
 equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
 type         | character varying(50) | not null
 color        | character varying(25) | not null
 location     | character varying(25) | 
 install_date | date                  | 
 functioning  | boolean               |
 . . .
To add a default value that specifies that "equipment should be considered working unless otherwise noted", give the following command:
ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';
If we want to ensure that the value is also not null, we can do this:
ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;
To rename the column, use the following syntax:
ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;
To remove the column we just created, enter this command:
ALTER TABLE pg_equipment DROP COLUMN working_order;
We can rename the entire table with this command:
ALTER TABLE pg_equipment RENAME TO playground_equip;

Deleting Tables in PostgreSQL

We can delete the table we created by typing:
DROP TABLE playground_equip;
DROP TABLE
If we give that command to a table that does not exist, we will receive the following error:
ERROR: table "playground_equip" does not exist
To avoid this error, we can tell postgreSQL to delete the table if it exists and return successfully either way. We do this by issuing the following command:
DROP TABLE IF EXISTS playground_equip;
NOTICE:  table "playground_equip" does not exist, skipping
DROP TABLE
This time, it tells us that the table was not found, but continues instead of throwing an error.

Conclusion

You should now know enough to create and manage simple tables in PostgreSQL. These skills will be helpful if you are managing data from another application, or learning the how to control PostgreSQL from the command line.

Article ID: 229
Created On: Mon, Dec 30, 2013 at 1:05 AM
Last Updated On: Sun, Jan 5, 2014 at 8:54 PM
Authored by: ASPHostServer Administrator [asphostserver@gmail.com]

Online URL: http://faq.asphosthelpdesk.com/article.php?id=229