在 PostgreSQL 中使用 crosstab() 时引号不正确 [英] Quotation mark incorrect when using crosstab() in PostgreSQL

问题描述

我有一张表t1如下:

create table t1 (
  person_id int,
  item_name varchar(30),
  item_value varchar(100)
);

这个表有5条记录:

person_id | item_name | item_value
   1        'NAME'      'john'
   1        'GENDER'    'M'
   1        'DOB'       '1970/02/01'
   1        'M_PHONE'   '1234567890'
   1        'ADDRESS'   'Some Addresses unknown'

现在我想用crosstab函数来提取NAME、GENDER数据，所以我写了一个SQL为:

Now I want to use crosstab function to extract NAME, GENDER data, so I write a SQL as:

select * from crosstab(
  'select person_id, item_name, item_value from t1 
   where person_id=1 and item_name in ('NAME', 'GENDER') ') 
as virtual_table (person_id int, NAME varchar, GENDER varchar)

我的问题是，如您所见，crosstab() 中的SQL 包含item_name 的条件，这会导致引号不正确.我该如何解决这个问题?

My problem is, as you see the SQL in crosstab() contains condition of item_name, which will cause the quotation marks to be incorrect. How do I solve the problem?

推荐答案

为避免对如何转义单引号造成任何混淆并通常简化语法，请使用 dollar-quoting 用于查询字符串:

To avoid any confusion about how to escape single quotes and generally simplify the syntax, use dollar-quoting for the query string:

SELECT *
FROM   crosstab($$
    SELECT person_id, item_name, item_value
    FROM   t1 
    WHERE  person_id = 1
    AND    item_name IN ('NAME', 'GENDER')
    $$) AS virtual_table (person_id int, name varchar, gender varchar)

您应该将 ORDER BY 添加到您的查询字符串中.我引用 tablefunc 模块的手册:

And you should add ORDER BY to your query string. I quote the manual for the tablefunc module:

在实践中，SQL 查询应始终指定 ORDER BY 1,2 以确保输入行是正确排序的，也就是说，具有将相同的 row_name 放在一起并在排.请注意，交叉表本身并不关注查询结果的第二列；它只是在那里订购，控制第三列值在页面中出现的顺序.

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.

更多细节:

• PostgreSQL 交叉表查询
• 在 PostgreSQL 中插入带单引号的文本

这篇关于在 PostgreSQL 中使用 crosstab() 时引号不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！