How To Install and Use PostgreSQL on Ubuntu 12.04

Install Postgres

apt-get update

sudo apt-get install postgresql postgresql-contrib

Create Your PostgreSQL Roles and Databases

 su – postgres

createuser

Enter name of role to add: newuser
Shall the new role be a superuser? (y/n) y

createuser --pwprompt

Connecting to the Postgres Databases

su – postgres

 createdb events

How to Create and Delete a Postgres Tables

CREATE TABLE potluck (name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE);

1. It has created a table called potluck within the database, newdb.


2. We have set up 4 columns in the table—name, food, confirmed, and signup date.


3. The "name” column has been limited by the VARCHAR command to be under 20 characters long.


4. The "food” column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.


5. The "confirmed” column records whether the person has RSVP’d with one letter, Y or N.


6. The "date” column will show when they signed up for the event. Postgres requires that dates be written as yyyy-mm-dd

CREATE TABLE

\dt

postgres=# \dt
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | potluck | table | root
(1 row)

How to Add Information to a Postgres Table

INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('John', 'Casserole', 'Y', '2012-04-11');

INSERT 0 1

INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Sandy', 'Key Lime Tarts', 'N', '2012-04-14');
INSERT INTO potluck (name, food, confirmed, signup_date)VALUES ('Tom', 'BBQ','Y', '2012-04-18');
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Tina', 'Salad', 'Y','2012-04-18');

SELECT * FROM potluck;
 name  |      food      | confirmed | signup_date 
-------+----------------+-----------+-------------
 John  | Casserole      | Y         | 2012-04-11
 Sandy | Key Lime Tarts | N         | 2012-04-14
 Tom   | BBQ            | Y         | 2012-04-10
 Tina  | Salad          | Y         | 2012-04-18
(4 rows)

 DELETE FROM potluck WHERE name = 'John' ;

How to Add and Delete a Column

ALTER TABLE potluck ADD email VARCHAR(40);

ALTER TABLE potluck DROP email;

How to Update Information in the Table

 UPDATE potluck set confirmed = 'Y' WHERE name = 'Sandy';