PostgreSQL is an open source database management system that utilized the SQL querying language. PostgreSQL, or simply "Postgres", is a very useful tool on a VPS server because it can handle the data storage needs of websites and other applications.
In this guide, we will examine how to query a PostgreSQL database. This will allow us to instruct Postgres to return all of the data it manages that matches the criteria we are looking for.
This tutorial assumes that you have installed Postgres on your machine. We will be using Ubuntu 12.04, but any modern Linux distribution should work.
We will be downloading a sample database to work with from the internet.
First, log into the default Postgres user with the following command:
sudo su - postgres
We will acquire the database file by typing:
wget http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz
Extract the gzipped archive and change to the content directory:
tar xzvf world-1.0.tar.gz cd dbsamples-0.1/world
Create a database to import the file structure into:
createdb -T template0 worlddb
Finally, we will use the .sql file as input into the newly created database:
psql worlddb < world.sql
We are now ready to log into our newly create environment:
psql worlddb
Before we begin, let's get an idea of what kind of data we just imported. To see the list of tables, we can use the following command:
\d+
List of relations Schema | Name | Type | Owner | Size | Description --------+-----------------+-------+----------+--------+------------- public | city | table | postgres | 264 kB | public | country | table | postgres | 48 kB | public | countrylanguage | table | postgres | 56 kB | (3 rows)
We have three tables here. If we want to see the columns that make up the "city" table, we can issue this command:
\d city
Table "public.city" Column | Type | Modifiers -------------+--------------+----------- id | integer | not null name | text | not null countrycode | character(3) | not null district | text | not null population | integer | not null Indexes: "city_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
We can see information about each of the columns, as well as this table's relationship with other sets of data.
We query (ask for) information from Postgres by using "select" statements. These statements use this general syntax:
SELECT columns_to_return FROM table_name;
For example, if we issue "\d country", we can see that the "country" table has many columns. We can create a query that lists the name of the country and the continent it is on with the following:
SELECT name,continent FROM country;
name | continent ----------------------------------------------+--------------- Afghanistan | Asia Netherlands | Europe Netherlands Antilles | North America Albania | Europe Algeria | Africa American Samoa | Oceania Andorra | Europe . . .
To view all of the columns in a particular table, we can use the asterisk (*) wildcard character. This means "match every possibility" and, as a result, will return every column.
SELECT * FROM city;
id | name | countrycode | district | population ------+-----------------------------------+-------------+-------------------------------+------------ 1 | Kabul | AFG | Kabol | 1780000 2 | Qandahar | AFG | Qandahar | 237500 3 | Herat | AFG | Herat | 186800 4 | Mazar-e-Sharif | AFG | Balkh | 127800 5 | Amsterdam | NLD | Noord-Holland | 731200 6 | Rotterdam | NLD | Zuid-Holland | 593321 7 | Haag | NLD | Zuid-Holland | 440900 . . .
Here, we see the "city" table in its entirety.
You can organize the results of your query by using the "order by" clause. This allows you to specify a sort order to the returned data.
The "order by" clause comes after the normal select statement. This is the general syntax:
SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];
If we wanted to select the country and continent from the country table, and then order by continent, we can give the following:
SELECT name,continent FROM country ORDER BY continent;
name | continent ----------------------------------------------+--------------- Algeria | Africa Western Sahara | Africa Madagascar | Africa Uganda | Africa Malawi | Africa Mali | Africa Morocco | Africa Côte d\u0092Ivoire | Africa . . .
As you can see, by default the order statement organizes data in ascending order. This means that it starts at the beginning of lettered organizations and the lowest number for numerical searches.
If we want to reverse the sort order, we can type "desc" after the "order by" column declaration:
SELECT name,continent FROM country ORDER BY continent DESC;
name | continent ----------------------------------------------+--------------- Paraguay | South America Bolivia | South America Brazil | South America Falkland Islands | South America Argentina | South America Venezuela | South America Guyana | South America Chile | South America . . .
We can also choose to sort by more than one column. We can have a primary sort field, and then additional sort fields that are used if multiple records have the same value in the primary sort field.
For instance, we can sort by continent, and then by country to get an alphabetical list of country records in each continent:
SELECT name,continent FROM country ORDER BY continent,name;
name | continent ----------------------------------------------+--------------- Angola | Africa Burundi | Africa Benin | Africa Burkina Faso | Africa Botswana | Africa Central African Republic | Africa Côte d\u0092Ivoire | Africa Cameroon | Africa Congo, The Democratic Republic of the | Africa . . .
We now have alphabetical sorting in two columns.
We have learned how to select only certain information from a table by specifying the columns we want, but Postgres provides more fine-grained filtering mechanisms.
We can filter results by including a "where" clause. A where clause is followed by a description of the results we would like to receive.
For example, if we wanted to select all of the cities in the United States, we could tell Postgres to return the name of cities where the three digit country code is "USA":
SELECT name FROM city WHERE countrycode = 'USA';
name ------------------------- New York Los Angeles Chicago Houston Philadelphia Phoenix San Diego Dallas San Antonio . . .
String values, like USA above, must be placed in single-quotations to be interpreted correctly by Postgres.
The previous query used "=" to compare whether the column value is an exact match for the value given on the right of the expression. We can search more flexibly though with the "like" comparison operator.
The like operator can use "_" as a wildcard to match a single character and "%" as a wildcard that matches zero or more characters.
We can also combine filtering terms with either "and" or "or". Let's combine some filtering to find cities in the US that have names starting with "N":
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';
name -------------------- New York Nashville-Davidson New Orleans Newark Norfolk Newport News Naperville New Haven North Las Vegas Norwalk New Bedford Norman (12 rows)
We can, of course, sort these results just like with regular, unfiltered select data.
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;
name -------------------- Naperville Nashville-Davidson Newark New Bedford New Haven New Orleans Newport News New York Norfolk Norman North Las Vegas Norwalk (12 rows)
We are going to examine some more complex queries. Consider the following:
SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
country | capital | continent ---------------------------------------+-----------------------------------+--------------- Algeria | Alger | Africa Angola | Luanda | Africa Benin | Porto-Novo | Africa Botswana | Gaborone | Africa Burkina Faso | Ouagadougou | Africa Burundi | Bujumbura | Africa Cameroon | Yaoundé | Africa Cape Verde | Praia | Africa Central African Republic | Bangui | Africa Chad | N´Djaména | Africa . . .
This query has a few different parts. Let's start at the end and work backwards.
The "order by" section of the statement (ORDER BY continent,country) should be familiar.
This section of the statement tells Postgres to sort based on continent first, and then sort the entries with matching continent values by the country column.
To explain the next portion, the table specification, we will learn about table joins.
Postgres allows you to select data from different, related tables using the "join" clause. Tables are related if they each have a column that can that refers to the same data.
In our example database, our "country" and "city" table share some data. We can see that the "country" table references the "city" table by typing:
\d country
. . . . . . Foreign-key constraints: "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id) . . . . . .
This statement tells us that the "capital" column within the "country" table is a reference to the "id" column within the "city" table. This means that we can almost treat these two tables as one giant table by matching up the values in those columns.
In our query, the table selection reads "FROM country JOIN city ON country.capital = city.id".
In this statement, we are telling Postgres to return information from both tables. The "join" statement specifies the default join, which is also called an "inner join".
An inner join will return the information that is present in both tables. For example, if we were matching columns that were not explicitly related as foreign-keys, we could run into a situation where one table had values that weren't matched in the other table. These would not be returned using a regular join.
The section after the "on" keyword specifies the columns that the tables share so that Postgres knows how the data is related. This information is given by specifying:
table_name.column_name
In our case, we are selecting records that have matching values in both tables, where the capital column of the country table should be compared to the id column of the city table.
We are now to the beginning of our query statement. The part the selects the columns. This part should be fairly simple to decipher based on the last section.
We now have to name the table that the columns are in if we are trying to select a column name that is present in both tables.
For instance, we've selected the "name" column from both the country table and the city table. If we were to leave off the "table_name." portion of the selection, the match would be ambiguous and Postgres would not know which data to return.
We get around this problem by being explicit about which table to select from when there are naming collisions. It is sometimes helpful to name the tables regardless of whether the names are unique in order to maintain readability.,/p>
You should now have a basic idea of how to formulate queries. This gives you the ability to return specific data from various sources. This is helpful for building or using applications and interactive webpages around this technology.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!