Per version 12 release notes:
"Treat object-name columns in the information_schema views as being of
type name, not varchar (Tom Lane)
Per the SQL standard, object-name columns in the information_schema
views are declared as being of domain type sql_identifier. In
PostgreSQL, the underlying catalog columns are really of type name. This
change makes sql_identifier be a domain over name, rather than varchar
as before. ..."
This came up in this SO question:
https://stackoverflow.com/questions/65416748/postgres-12-4-gives-function-does-not-exists-error
Where the query is:
SELECT (TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name,
pg_size_pretty(pg_table_size(table_name)) as table_size,
pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size,
pg_size_pretty(pg_total_relation_size(table_name)) as total_size
from information_schema.TABLES nowait
where TABLE_SCHEMA='myschema'
order by pg_total_relation_size(table_name) desc;
And the error is:
"ERROR: function pg_table_size(information_schema.sql_identifier) does
not exist
LINE 1: ..."."' || TABLE_NAME) as table_name, pg_size_pretty(pg_table_s..."
My attempts:
SELECT pg_table_size(table_name) from information_schema.tables;
ERROR: function pg_table_size(information_schema.sql_identifier) does
not exist
LINE 1: SELECT pg_table_size(table_name) from information_schema.ta...
SELECT pg_table_size(table_name::text) from information_schema.tables;
ERROR: invalid name syntax
SELECT pg_table_size(table_name::regclass) from information_schema.tables;
ERROR: invalid name syntax
SELECT table_name::text::regclass from information_schema.tables;
ERROR: invalid name syntax
So how does one go about using a table name from
information_schema.tables in pg_table_size()?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
