Before we install postgres, we should quick perform a quick update of the apt-get repository:<\/p>\n
apt-get update<\/pre>\nOnce apt-get has updated go ahead and download Postgres and its helpful accompanying dependencies:<\/p>\n
sudo apt-get install postgresql postgresql-contrib<\/pre>\nWith that, postgres is installed on your server.<\/p>\n
<\/div>\nCreate Your PostgreSQL Roles and Databases<\/h2>\n
Once Postgres has been installed on your server, you can start to configure the database.<\/p>\n
Postgres uses the concept of roles to distinguish the variety of users that can connect to a database. When it is first installed on a server, the default postgres user is actual named \u201cpostgres\u201d. The other users are specified in one of variety of ways. The common methods are ident<\/i> and md5<\/i>. The postgres default is to use ident<\/i> authentication, tying each server user to a Postgres account. The alternative which can be set in the authentication configuration, located in \u201c\/etc\/postgresql\/9.1\/main\/pg_hba.conf \u201c is md5 which asks the client to supply an encrypted password.<\/p>\n
To begin creating custom users, first switch into the default user:<\/p>\n
sudo su \u2013 postgres<\/pre>\nOnce logged in as this user, you can move forward to create more roles in your PostgreSQL system:<\/p>\n
createuser<\/pre>\nEnter name of role to add: newuser\nShall the new role be a superuser? (y\/n) y<\/pre>\nTo outfit your user with a password, you can add the words \u2013pwprompt to the createuser command:<\/p>\n
createuser --pwprompt<\/pre>\n<\/div>\nConnecting to the Postgres Databases<\/h2>\n
With the users that you want to use to log into your Postgres shell set up, you can proceed to make a database for them to use.<\/p>\n
You can create the Postgres database as a superuser. In this case, we will use the default super user.<\/p>\n
Go ahead and switch into the postgres user once again:<\/p>\n
su \u2013 postgres<\/pre>\nAs postgres, you can start to create your first usable postgres database:<\/p>\n
createdb events<\/pre>\nAnd with that you can finally connect to the postgres shell.<\/p>\n
<\/div>\nHow to Create and Delete a Postgres Tables<\/h2>\n
Once we log into the correct database (using the
psql -d events<\/code> command where events<\/strong> is that database’s name), we can create tables within it.<\/p>\nLet\u2019s imagine that we are planning a get together of friends. We can use Postgres to track the details of the event.<\/p>\n
Let\u2019s create a new Postgres table:<\/p>\n
CREATE TABLE potluck (name VARCHAR(20),\nfood VARCHAR(30),\nconfirmed CHAR(1), \nsignup_date DATE);<\/pre>\nThis command accomplishes a number of things:<\/p>\n
\n
- It has created a table called potluck within the database, newdb.<\/li>\n
- We have set up 4 columns in the table\u2014name, food, confirmed, and signup date.<\/li>\n
- The \u201cname\u201d column has been limited by the VARCHAR command to be under 20 characters long.<\/li>\n
- The \u201cfood\u201d column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.<\/li>\n
- The \u201cconfirmed\u201d column records whether the person has RSVP\u2019d with one letter, Y or N.<\/li>\n
- The \u201cdate\u201d column will show when they signed up for the event. Postgres requires that dates be written as yyyy-mm-dd<\/li>\n<\/ol>\n
Once entered, postgres should confirm the table creation with the following line:<\/p>\n
CREATE TABLE<\/pre>\nYou can additionally see all of the tables within the database with the following command:<\/p>\n
\\dt<\/pre>\nThe result, in this case, should look like this:<\/p>\n
postgres=# \\dt\n List of relations\n Schema | Name | Type | Owner \n--------+---------+-------+-------\n public | potluck | table | root\n(1 row)<\/pre>\n<\/div>\nHow to Add Information to a Postgres Table<\/h2>\n
We have a working table for our party. Now it\u2019s time to start filling in the details.<\/p>\n
Use this format to insert information into each row:<\/p>\n
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('John', 'Casserole', 'Y', '2012-04-11');<\/pre>\nOnce you input that in, you will see the words:<\/p>\n
INSERT 0 1<\/pre>\nLet\u2019s add a couple more people to our group:<\/p>\n
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Sandy', 'Key Lime Tarts', 'N', '2012-04-14');\nINSERT INTO potluck (name, food, confirmed, signup_date)VALUES ('Tom', 'BBQ','Y', '2012-04-18');\nINSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Tina', 'Salad', 'Y','2012-04-18');<\/pre>\nWe can take a look at our table:<\/p>\n
SELECT * FROM potluck;\n name | food | confirmed | signup_date \n-------+----------------+-----------+-------------\n John | Casserole | Y | 2012-04-11\n Sandy | Key Lime Tarts | N | 2012-04-14\n Tom | BBQ | Y | 2012-04-10\n Tina | Salad | Y | 2012-04-18\n(4 rows)<\/pre>\nShould 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:<\/p>\n
DELETE FROM potluck WHERE name = 'John' ;<\/pre>\n<\/div>\nHow to Add and Delete a Column<\/h2>\n
We are creating a handy chart, but it is missing some important information: our attendees\u2019 emails.<\/p>\n
We can easily add this:<\/p>\n
ALTER TABLE potluck ADD email VARCHAR(40);<\/pre>\nThis command puts the new column called “email” at the end of the table by default, and the VARCHAR command limits it to 40 characters.<\/p>\n
Just as you can add a column, you can delete one as well:<\/p>\n
ALTER TABLE potluck DROP email;<\/pre>\nI guess we will never know how to reach the picnickers.<\/p>\n
<\/div>\nHow to Update Information in the Table<\/h2>\n
Now that we have started our potluck list, we can address any possible changes.<\/p>\n
For example: Sandy has confirmed that she is attending, so we are going to update that in the table.<\/p>\n
UPDATE potluck set confirmed = 'Y' WHERE name = 'Sandy';<\/pre>\nYou can also use this command to add information into specific cells, even if they are empty.<\/p>\n","protected":false},"excerpt":{"rendered":"
Install Postgres Before we install postgres, we should quick perform a quick update of the apt-get repository: apt-get update Once apt-get has updated go ahead… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-784","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/784","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=784"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/784\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=784"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=784"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=784"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}