Working with Data Arrays in PostgreSQL

2hAdvanced2024-03-15

Authors

Adam Wilbert

Data Visualization Expert

Course details

Data arrays are an important storage construct in programming, and PostgreSQL is one of the few relational database platforms that has built-in support for arrays. Join database expert Adam Wilbert for this deep dive course into PostgreSQL’s support for the array data format. Learn about data arrays in PostgreSQL. Go over examples of when array data is useful and examine the tools available for incorporating data arrays into your PostgreSQL databases. Explore useful built-in functions, and finish the course with a culmination challenge and solution so that you can practice what you’ve learned.

Skills covered

PostgreSQLData EngineeringDatabase DevelopmentDatabase ManagementData ScienceOpen SourceSoftware DevelopmentOne-Off

Concepts

Introduction

Leverage array data in PostgreSQL
What you should know

Getting Started with Arrays

What are data arrays
Create a table to store array elements
The ARRAY constructor and INSERT syntax
Challenge - Store array data in PostgreSQL
Solution - Store array data in PostgreSQL

Querying Array Columns

Select a single array element
Select a range of values with array slices
Slicing multidimensional arrays
Locate values with the ANY() function
Expand arrays with UNNEST() and GENERATE SUBSCRIPT()
Challenge - Query array data
Solution - Query array data

Modify the Data Stored in an Array

Updating array values
Prepending or appending arrays
Concatenating arrays
Replace and remove elements

Useful Array Functions

Determine the position of a value in an array
Comparing two arrays with operators
Output array values to a string
Work with randomized array elements
Collect single values into an array
Challenge - Applying array functions
Solution - Applying array functions

Analysis Project - River Height Monitoring

Set up the river height monitoring table
Counting elements
Isolate elements
Compare values
Select a range of elements

Conclusion

Next steps