Working with Data Arrays in PostgreSQL

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.

Concepts

0. Introduction

01 - Leverage array data in PostgreSQL
02 - What you should know

1. Getting Started with Arrays

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

2. Querying Array Columns

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

3. Modify the Data Stored in an Array

15 - Updating array values
16 - Prepending or appending arrays
17 - Concatenating arrays
18 - Replace and remove elements

4. Useful Array Functions

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

5. Analysis Project - River Height Monitoring

26 - Set up the river height monitoring table
27 - Counting elements
28 - Isolate elements
29 - Compare values
30 - Select a range of elements

Conclusion

31 - Next steps