Обсуждение: Is there a way to check if objects are created with quotes or not?

Edwin UY <edwin.uy@gmail.com> writes:
> 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.

"table1" shouldn't need quotes.  The rules for that are here:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

            regards, tom lane

On Tue, Jan 21, 2025 at 3:28 PM Edwin UY <edwin.uy@gmail.com> wrote:
>
> 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.

I'm guessing you mean quoting with double quotes, like "table1". Object names,
a.k.a identifiers, may be quoted with double quotes; single quotes are used for
text and other literals/constants.

The link shared by Tom [1] has the complete set of rules around quoting, but in
short, the general rule is that if an identifier is not qupted by the
user/application, it will be converted to lower-case. If the identifier is
quoted by the user/application, then Postgres will retain the case used in the
name.

For example, first two of the following queries are equivalent, whereas the
third one refers to a different object than the first two.

select * from table1;
select * from "table1";
select * from "Table1";

> 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?

Renaming an object does _not_ break any dependencies like indexes, etc. So it's
safe to rename an object. But this may break your application queries, but it
appears that you're willing to accept that and modify the application queries.

Here's an quick and dirty example that shows how to find table names with
upper-case characters; converting them to lower case should help you. This may
not be sufficient, so be sure to read through the documnetation shared by Tom to
take care of unusual cases.

postgres=# create table table1();
CREATE TABLE
postgres=# create table "Table1"();
CREATE TABLE
postgres=# select relname from pg_class where relname <> lower(relname);
 relname
---------
 Table1
(1 row)

[1]: Identifiers and Key Words
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Best regards,
Gurjeet
http://Gurje.et

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))