Postgres sql database cheatsheet

In this post, I am installing postgres on ubuntu and then will perform different sql operations on it. Installation commands may vary with other distributions like CentOS or Mac.

To install:

This will also install other dependencies.

To connect to Postgres:

To check the login info, use the following command

By default, PostgreSQL will not allow general remote access. To grant permission to connect, you must edit a configuration file, pg_hba.conf. This file lives in the database file area (/etc/postgresql/9.6/main/pg_hba.conf in our example), and contains entries that grant or reject permission for users to connect to the database.

Connect from another system: 

To check network connectivity, you can use psql installed on another machine on the network as a client, or any other PostgreSQL compatible application.

With psql, you specify the host (either the name or IP address) with the -h option, and one of the system databases (as you haven’t yet created a real database):

Create DB: $ createdb mydb or $ createdb <db_name>
Remove DB: $ dropdb mydb

To access the database:

To check version info from database:

To test SELECT commands: 

To create a table: 

Another table: 

You can use multiple lines to create tables. Query end up with a semicolon );

To remove a table: 

To insert a row in weather table: 

It returned 0 1 where 0 is OID (status code means no error) and 1 means 1 row affected.

COPY command to quickly copy multiple queries:

Query table:

To remove duplicate rows:

Here again, the result row ordering might vary. You can ensure consistent results by using DISTINCT and ORDER BY together: [2]

Notes:
[1] While SELECT * is useful for off-the-cuff queries, it is widely considered bad style in production code, since adding a column to the table would change the results.

[2] In some database systems, including older versions of PostgreSQL, the implementation of DISTINCT automatically orders the rows and so ORDER BY is unnecessary. But this is not required by the SQL standard, and current PostgreSQL does not guarantee that DISTINCT causes the rows to be ordered.

Update between tables: 

You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows:

UPDATE weather
SET temp_hi = temp_hi – 2, temp_lo = temp_lo – 2
WHERE date > ‘1994-11-28’;
Look at the new state of the data:

Joins Between Tables

 

Delete: 

Rows can be removed from a table using the DELETE command. Suppose you are no longer interested in the weather of Hayward. Then you can do the following to delete those rows from the table:

DELETE FROM weather WHERE city = ‘Hayward’;
All weather records belonging to Hayward are removed.

One should be wary of statements of the form

Without a qualification, DELETE will remove all rows from the given table, leaving it empty. The system will not request confirmation before doing this.

Postgres extensions: 

Views:

Suppose the combined listing of weather records and city location is of particular interest to your application, but you do not want to type the query each time you need it. You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table:

Foreign eys: 

Consider the following problem: You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table. This is called maintaining the referential integrity of your data. In simplistic database systems this would be implemented (if at all) by first looking at the cities table to check if a matching record exists, and then inserting or rejecting the new weather records. This approach has a number of problems and is very inconvenient, so PostgreSQL can do this for you.

The new declaration of the tables would look like this:

Now try inserting an invalid record:

INSERT INTO weather VALUES (‘Berkeley’, 45, 53, 0.0, ‘1994-11-28’);
ERROR: insert or update on table “weather” violates foreign key constraint “weather_city_fkey”
DETAIL: Key (city)=(Berkeley) is not present in table “cities”.
The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial, but just refer you to Chapter 5 for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn about them.

Inheritance:

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Let’s create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you’re really clever you might invent some scheme like this:

 

CREATE VIEW cities AS

SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this:

CREATE TABLE cities (
name text,
population real,
altitude int — (in ft)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
In this case, a row of capitals inherits all columns (name, population, and altitude) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. State capitals have an extra column, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables.

For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 feet:

SELECT name, altitude
FROM cities
WHERE altitude > 500;
which returns:

name | altitude
———–+———-
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude over 500 feet:

SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
name | altitude
———–+———-
Las Vegas | 2174
Mariposa | 1953
(2 rows)
Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE, and DELETE — support this ONLY notation.