Edwin UY schrieb am 22.01.2025 um 00:28:
> I am just now finding out that I sometimes I have to enclosed tables
> in quotes, that is a name named table1 need to be quoted as 'table1'
> otherwise it gives error. Is there any way to find out what are
> those such tables so we can rename it to be without the quotes? Or
> maybe it is not a good idea to rename as it might break the
> indexes / foreign keys / sequences?
The function quote_ident() will return a quoted string if needed
(e.g. when using mixed case or other special characters).
To find all tables that would need double quotes (table1 is not one of them) you
can check if the name stored in the system catalog is the same as the quoted name:
select *
from pg_tables
where schemaname = 'public'
and tablename <> quote_ident(tablename);
If you only have tables that use mixed case, another option is
to check if the name is all lower case:
select *
from pg_tables
where schemaname = 'public'
and tablename <> lower(tablename);
The recommendation is indeed to avoid quoted identifiers.
Using the above query you could generate ALTER statements to rename those using
mixed case (and only that) to lower case.
Something along the lines:
select format('alter table %I.%I rename to %I;', schemaname, tablename, lower(tablename))
from pg_tables
where schemaname = 'public'
and tablename <> lower(tablename)
and lower(tablename) = quote_ident(lower(tablename))
