Eight Interesting PostgreSQL Features You May Not Know About

Hello, Habr! We invite you to a free Demo lesson “Parallel cluster CockroachDB”, which will take place as part of the PostgreSQL course. We also publish a translation of Tom Brown’s article – Principal Systems Engineer at EnterpriseDB.


In this article, we will look at some useful tips for working with PostgreSQL:

  • Link to the whole line

  • Comparing multiple columns

  • Common table expressions

  • Custom configuration options

  • Comparison of boolean values ​​without “equals”

  • Change column type at no extra cost

  • Information about the section the string is in

  • Tables are types

Link to the entire line

Have you ever tried a query like this?

SELECT my_table FROM my_table;

The request looks strange. It returns all columns of the table as one column. Why might you need it? Well, I think you have referenced tables in the following way more than once:

SELECT table_a.column, table_b.column
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.aid;

There is a row reference here, but only one column. And there is nothing unusual here. How about this?

SELECT data, backup_data
FROM data
FULL JOIN backup_data ON data = backup_data
WHERE data IS NULL OR backup_data IS NULL;

Here is the data table and its backup_data. And what if we want to see the difference between them: find changes since the backup and find out if we have lost any lines in the backup?

For demonstration, let’s create a table and insert three rows:

postgres=# CREATE TABLE data (id serial, person text, country text);

CREATE TABLE

postgres=# INSERT INTO data (person, country)

  VALUES ('Tim','France'),('Dieter','Germany'),('Marcus','Finland');

INSERT 0 3

Now let’s create an identical copy of the table and copy the data into it:

postgres=# CREATE TABLE backup_data (id serial, person text, country text);

CREATE TABLE

postgres=# INSERT INTO backup_data SELECT * FROM data;

INSERT 0 3

We want the tables to be different, so we delete one row and add one row:

postgres=# DELETE FROM data WHERE id = 2;

DELETE 1

postgres=# INSERT INTO data (person, country)

  VALUES ('Roberto','Italy');

INSERT 0 1

Let’s see what happens when we run a query to compare tables:

postgres=# SELECT data, backup_data

postgres-# FROM data

postgres-# FULL JOIN backup_data ON data = backup_data

postgres-# WHERE data IS NULL OR backup_data IS NULL;

       data        |    backup_data  

-------------------+--------------------

                   | (2,Dieter,Germany)

 (4,Roberto,Italy) |

(2 rows)

We can see that the backup_data table contains a row that is not in the data table and vice versa.

You can use this feature like this:

postgres=# SELECT to_jsonb(data) FROM data;

                   to_jsonb                   

-----------------------------------------------------

 {"id": 1, "person": "Tim", "country": "France"}

 {"id": 3, "person": "Marcus", "country": "Finland"}

 {"id": 4, "person": "Roberto", "country": "Italy"}

(3 rows)

We’ve turned all our data into JSON!

Comparing multiple columns

This is a very interesting trick that can be used to make queries shorter and more readable.

Let’s say we have the following request:

SELECT country, company, department

FROM suppliers

WHERE country = 'Australia'

  AND company = 'Skynet'

  AND department="Robotics";

We can get rid of the AND:

SELECT country, company, department

FROM suppliers

WHERE (country, company, department) = ('Australia','Skynet','Robotics');

And we can also use IN for OR conditions:

SELECT country, company, department

FROM suppliers

WHERE department="Robotics"

AND (

  (country = 'Australia'

    AND company = 'Skynet')

OR

  (country = 'Norway'

    AND company = 'Nortech')

);

This query can be shortened:

SELECT country, company, department

FROM suppliers

WHERE department="Robotics"

  AND (country, company) IN (('Australia','Skynet'),('Norway','Nortech'));

Common table expressions

Let’s say you have read-only access to the database and cannot create tables. And also you have a small dataset that you would like to join with existing tables.

SELECT station, time_recorded, temperature

FROM weather_stations;



    station     |    time_recorded    | temperature

----------------+---------------------+-------------

 Biggin_Hill_14 | 2020-02-02 13:02:44 |        22.4

 Reigate_03     | 2020-02-02 16:05:12 |        20.9

 Aberdeen_06    | 2020-02-02 15:52:49 |         8.5

 Madrid_05      | 2020-02-02 14:05:27 |        30.1

(4 rows)

Let’s say you want to get an idea of ​​how warm or cold each station is. Then you can make a request like this:

SELECT station, 

  CASE

    WHEN temperature <= 0 THEN 'freezing'

    WHEN temperature < 10 THEN 'cold'

    WHEN temperature < 18 THEN 'mild'

    WHEN temperature < 30 THEN 'warm'

    WHEN temperature < 36 THEN 'hot'

    WHEN temperature >= 36 THEN 'scorching'

  END AS temp_feels

FROM weather_stations;

This method is inconvenient to add conditions. This can be simplified by creating a pseudo-table using common table expressions (CTEs):

WITH temp_ranges (temp_range, feeling, color) AS (VALUES ('(, 0]' :: numrange, 'freezing', 'blue'), ('(0,10)' :: numrange, 'cold', 'white '), ('[10,18)'::numrange, 'mild', 'yellow'),

    ('[18,30)'::numrange, 'warm', 'orange'),

    ('[30,36)'::numrange, 'hot', 'red'),

    ('[36,)'::numrange, 'scorching', 'black')

)

SELECT ws.station, tr.feeling, tr.colour

FROM weather_stations ws

INNER JOIN temp_ranges tr ON ws.temperature <@ tr.temp_range;

Если вы не знакомы с диапазонными типами, то вас могут смутить “numrange”. Это один из диапазонных типов, предназначенный для представления диапазона чисел. Круглые скобки означают исключение границы, квадратные — включение. Таким образом, ‘(0,10]’means “from 0, but not including 0, and up to and including 10”. The missing left border of the range means all values ​​are less than the specified number, and the missing right one – more than the specified number.

Custom configuration options

Postgres comes with a large set of parameters that allow you to customize all aspects of the database system, but you can also add your own parameters and name them whatever you like, as long as you specify a prefix.

For example, you can add the following parameter to postgresql.conf:

config.cluster_type="staging"

And then get its value using the SHOW command.

postgres=# SHOW config.cluster_type;

 config.cluster_type

---------------------

 staging

(1 row)

Please note that these options do not appear in the pg directorysettings and are not displayed by the SHOW ALL command.

So why do we have to specify the configuration prefix? Before PostgreSQL 9.2 there was a custom parametervariable_classes, which took a list of classes that could be used by extensions for their own parameters. You had to add an extension class to this list if you wanted to customize it via postgresql.conf. However, this requirement has been removed in later versions and you no longer need to declare them explicitly. Built-in parameters are not prefixed, so any custom parameters must be prefixed or they will not be accepted.

These parameters are useful for providing metadata about the cluster.

Comparison of boolean values ​​without “equals”

You probably wrote queries like this:

SELECT user, location, active

FROM subscriptions

WHERE active = true;

Did you know that you don’t need to write “= true”? It can be simplified:

WHERE active

This works because boolean values ​​do not need to be compared with another boolean value, since expressions return true or false anyway. Denial can be written like this:

WHERE NOT active

This also reads better.

Change column type at no extra cost

Often, when changing the type of a column in a data table, it is necessary to re-create the entire table. But in many cases this does not happen.

And we can find these types:

SELECT

  castsource::regtype::text,

  array_agg(casttarget::regtype ORDER BY casttarget::regtype::text) casttargets

FROM pg_cast

WHERE castmethod = 'b'

GROUP BY 1

ORDER BY 1;

This query will return a relatively small list of types with information about which “binary compatible” types they can be converted to. You will see from the results that the types text, xml, char and varchar are interchangeable. Therefore, if you have a table that contains XML data in a text column, feel free to transform it (note that if the XML is invalid, Postgres will return an error).

Information about the section the string is in

The table might be partitioned and you might want to know which section the row is in? It’s easy: just add tableoid :: regclass to the SELECT. For instance:

postgres=# SELECT tableoid::regclass, * FROM customers;

   tableoid   | id  |      name      |     country    | subscribed

--------------+-----+----------------+----------------+------------

 customers_de |  23 | Hilda Schumer  | Germany        | t

 customers_uk | 432 | Geoff Branshaw | United Kingdom | t

 customers_us | 815 | Brad Moony     | USA            | t

(3 rows)

Here tableoid is a hidden system column that just needs to be explicitly specified in the SELECT. It returns the OID (Object Identifier) ​​of the table to which the row belongs. If you cast it to regclass, you get the name of the table.

Tables are types

Yes, you heard that right. Every time you create a table, you are actually creating a new type as well. See:

CREATE TABLE books (isbn text, title text, rrp numeric(10,2));

We can use this table type when creating another table, as a function parameter, or as a return type:

CREATE TABLE personal_favourites (book books, movie movies, song songs);

Then the data can be inserted:

INSERT INTO personal_favourites (book)
  VALUES (('0756404746','The Name of the Wind',9.99));

To get individual columns from a table value, you can select a column from a column:

SELECT (book).isbn, (book).title, (book).rrp
FROM personal_favourites;

Now I know what you are thinking: a table that contains a table type that contains types is also a type? Yes, but let’s not go into those details, or we’ll end up in a confusing Inception-style situation.

And as I mentioned in Referencing the Entire String, you can convert the entire string to JSON:

postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites))
FROM personal_favourites;

             	jsonb_pretty            	 
----------------------------------------------
 {                                       	+
 	"book": {                           	+
     	"rrp": 9.99,                    	+
     	"isbn": "0756404746",           	+
     	"title": "The Name of the Wind" 	+
 	},                                  	+
 	"song": {                           	+
     	"album": "Grace",               	+
     	"title": "This is our Last Goodbye",+
     	"artist": "Jeff Buckley"        	+
 	},                                  	+
 	"movie": {                          	+
     	"title": "Magnolia",            	+
     	"studio": "New Line Cinema",    	+
     	"release_date": "2000-03-24"    	+
 	}                                   	+
 }

This functionality can be used to create schemas with JSON data to get NoSQL-like functionality, but with data that has a specific structure.

But wait, what if I want to store and retrieve all of my favorite books, songs and movies, not just one entry?

This works too. Any type, including tabular, can be turned into an array by adding [] after the data type name. Instead of re-creating the table, let’s just convert the columns to arrays and then add another book:

ALTER TABLE personal_favourites
  ALTER COLUMN book TYPE books[] USING ARRAY[book];

ALTER TABLE personal_favourites
  ALTER COLUMN movie TYPE movies[] USING ARRAY[movie];

ALTER TABLE personal_favourites
  ALTER COLUMN song TYPE songs[] USING ARRAY[song];

Add one more book:

UPDATE personal_favourites
  SET book = book || ('1408891468','Jonathan Strange and Mr Norrell',7.99)::books;

The result now looks like this:

postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites))
FROM personal_favourites;
                  	jsonb_pretty                 	 
--------------------------------------------------------
 {                                                 	+
 	"book": [                                     	+
     	{                                         	+
         	"rrp": 9.99,                          	+
         	"isbn": "0756404746",                 	+
         	"title": "The Name of the Wind"       	+
     	},                                        	+
     	{                                         	+
         	"rrp": 7.99,                          	+
         	"isbn": "1408891468",                 	+
         	"title": "Jonathan Strange and Mr Norrell"+
     	}                                         	+
 	],                                            	+
 	"song": [                                     	+
     	{                                         	+
         	"album": "Grace",                     	+
         	"title": "This is our Last Goodbye",  	+
         	"artist": "Jeff Buckley"              	+
     	}                                         	+
 	],                                            	+
 	"movie": [                                    	+
     	{                                         	+
         	"title": "Magnolia",                  	+
         	"studio": "New Line Cinema",          	+
         	"release_date": "2000-03-24"          	+
     	}                                         	+
 	]                                             	+
 }

Now the query displays an array of books and this is without any changes to it. I hope these tips help you use Postgres more efficiently! To find out more, check out our new eBook 5 Ways to Get More from PostgreSQL (Five ways to get more out of PostgreSQL).


Is it interesting to develop in this direction? Sign up for a free Demo lesson “Partitioning PostgreSQL Tables” and participate in online meeting with Evgeny Aristov – Head of the educational program “PostgreSQL” and courses “Databases”, “Software Architect”, “MS SQL Server Developer”, “Non-relational databases”.

Similar Posts

Leave a Reply Cancel reply