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 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.
\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.
column_name data_type (optional_data_length_restriction) column_constraintsThe column name should be self-explanatory.
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:
\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.
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:
\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;
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.
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