Invalid quote label when using crosstab () in PostgreSQL

Question

I have a table t1 as shown below:

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

There are five entries in this table:

 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' 

Now I want to use the crosstab function to retrieve NAME , GENDER , so I write 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) 

My problem is that the SQL in crosstab() contains the item_name , which will lead to incorrect quotes. How to solve the problem?

Answer 1

To avoid confusion about how to avoid single quotes and generally simplify the syntax, use a dollar quote 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) 

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

In practice, the SQL query should always indicate ORDER BY 1,2 to ensure that the input lines are correctly ordered, that is, the values ​​with the same row_name_name are collected and correctly ordered within the row. Note that the crosstab itself does not pay attention to the second column of the query result; you just need to order it to control the order in which the values ​​of the third column are displayed on the page.

More details:

• PostgreSQL crosstab query
• Insert single quote text in PostgreSQL

Answer 2

Double single quotes to avoid them:

 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)