Postgres 12.4 gives function does not exists error

Question

I am running the below query in postgres 12.4 :

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;

I am getting the below error message :

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...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Can anyone tell me what exactly am I missing here? I would also like to mention that the exact same query runs fine in postgres version 9.5. I couldn't find anything in the postgres 12.4 documentation as well. Any thoughts/suggestions are welcomed. Thanks!

Well the change can be found here [12 Release Notes](https://www.postgresql.org/docs/12/release-12.html) "Treat object-name columns in the information_schema views as being of type name, not varchar (Tom Lane)". Not sure yet how to deal with that yet. — Adrian Klaver, Dec 22 '20 at 23:53

score 2 · Accepted Answer · answered Dec 23 '20 at 01:28

Per this thread on the Postgres --general list:

SELECT
    pg_size_pretty(pg_table_size(quote_ident(table_name))),
    pg_size_pretty(pg_indexes_size(quote_ident(table_name))) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size
FROM
    information_schema.tables
WHERE
    table_schema = 'myschema'
;

Your previous query was never working with this:

(TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name

it was just using the value in the table_name column which previously was a varchar so it worked in pg_table_size(). Now that the column type has changed you need to use quote_ident() to correctly cast it. FYI, the above will work in 9.5 also.

Postgres 12.4 gives function does not exists error

1 Answers1

Linked