I have a rather complicated issue for myself which I hope someone can help me with.

I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table.

To keep it simple I will just show the table structure i want to use for the pivot and then use the methods given to create others, I hope :)

The fields I want to use are postcode_nospace_, and ageband.

ageband will be the columns across the top with postcode_nospace_ being the rows with a count of each postcode being the data.

There are 2132 records and of which some have more than one postcode, hence the count.

CREATE TABLE adult_social_care.activities_in_localities_asc
  ogc_fid integer NOT NULL,
  sort numeric(5,0),
  ageband character(12),
  postcode_nospace_ character(8),
  wkb_geometry geometry,
  CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)


Here is the outcome I want to achieve as shown in an excel pivot table with the same data.

postcode 18_24  25_34   35_44   45_54   55_64   65_74   Total Count
BB115DE     1           2                   2      3       8
FY38LZ                              1       1              2

From looking around I have compiled the following SQL query. It groups by postcode and creates the field names required. However the fields are blank. I would ideally also want a total_count column at the end of the table.

SELECT * FROM crosstab(
    'SELECT postcode_nospace_, ageband, count(ageband) as total_count
     FROM adult_social_care.activities_in_localities_asc
     GROUP BY postcode_nospace_, ageband
     ORDER BY postcode_nospace_'

     ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric);
  • What are the benefits of crosstab in 9.1 and upwards? We are currently considering upgrading to a later version. Commented Sep 8, 2015 at 14:02
  • 1
    The tablefunc module has not changed much but Postgres 9.0 is just too old. It is reaching EOL right now. Follow the link. Commented Sep 8, 2015 at 14:06
  • Thanks, just looked into it and will be upgrading to 9.4.4. as soon as time permits. Commented Sep 8, 2015 at 14:13

1 Answer 1


Your table definition says

ageband character(12),

which means the values in there look like '18-24 ' instead of '18-24'. This way, the items from the VALUES list do not match the values in the table, therefore you get an empty table as result.

If you change the column type to a bit more senseful text (which trims the values, too, as Erwin points it out):

ALTER TABLE activities_in_localities_asc ALTER COLUMN ageband TYPE text;

you will get your desired results.

  • Yes this worked perfectly. If say I changed the column type to character varying would this of worked without the trim? I want to now add a column at the end called 'total_count' which would contain a sum of the columns to create a new column. I have tried many variations to try and get this to work to no avail. More importantly than this though, I want to create this table spatially by using an 'INNER JOIN' or something like that to enable me to view the data more intelligently. I know that I could create the table and then take do a separate SQL query to do this, but strive for perfection. Commented Sep 8, 2015 at 14:00
  • A cast from char(n) to text or varchar does not conserve blanks, so the UPDATE (with trim()) is not necessary. Commented Sep 8, 2015 at 14:03
  • and in relation to the other part, can this be done? Commented Sep 8, 2015 at 14:06
  • 3
    @danielfranklin: Please start a new question for the new question. Comments are not the place. You can always link to this one for context. Commented Sep 8, 2015 at 14:08
  • 1
    @dezso: It's probably best, not to use char(n). Ever. Commented Sep 8, 2015 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.