无效长度：字段位置必须大于零 [英] Invalid Length: field position must be greater than zero

问题描述

我正在尝试转换PostgresSQL中的列：

I am trying to transform a column in PostgresSQL:

col_A中有一些值：

Here are some values in col_A:

col_A
---------
John_arrived
Mary_Brown_arrived
J_C_Jr_arrived
Q_arrived

目标是仅获取col_A的第一部分和最后一部分：

The aim is to get the first part and last part of col_A only:

d_col_A
-------
John_arrived
Mary_arrived
J_arrived
Q_arrived

这是我的查询：

with t1 as (
select split_part(col_A, '_'::text, 1) || '_' 
    || COALESCE (split_part(col_A, '_'::text, -1), '' ) as d_col_A
    from my_table
)

select distinct d_col_A from t1

然后我遇到以下错误：

Invalid Length
  Detail: 
  -----------------------------------------------
  error:  Invalid Length
  code:      8001
  context:   field position must be greater than zero
  query:     2382655
  location:  funcs_string.cpp:1565
  process:   query0_27 [pid=11502]
  -----------------------------------------------

知道我做错了什么吗？谢谢！

Any idea what I did wrong? Thanks!

推荐答案

如果您希望列的最后一部分，则不能使用-1。您需要反转该列并获取第一部分，然后再次反转：

If you want the last part of column, you can't use -1. You need to reverse the column and get the first part and reverse again:

reverse(split_part(reverse(col_A), '_'::text, 1))

这篇关于无效长度：字段位置必须大于零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！