postgresql 以声明方式从 Postgres 字符串中获取最后一个单词

Getting the last word from a Postgres string, declaratively

提问by Wojciech Kaczmarek

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

[编辑] 这个问题的原始标题是“以声明方式获取 Postgres 数组的最后一个元素”

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

如何在Postgres中获取数组的最后一个元素？

I need to do it declaratively 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.

我需要声明性地做它，因为我想将它用作 ORDER BY 标准。我不想为它创建一个特殊的 PGSQL 函数，在这种情况下对数据库的更改越少越好。

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_arrayor regexp_split_to_arrayfunctions, then how to get its last element?

换句话说，我想将 Ruby 推sort_by {|x| x.split[-1]}入数据库级别。我可以使用 Postgresstring_to_array或regexp_split_to_array函数将一个值拆分为单词数组，那么如何获取它的最后一个元素？

回答by Mark Byers

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

回答by Frank Heikens

Use array_upper():

使用array_upper()：

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

回答by Erwin Brandstetter

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.

在处理实际数组text（不是字符串）时，array_upper()在索引中使用。

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)];

回答by Yottabyte

You can do the following:

您可以执行以下操作：

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

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.

即获取索引，然后选择最后一个元素。

回答by alexkovelsky

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):

您还可以使用 array_upper() 返回元素本身（不仅仅是它的索引）：

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'

结果： 'three'

回答by leonbloy

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

编辑：这是错误的——正确答案见下文——

I guess you must use array_length():

我想你必须使用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:

要以声明方式使用它，（即时）您可以创建一个小的 SQL 函数：

CREATE FUNCTION last_elem (text[]) RETURNS text AS $$
 SELECT [array_length(,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.

以上是不正确的，因为在 Postgresql 数组有时可能不是基于一个的。

The correct way, then, is with array_upper()

那么，正确的方法是 array_upper()

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


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

回答by Nikolay Christov

You can combine string_to_array and array_length

您可以组合 string_to_array 和 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

这将使用“.”将 column_name 中的字符串拆分为数组。作为分隔符，并会给你最后一部分

回答by matiu

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

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)]

返回数组最后一个元素的索引。所以要使用它，你必须引用数组两次：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)