本文介绍了无效长度:字段位置必须大于零的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试转换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))
这篇关于无效长度:字段位置必须大于零的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!