apt-get updateOnce apt-get has updated go ahead and download Postgres and its helpful accompanying dependencies:
sudo apt-get install postgresql postgresql-contribWith that, postgres is installed on your server.
su – postgresOnce logged in as this user, you can move forward to create more roles in your PostgreSQL system:
Enter name of role to add: newuser Shall the new role be a superuser? (y/n) yTo outfit your user with a password, you can add the words –pwprompt to the createuser command:
su – postgresAs postgres, you can start to create your first usable postgres database:
createdb eventsAnd with that you can finally connect to the postgres shell.
psql -d eventscommand where events is that database's name), we can create tables within it.
CREATE TABLE potluck (name VARCHAR(20), food VARCHAR(30), confirmed CHAR(1), signup_date DATE);This command accomplishes a number of things:
CREATE TABLEYou can additionally see all of the tables within the database with the following command:
\dtThe result, in this case, should look like this:
postgres=# \dt List of relations Schema | Name | Type | Owner --------+---------+-------+------- public | potluck | table | root (1 row)
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('John', 'Casserole', 'Y', '2012-04-11');Once you input that in, you will see the words:
INSERT 0 1Let’s add a couple more people to our group:
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');We can take a look at our table:
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)Should we want to, then, follow up by removing an unlucky attendee, in this John and his casserole, from our potluck we can accomplish this with the Delete command:
DELETE FROM potluck WHERE name = 'John' ;
ALTER TABLE potluck ADD email VARCHAR(40);This command puts the new column called "email" at the end of the table by default, and the VARCHAR command limits it to 40 characters. Just as you can add a column, you can delete one as well:
ALTER TABLE potluck DROP email;I guess we will never know how to reach the picnickers.
UPDATE potluck set confirmed = 'Y' WHERE name = 'Sandy';You can also use this command to add information into specific cells, even if they are empty.
Article ID: 227
Created On: Mon, Dec 30, 2013 at 12:46 AM
Last Updated On: Mon, Dec 30, 2013 at 12:46 AM
Authored by: ASPHostServer Administrator [firstname.lastname@example.org]
Online URL: http://faq.asphosthelpdesk.com/article.php?id=227