You should do your INSERTs into the base table only
That way you can INSERT queries like these
insert into base_table (a, b, c, x) VALUES (null, 'xxx', 'xxx',1);
insert into base_table (b, c, x) VALUES ('xxx', 'xxx',2);
If you do INSERT queries like these
insert into base_table (a, b, c) VALUES (null, 'xxx', 'xxx');
insert into base_table (b, c) VALUES ('xxx', 'xxx');
then you should assign a default value of 1 for x
in the table definition
create table base_table (
a int unsigned auto_increment primary key,
b char(10) default null,
c char(10) default null,
x tinyint unsigned default 1
);
Otherwise, you should only INSERT into base_table
since there exists no mechanism for dynamically switching the default value of x
within the definition of a view. The x
column has no ability to be selected in the view (because you left it out of the SELECT clause). Consequently, it would definitely have no ability to be updated or assigned in the view.
Give it a Try !!!
Proper crosstab query
First of all, the presented query would not work while you still have char(n)
in your table definition - like we discussed under your previous question. You would need to convert to text
or varchar
first.
This works with your current table definition:
SELECT * FROM crosstab(
'SELECT postcode_nospace_::text, ageband::text, count(ageband) AS ct
FROM adult_social_care.activities_in_localities_asc
GROUP BY 1, 2
ORDER BY 1'
,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
, ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$
)
AS t("postcode" text
, "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
, "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint);
I am also using bigint
instead of numeric
to save another unnecessary type conversion (count()
returns bigint
).
Detailed explanation:
Better table definition
But better ALTER
the data type in your table to text
like @dezso suggested. Or, if you want to keep the length limit, add a CHECK
constraint or use varchar(n)
. Never use char(n)
.
ALTER TABLE activities_in_localities_asc
ALTER COLUMN ageband TYPE varchar(12)
, ALTER COLUMN postcode_nospace_ TYPE varchar(8);
ALTER TABLE gazetteers_and_addresses.unit_postcode
ALTER COLUMN pc_area TYPE varchar(10);
Then you don't need to cast original column values any more:
SELECT * FROM crosstab(
'SELECT postcode_nospace_, ageband, count(ageband)
...
Ideally, though, ageband
should be an enum
or (my preference) an ID referencing a lookup table, not plain, error-prone text
... key word: normalization.
Add total count
Building on your table, but with text
or varchar()
columns.
Wrap your original query into a CTE and call the the result twice in a UNION
query - the 2nd call adds totals per postcode:
SELECT * FROM crosstab(
$$WITH cte AS (
SELECT postcode_nospace_, ageband, count(ageband) AS ct
FROM adult_social_care.activities_in_localities_asc
GROUP BY 1, 2
)
TABLE cte -- original results
UNION ALL -- add total per postcode
SELECT postcode_nospace_, 'total' AS ageband, sum(ct) AS ct
FROM cte
GROUP BY 1
ORDER BY 1$$ -- dollar-quotes to include single quotes easily
,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
, ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER'), ('total')$$
)
AS t("postcode" text
, "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
, "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint, "total" bigint);
About the TABLE
command:
Related CTE examples:
Best Answer
For the simple case you present, build an array from your sorted values and unnest one by one in the
SELECT
listObviously, this is not dynamic, but only works for given fields. You did not actually ask for "dynamic" - except that you added the tag dynamic-sql.
For more complex operations use
crosstab()
from the additional module tablefunc. Detailed instructions:Still not "dynamic". A completely dynamic function is hardly possible since SQL demands to know the return type at the time of the function call. If the return type is dynamic, you need two steps:
But there may be something for you here: