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 UbuntuIn 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-contribAfter installation, create a new user to manage the database we'll be creating:
sudo adduser postgres_userLog into the default PostgreSQL user (called "postgres") to create a database and assign it to the new user:
sudo su - postgres psqlYou 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:
\qExit out of the default "postgres" user account and log into the user you created with the following commands:
exit sudo su - postgres_userSign into the database you created with the following command:
psql my_postgres_dbWe are now ready to learn about table management.
Table Creation Syntax in PostgreSQLOur 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.
PostgreSQL Column and Table DefinitionsColumn definitions follow this syntax pattern:
column_name data_type (optional_data_length_restriction) column_constraintsThe column name should be self-explanatory.
PostgreSQL Data TypesThe data type can be any of the following:
PostreSQL Column and Table ConstraintsColumn 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:
How to Create a Table in PostgreSQLLet'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 TABLEWe 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.
How to Change Table Data in PostgreSQLWe 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 TABLEWe 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;
Deleting Tables in PostgreSQLWe can delete the table we created by typing:
DROP TABLE playground_equip;
DROP TABLEIf we give that command to a table that does not exist, we will receive the following error:
ERROR: table "playground_equip" does not existTo 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 TABLEThis time, it tells us that the table was not found, but continues instead of throwing an error.
ConclusionYou 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.
|Posted by: ASPHostServer Administrator - Mon, Dec 30, 2013 at 1:05 AM. This article has been viewed 2353 times.|
|Online URL: http://faq.asphosthelpdesk.com/article.php?id=229|