postgresql Cross Tab syntax error: No function matches the given name and argument types. You might need to add explicit type casts

Question

I am trying to create a cross tab, but it is giving me an error: No function matches the given name and argument types. You might need to add explicit type casts.

Here is my query for creating a cross tab:

CREATE TABLE regions_ct(region_id SERIAL, region VARCHAR (255));
INSERT INTO regions_ct(region_id, region) VALUES(1,'Asia');
INSERT INTO regions_ct(region_id, region) VALUES(2,'Australia and Oceania');
INSERT INTO regions_ct(region_id, region) VALUES(3,'Central America and the Caribbean');
INSERT INTO regions_ct(region_id, region) VALUES(4,'North America');
INSERT INTO regions_ct(region_id, region) VALUES(5,'Sub-Saharan Africa');
INSERT INTO regions_ct(region_id, region) VALUES(6,'Middle East and North Africa');
INSERT INTO regions_ct(region_id, region) VALUES(7,'Europe');

SELECT *
FROM crosstab(
  'select region_id, region
   from regions_ct'
   )
AS regions_ct(region_id SERIAL, region VARCHAR (255));

Answer 1

First, you seem to have not installed the tablefunc extension. Make sure your -contrib package is installed (i.e., yum install postgresql12-contrib), and then install the tablefunc extension so that crosstab is available: CREATE EXTENSION tablefunc.

Next, it seems that your table definition doesn't lend itself to pivoting. crosstab requires at least 3 columns of which you can collate into a rowid, category, and value(s). One way to deal with this is by adding an imaginary category (the second region_id):

db=# SELECT *
       FROM crosstab( 'select region, region_id, region_id from regions_ct' )
         AS regions_ct( region varchar(255), category1 int );
              region               | category1 
-----------------------------------+-----------
 Asia                              |         1
 Australia and Oceania             |         2
 Central America and the Caribbean |         3
 North America                     |         4
 Sub-Saharan Africa                |         5
 Middle East and North Africa      |         6
 Europe                            |         7
(7 rows)

I'm not sure what kind of output you're looking for, but I can try to help if you update your question with your expected output.