Learn how to use PostgreSQL to store your relational data
Before you get started with using PostgreSQL, you'll have to install it. Follow these steps to get started:
There are a couple of ways to install PostgreSQL. One of the easier ways to get started is with Postgres.app. Navigate to http://postgresapp.com/ and then click "Download":
Once it's finished downloading, double click on the file to unzip then move the PostgreSQL elephant icon into your
applicationsfolder. Double click the icon to launch the application.
You should see a new window launched that says "Welcome to Postgres". If it says that it cannot connect to the postgres server this means that the DEFAULT port is probably already in use. Make sure you don't have any other instances of Postgres on your computer. Uninstall them if you do and then resume with these steps. Click on the button that says "Open psql":
Postgres.app will by default create a role and database that matches your current macOS username. You can connect straight away by running
You should now be all set up to start using PostgreSQL. For documentation on command line tools etc see http://postgresapp.com/documentation/
Digital Ocean have got a great article on getting started with postgres. A quick summary is below.
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
By default the only role created is the default 'postgres', so PostgreSQL will only respond to connections from an Ubuntu user called 'postgres'. We need to pretend to be that user and create a role matching our actual Ubuntu username:
sudo -u postgres createuser --interactive
This command means 'run the command
createuser --interactive as the user called "postgres"'.
When asked for the name of the role enter your Ubuntu username. If you're not sure, open a new Terminal tab and run
When asked if you want to make the role a superuser, type 'y'.
We now need to create the database matching the role name, as PostgreSQL expects this. Run:
sudo -u postgres createdb [your user name]
You can now connect to PostgreSQL by running
Create your first PostgreSQL database
To start PostgreSQL, type this command into the terminal:
Next type this command into the PostgreSQL interface:
CREATE DATABASE test;
NOTE: Don't forget the semi-colon. If you do, useful error messages won't show up.
Create new users for your database
If you closed the PostgreSQL server, start it again with:
To create a new user, type the following into the psql prompt:
CREATE USER testuser;
Next we need to give our user permissions to access the test database we created above. Enter the following command into the
GRANT ALL PRIVILEGES ON DATABASE test TO testuser;
PostGIS - Spacial and Geographic objects for PostgreSQL
If you've installed Postgres App as in the example above, you can easily extend it to include PostGIS. Follow these steps to begin using PostGIS:
Ensure that you're logged in as a user OTHER THAN
postgres. Follow the steps above to enable your default user to be able to access the
psqlprompt. (installation step 7)
Type the following into the
psqlprompt to add the extension:
CREATE EXTENSION postgis;
PostGIS Distance between two sets of coordinates
After you've extended PostgreSQL with PostGIS you can begin to use it. Type
the following command into the
psql command line:
SELECT ST_Distance(gg1, gg2) As spheroid_dist FROM (SELECT ST_GeogFromText('SRID=4326;POINT(-72.1235 42.3521)') As gg1, ST_GeogFromText('SRID=4326;POINT(-72.1235 43.1111)') As gg2 ) As foo ;
This should return
spheroid_dist along with a value in meters. The
example above returns:
84315.42034614 which is rougly 84.3km between the two
Once you are serving the database from your computer
To change db
To see the tables in the database
To select (and show in terminal) all tables
SELECT * FROM table_name
To make a table
CREATE TABLE table_name (col_name1, col_name2)
To add a row
INSERT INTO table_name ( col_name ) VALUES ( col_value)col_name only require if only some of the cols are being filled out
To edit a column to a table
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT expression
To add a column to a table
ALTER TABLE table_name ADD COLUMN column_name data_type
To find the number of instances where the word “Day” is present in the title of a table
SELECT count(title) FROM table_name WHERE title LIKE '%Day%’;
To delete a row in a table
DELETE FROM table_name WHERE column_name = ‘hello';
Postgresql follows the SQL convention of calling relations TABLES, attributes COLUMNs and tuples ROWS
Transaction All or nothing, if something fails the other commands are rolled back like nothing happened
Reference When a table is being created you can reference a column in another table to make sure any value which is added to that column exists in the referenced table.
CREATE TABLE cities ( name text NOT NULL, postal_code varchar(9) CHECK (postal_code <> ''), country_code char(2) REFERENCES countries, PRIMARY KEY (country_code, postal_code) );
<> means not equal
Join reads You can join tables together when reading them,
Inner Join Joins together two tables by specifying a column in each to join them by i.e.
SELECT cities.*, country_name FROM cities INNER JOIN countries ON cities.country_code = countries.country_code;
This will select all of the columns in both the countries and cities tables the data, the rows are matched up by country_code.
Grouping You can put rows into groups where the group is defined by a shared value in a particular column.
SELECT venue_id, count(*) FROM events GROUP BY venue_id;
This will group the rows together by the venue_id, count is then performed on each of the groups.