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

问题描述

我有一个表t1，如下所示:

I have a table t1 as below:

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

此表中有五个记录:

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'

现在，我想使用交叉表函数提取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?

推荐答案

为避免对如何转义单引号和通常简化语法的任何混淆，请使用

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 Query
• Insert text with single quotes in PostgreSQL

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