Postgresql array last element

by

Answer:

In PostgreSQL, you can use the array_upper and unnest functions to get the last element of an array.

Here is an example:

    SELECT unnest(your_array_column) AS last_element
    FROM your_table
    WHERE array_upper(your_array_column, 1) IS NOT NULL
    ORDER BY array_upper(your_array_column, 1) DESC
    LIMIT 1;
  • your_array_column: The name of the column that contains the array.
  • your_table: The name of the table that contains the array column.

This query uses the unnest function to transform the array into a set of rows, with one element in each row. Then, it selects the last element by ordering the rows by the array upper bound and retrieving only the first row using LIMIT 1.

Let’s consider an example table employees with a column phone_numbers which stores an array of phone numbers for each employee:

    CREATE TABLE employees (
      id SERIAL PRIMARY KEY,
      name VARCHAR(100),
      phone_numbers VARCHAR(100)[]
    );

    INSERT INTO employees (name, phone_numbers)
    VALUES ('John Doe', ARRAY['555-1234', '555-5678', '555-9012']);

Using the above query, we can retrieve the last phone number from the phone_numbers array for the employee:

    SELECT unnest(phone_numbers) AS last_phone_number
    FROM employees
    WHERE array_upper(phone_numbers, 1) IS NOT NULL
    ORDER BY array_upper(phone_numbers, 1) DESC
    LIMIT 1;

The result of this query will be:

    +-----------------+
    | last_phone_number |
    +-----------------+
    | 555-9012        |
    +-----------------+

As you can see, the last element of the phone_numbers array for the employee ‘John Doe’ is ‘555-9012’.

Leave a comment