An introduction to PostgreSQL
JSON and JSONB
JavaScript Object Notation (JSON) and PostgreSQL's JSONB, a special binary representation of JSON, are popular for storing and manipulating unstructured data. While JSON stores data in text format, JSONB stores it in a decomposed binary format, making it slower to insert but faster to query. For example, after creating a table with a JSON/JSONB data type:
CREATE TABLE users ( id serial PRIMARY KEY, profile JSONB );
You can query it using the following syntax:
INSERT INTO users (profile) VALUES ('{"name": "John", "age": 30,"interests": ["cycling", "hiking"]}');
Because JSONB is quite popular, I'll discuss it in more detail in a practical example later in this article. For now suffice it to say that while JSON preserves the exact input format (such as whitespace and the order of keys) and is generally faster to store because of minimal processing, JSONB gives you several advantages in terms of efficient indexing and fast data access as well as a wider array of operators and functions for manipulating JSON data, making JSONB preferable when you need to frequently update or query JSON elements.
Arrays
Arrays in PostgreSQL are used to store multiple values in a single column. They can be particularly useful in various scenarios but come with their own set of considerations. For example, it might be tempting to simplify the model when you have a one-to-many relationship where the "many" side contains a small, fixed number of related items. Using an array in such a scenario avoids the need for additional join tables. Arrays can also improve efficiency when you frequently need to retrieve all related items simultaneously without performing a join operation. This can be especially beneficial when the array elements are often accessed together. Moreover, arrays can store not only simple types such as integers and strings but also composite types, allowing for the representation of complex and nested structures within a single column.
However, queries with arrays can quickly become complex and less intuitive. Also, updating individual elements of an array is more complex than updating values in a normalized table. While indexes can be created on array columns, they may not always be as effective as indexes on regular columns, especially for complex queries involving array elements. Therefore you should consider these trade-offs carefully.
A table with an array data type can be created as follows:
CREATE TABLE posts ( id serial PRIMARY KEY, tags text[] );
The values themselves are inserted using the following syntax:
INSERT INTO posts (tags) VALUES (ARRAY['science', 'technology','education']);
Spatial Data Types
When you deal with maps, suddenly a whole class of questions becomes relevant, starting from the simple (e.g., "What is the distance between these two places?") to the more complex (e.g., "What points of interest can be found in a given area?"). PostgreSQL contains a collection of spatial data types to handle these issues. While this topic deserves a separate article, I'll cover the basics here.
To store spatial data, you must create tables with spatial columns, for example:
CREATE TABLE spatial_data ( id SERIAL PRIMARY KEY, location GEOMETRY(POINT, 4326) );
This SQL statement creates a table with a POINT
type geometry column, specifying spatial reference identifier (SRID) 4326, commonly used for geographic coordinate systems. To insert a point into the location
column using the ST_GeomFromText
function, which converts text to a spatial data type, type:
INSERT INTO spatial_data (location) VALUES (ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));
Apart from the ST_GeomFromText, PostgreSQL offers other functions such as ST_Area
to calculate the area of a polygon and ST_Distance
to compute the shortest distance between two geometries. PostgreSQL also supports many useful geospatial data types such as MULTIPOLYGON
, which can represent disjointed territories of a single entity, like islands that are part of a country along with a mainland.
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Gnome 47.1 Released with a Few Fixes
The latest release of the Gnome desktop is all about fixing a few nagging issues and not about bringing new features into the mix.
-
System76 Unveils an Ampere-Powered Thelio Desktop
If you're looking for a new desktop system for developing autonomous driving and software-defined vehicle solutions. System76 has you covered.
-
VirtualBox 7.1.4 Includes Initial Support for Linux kernel 6.12
The latest version of VirtualBox has arrived and it not only adds initial support for kernel 6.12 but another feature that will make using the virtual machine tool much easier.
-
New Slimbook EVO with Raw AMD Ryzen Power
If you're looking for serious power in a 14" ultrabook that is powered by Linux, Slimbook has just the thing for you.
-
The Gnome Foundation Struggling to Stay Afloat
The foundation behind the Gnome desktop environment is having to go through some serious belt-tightening due to continued financial problems.
-
Thousands of Linux Servers Infected with Stealth Malware Since 2021
Perfctl is capable of remaining undetected, which makes it dangerous and hard to mitigate.
-
Halcyon Creates Anti-Ransomware Protection for Linux
As more Linux systems are targeted by ransomware, Halcyon is stepping up its protection.
-
Valve and Arch Linux Announce Collaboration
Valve and Arch have come together for two projects that will have a serious impact on the Linux distribution.
-
Hacker Successfully Runs Linux on a CPU from the Early ‘70s
From the office of "Look what I can do," Dmitry Grinberg was able to get Linux running on a processor that was created in 1971.
-
OSI and LPI Form Strategic Alliance
With a goal of strengthening Linux and open source communities, this new alliance aims to nurture the growth of more highly skilled professionals.