Chapter 5 Median Aggregator Code Incompatible with PostgreSQL14

[robml]

Having trouble with data, code, or exercises in Practical SQL? I'm glad to help. Please answer these questions, and I'll typically reply within one to two business days.

Please include the chapter number in your issue title. For example: "Chapter 13: Regular Expression Question"

General

Please answer all of these as they're essential for troubleshooting.

What's your operating system (e.g. Windows 10, macOS Catalina, Ubuntu, etc.)?
I am running Windows 10

Did you install PostgreSQL and pgAdmin according to the steps on page xxviii of the book's Introduction? If not, please describe your installation.
I installed PostgreSQL and pgAdmin exactly according to the steps in the book.

Which versions of PostgreSQL and pgAdmin 4 are you using? If you aren't sure, for PostgreSQL run the SQL command SELECT version(); and for pgAdmin 4, navigate to "About pgAdmin 4" under the "Help" menu.
I am using PostgreSQL14 and pgAdmin version 6.1.

Did you download the book's code examples and data from GitHub using the directions on page xxvii of the Introduction? If not, please describe how you loaded the material on your computer.
I did download the instructions, in particular for this issue.

Issue or Question

Chapter, page and code listing number:
The problem isn't with the author but one due to PostgreSQL 14 in general, namely the median aggregator from the wiki listed in Chapter 5, page 131/132 (the entire code block).

Describe the issue or your question:
When I run the code mentioned I get the following error message, and considering I am still a beginner in SQL don't know how to fix it, and have opted to continue using the percentile_cont(0.5) WITHIN GROUP (ORDER BY _column name_) function instead

Please paste the code and error message here. It's OK to alter identifying info such as a folder name
Here is the error message:

ERROR:  function array_append(anyarray, anyelement) does not exist
SQL state: 42883

[anthonydb]

Hi, @robml,

Thanks for flagging this. It looks like the definition of the array_append() function has changed in PostgreSQL 14, breaking the function definition. You can compare the differences by looking at the PostgreSQL docs for versions 13 and the latest:

https://www.postgresql.org/docs/13/functions-array.html
https://www.postgresql.org/docs/current/functions-array.html

I will note in the errata here that the median() function works with PostgreSQL versions 13 and lower only. In the upcoming 2nd Edition of the book, I removed this median() function in favor of focusing exclusively on percentile_cont(), which I recommend going forward.

[robml]

Good to know @anthonydb !
When is the 2nd edition due? Looking forward to the JSON chapters.

[anthonydb]

Probably around March for the physical book, but the PDF is available now from No Starch Press!