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 VPS.
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.
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:
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:
We are now ready to learn about table management.
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:
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.
Column definitions follow this syntax pattern:
column_name data_type (optional_data_length_restriction) column_constraints
The column name should be self-explanatory.
The data type can be any of the following:
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.
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:
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.
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;
We can see the extra column by typing:
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;
We can delete the table we created by typing:
DROP TABLE playground_equip;
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.
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.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.