[EDIT] original title of this question was "Getting the last element of a Postgres array, declaratively"

How to obtain the last element of the array in Postgres?

I need to do it declaratively开发者_如何学JAVA as I want to use it as a ORDER BY criteria. I wouldn't want to create a special PGSQL function for it, the less changes to the database the better in this case.

In fact, what I want to do is to sort by the last word of a specific column containing multiple words. Changing the model is not an option here.

In other words, I want to push Ruby's sort_by {|x| x.split[-1]} into the database level. I can split a value into array of words with Postgres string_to_array or regexp_split_to_array functions, then how to get its last element?

If I understand your question correctly you have a string and you're first splitting it on some separator and then afterwards finding the last element of the array and discarding the rest.

You could miss out the middle man and get the last element directly:

SELECT regexp_replace('foo bar baz', '^.* ', '')

Result:

baz

Use array_upper():

SELECT array_upper(ARRAY[1,2,5,6], 1);

Q: what I want to do is to sort by the last word of a specific column

When dealing with an actual array of text (not a string), use array_upper() in the index.

Demo for 1-dimensional array

WITH x(a) AS (
    VALUES
       ('{zoo, zar, zaz}'::text[])
      ,('{3,4,5,6}')
      ,('{foo, bar, baz}')
    )
SELECT *
FROM   x
ORDER  BY a[array_upper(a, 1)];

Demo for 2-dimensional array

WITH x(a) AS (
    VALUES
       ('{{zoo, zar, zaz}
         ,{4,5,6}
         ,{14,15,16}
         ,{foo, bar, zzzaz}}'::text[])
      ,('{{zoo, zar, baz}
         ,{4,5,6}
         ,{14,15,16}
         ,{foo, bar, aaaaz}}'::text[])
    )
SELECT *
FROM   x
ORDER  BY a[array_upper(a, 1)][array_upper(a, 2)];

Getting the last word from a Postgres string :

In postgresql 14 you can do:

SELECT split_part('this is my word', ' ', -1);

to get the last word from a string which is word in the example

This is new to psql14 since now split_part accepts negative indexes

See changelog for details

You can do the following:

SELECT (ARRAY[1,8,3,7])[array_upper(ARRAY[1,8,3,7], 1)];

I.e. get the index and then select that last element.

UPDATE: the question was edited, so I update my answer.

You can also use array_upper() to return the element itself (not just its index):

SELECT arr[array_upper(arr, 1)] FROM (SELECT ARRAY[1,2,3,6] AS arr) AS t

So the anwer is:

SELECT arr[array_upper(arr, 1)] FROM (SELECT string_to_array('one two three', ' ') AS arr) AS t

result: 'three'

Edited: THIS IS WRONG -- SEE BELOW FOR CORRECT ANSWER --

I guess you must use array_length() :

SELECT string_to_array('hi guys, welcome', ' ') AS arr INTO temparr;
SELECT * FROM temparr;
         arr
----------------------
 {hi,"guys,",welcome}

SELECT arr[array_length(arr,1)] FROM temparr;
   arr
---------
 welcome

To use this declaratively, (on the fly) you can create a little SQL function:

CREATE FUNCTION last_elem (text[]) RETURNS text AS $$
 SELECT $1[array_length($1,1)];
$$ LANGUAGE SQL;


 select last_elem(string_to_array('hi guys, welcome', ' '));
 last_elem
-----------
 welcome

------- EDITED -- CORRECT ANSWER FOLLOWS ----------------------

The above is not correct because in Postgresql arrays can sometimes be not one-based.

The correct way, then, is with array_upper()

CREATE FUNCTION last_elem (text[]) RETURNS text AS $$
 SELECT $1[array_upper($1,1)];
$$ LANGUAGE SQL;


 select last_elem(string_to_array('hi guys, welcome', ' '));
 last_elem
-----------
 welcome

You can combine string_to_array and array_length

select 
(string_to_array(column_name, '.'))[array_length((string_to_array(column_name, '.')), 1)]
from table_name;

This will split the string in column_name into array using "." as delimiter and will give you the last part

This is a more generic answer to 'how to get the last element of an array'.

trader=# create table temp (name varchar);
CREATE TABLE

trader=# insert into temp (name) values ('foo bar baz');
INSERT 0 1

trader=# select (regexp_split_to_array(name, ' ')) from temp;
 regexp_split_to_array 
-----------------------
 {foo,bar,baz}
(1 row)

trader=# select (regexp_split_to_array(name, ' '))[array_upper(regexp_split_to_array(name, ' '), 1)] from temp;
 regexp_split_to_array 
-----------------------
 baz
(1 row)

array_upper

Returns the index of the last element of an array. So to use it, you have to reference the array twice: some_array[array_upper(some_array, 1)]

So if you already have your array:

trader=# create view temp2 as  (select regexp_split_to_array(name, ' ') as name_parts from temp);
CREATE VIEW

trader=# select * from temp2;
  name_parts   
---------------
 {foo,bar,baz}
(1 row)

It's less verbose to select the last element:

trader=# select name_parts[array_upper(name_parts, 1)] from temp2;
 name_parts 
------------
 baz
(1 row)