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 "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
– Adrian Klaver, Commented Dec 22, 2020 at 23:53

Adrian Klaver · Accepted Answer · 2020-12-23 01:28:00Z

3

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.

answered Dec 23, 2020 at 1:28

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres 12.4 gives function does not exists error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related