使用 regexp_split_to_table 时出错 (Amazon Redshift) [英] Error while using regexp_split_to_table (Amazon Redshift)

问题描述

我有同样的问题:
拆分一个Postgresql 中的逗号分隔字段并对所有结果表执行 UNION ALL
只是我的水果"列由|"分隔.当我尝试时:

SELECT你的Table.ID，regexp_split_to_table(yourTable.fruits, E'|') AS split_fruits从你的表

我得到以下信息:

<块引用>

错误:类型e"不存在

第一季度.E 有什么作用?我看到了一些没有使用 E 的例子.官方文档没有在他们的quick brown fox..."示例中解释它.

第 2 季度.我如何使用|"作为我的查询的分隔符?

我使用的是 PostgreSQL 8.0.2.不支持 unnest() 和 regexp_split_to_table() .

解决方案

A1

E 是 Posix 样式转义字符串的前缀.在现代 Postgres 中你通常不需要这个.仅当您想解释字符串中的特殊字符时才添加它.像 E' ' 表示换行符.详细信息和文档链接:

• 在 PostgreSQL 中插入带单引号的文本
• SQL 选择列以 开头的位置

E 在您的查询中是毫无意义的噪音，但它应该仍然有效.答案你链接到 恐怕不是很好.

A2

应该可以正常工作.但最好没有 E.

SELECT id, regexp_split_to_table(fruits, '|') AS split_fruits从 tbl;

对于简单的分隔符，您不需要昂贵的正则表达式.这通常更快:

SELECT id, unnest(string_to_array(fruits, '|')) AS split_fruits从 tbl;

在 Postgres 9.3+ 中，您宁愿使用 LATERAL 连接来设置返回函数:

SELECT t.id, f.split_fruits从 tbl tLEFT JOIN LATERAL unnest(string_to_array(fruits, '|')) AS f(split_fruits)真；

详情:

• 什么是PostgreSQL 中 LATERAL 和子查询的区别?
• 带有元素编号的 PostgreSQL unnest()

Amazon Redshift 不是 Postgres

它仅实现了一组简化的功能，如 记录在其手册中.特别是没有表函数，包括基本函数unnest()、generate_series()或regexp_split_to_table()code> 使用其计算节点"(访问任何表)时.

您应该使用标准化的餐桌布局(每行一个水果的额外餐桌).

或者这里有一些选项可以在 Redshift 中创建一组的行:

• 如何选择在 Amazon Redshift 中填充了多行常量?

这个解决方法应该可以:

1. 创建一个数字表，行数至少与列中的水果数量相同.临时或永久，如果你会继续使用它.假设我们永远不会超过 9 个:

   创建临时表 nr9(i int);插入 nr9(i) 值 (1)、(2)、(3)、(4)、(5)、(6)、(7)、(8)、(9)；

2. 加入数字表并使用split_part()，即实际在 Redshift 中实现:

   SELECT *, split_part(t.fruits, '|', n.i) 作为水果从 nr9 nJOIN tbl t ON split_part(t.fruits, '|', n.i) <>''

瞧.

I have the same question as this:
Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables
Just that my 'fruits' column is delimited by '|'. When I try:

SELECT 
    yourTable.ID, 
    regexp_split_to_table(yourTable.fruits, E'|') AS split_fruits
FROM yourTable

I get the following:

ERROR: type "e" does not exist

Q1. What does the E do? I saw some examples where E is not used. The official docs don't explain it in their "quick brown fox..." example.

Q2. How do I use '|' as the delimiter for my query?

Edit: I am using PostgreSQL 8.0.2. unnest() and regexp_split_to_table() both are not supported.

解决方案

A1

E is a prefix for Posix-style escape strings. You don't normally need this in modern Postgres. Only prepend it if you want to interpret special characters in the string. Like E' ' for a newline char.Details and links to documentation:

• Insert text with single quotes in PostgreSQL
• SQL select where column begins with

E is pointless noise in your query, but it should still work. The answer you are linking to is not very good, I am afraid.

A2

Should work as is. But better without the E.

SELECT id, regexp_split_to_table(fruits, '|') AS split_fruits
FROM   tbl;

For simple delimiters, you don't need expensive regular expressions. This is typically faster:

SELECT id, unnest(string_to_array(fruits, '|')) AS split_fruits
FROM   tbl;

In Postgres 9.3+ you'd rather use a LATERAL join for set-returning functions:

SELECT t.id, f.split_fruits
FROM   tbl t
LEFT   JOIN LATERAL unnest(string_to_array(fruits, '|')) AS f(split_fruits)
                                                                   ON true;

Details:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• PostgreSQL unnest() with element number

Amazon Redshift is not Postgres

It only implements a reduced set of features as documented in its manual. In particular, there are no table functions, including the essential functions unnest(), generate_series() or regexp_split_to_table() when working with its "compute nodes" (accessing any tables).

You should go with a normalized table layout to begin with (extra table with one fruit per row).

Or here are some options to create a set of rows in Redshift:

• How to select multiple rows filled with constants in Amazon Redshift?

This workaround should do it:

1. Create a table of numbers, with at least as many rows as there can be fruits in your column. Temporary or permanent if you'll keep using it. Say we never have more than 9:

   CREATE TEMP TABLE nr9(i int);
   INSERT INTO nr9(i) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
   

2. Join to the number table and use split_part(), which is actually implemented in Redshift:

   SELECT *, split_part(t.fruits, '|', n.i) As fruit
   FROM   nr9 n
   JOIN   tbl t ON split_part(t.fruits, '|', n.i) <> ''
   

Voilá.

这篇关于使用 regexp_split_to_table 时出错 (Amazon Redshift)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！