Subject: \dt shows table but \d <table> says the table doesn't exist ?

From: David Gauthier <[email-masked]>
Date: Fri, 3 May 2024 16:58:26 -0400

psql (15.3, server 14.5) on linux

Someone else's DB which I've been asked to look at.

\dt gives many tables, here are just 3...

 public | some_idIds                                       | table |
cron_user
 public | WarningIds                                       | table |
cron_user
 public | cpf_inv_driverIds                                | table |
cron_user

but \d public.some_idIds gives..

Did not find any relation named "public.some_idIds".

Soooooo... what am I missing ?
owner is "cron_user".  \dt shows cron_user is the owner of the table.

---

From: Magnus Hagander <[email-masked]>
Date: Fri, 3 May 2024 23:06:10 +0200

> Did not find any relation named "public.some_idIds".

Looks like you might need a \d "some_idIds" (include the quotes) since it
has an uppercase characters?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 3 May 2024 14:06:38 -0700

> Soooooo... what am I missing ?

Try:

select relname, length(relname) from pg_class where relname ilike 
'%some_idIds%';

Best guess there are hidden characters.

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 3 May 2024 14:08:26 -0700

On 5/3/24 14:06, Magnus Hagander wrote:
> 
> 
> On Fri, May 3, 2024 at 10:58 PM David Gauthier <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>       public | some_idIds                                       | table
>     | cron_user
>       public | WarningIds                                       | table
>     | cron_user
>       public | cpf_inv_driverIds                                | table
>     | cron_user
> 
> Looks like you might need a \d "some_idIds" (include the quotes) since 
> it has an uppercase characters?

This:

"Did not find any relation named "public.some_idIds"."

to me indicates it did look for the properly cased name.

---

From: Magnus Hagander <[email-masked]>
Date: Fri, 3 May 2024 23:13:14 +0200

> to me indicates it did look for the properly cased name.

That is arguably a really bad error message, because it puts those quotes
there whether needed or not. if you put the quotes in there, you get:

Did not find any relation named "public."some_idIds"".

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 3 May 2024 14:13:38 -0700

> to me indicates it did look for the properly cased name.
More likely there is confusion between an upper case I "eye" and a lower
case l "el".

David J.

---

From: Tom Lane <[email-masked]>
Date: Fri, 03 May 2024 17:15:11 -0400

> to me indicates it did look for the properly cased name.

No, that message just regurgitates what you typed.  Magnus is
correct that the pattern will be case-folded if not quoted.
You can check with --echo-hidden (-E):

postgres=# \d public.some_idIds
/******** QUERY *********/
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(some_idids)$' COLLATE pg_catalog.default
  AND n.nspname OPERATOR(pg_catalog.~) '^(public)$' COLLATE pg_catalog.default
ORDER BY 2, 3;
/************************/

So it is in fact looking for public.some_idids.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Fri, 03 May 2024 17:28:20 -0400

> Did not find any relation named "public."some_idIds"".

This is one of the places where it's unfortunate that our English-text
rule for quoting a string to set it off from the rest of the error
message collides with SQL's rule for quoting an identifier.  Leaving
out the outer quotes would be contrary to our style guide, but having
them there can be confusing too to people who know SQL well.

It'd be better if we could show the transformed search string, but
since it's been marked up to be a regex I fear that'd introduce
even more confusion than it solves.

			regards, tom lane

---

From: Isaac Morland <[email-masked]>
Date: Fri, 3 May 2024 18:08:25 -0400

On Fri, 3 May 2024 at 17:28, Tom Lane <[email-masked]> wrote:

> them there can be confusing too to people who know SQL well.

I'm not sure if this is a serious suggestion or not: enclose the name of
the table, as you would type it in psql, between curly quotes.

So for example:

“table_name”
“"TableNameWithUpperCaseLetters"”

In a context where the curly quotes and surrounding message could be in a
non-monospaced typeface and the table name (including double quote marks if
needed) in a monospaced typeface this would be more natural.

---

From: jian he <[email-masked]>
Date: Sat, 4 May 2024 11:40:56 +0800

>   AND n.nspname OPERATOR(pg_catalog.~) '^(public)$' COLLATE pg_catalog.default

make it case insensitive?
like
> WHERE c.relname OPERATOR(pg_catalog.~*) '^(some_idids)$' COLLATE pg_catalog.default

we already transformed `\d SOME_IDIDS` to `\d some_idids`, in some way
it looks case-insensitive.

---

From: Tom Lane <[email-masked]>
Date: Fri, 03 May 2024 23:51:17 -0400

> make it case insensitive?

That would just move the problem around; that is, now we'd have people
complaining because they'd asked for "\d foo" and were getting results
for tables Foo and FOO.

By and large, I'd expect people using mixed-case table names to get
accustomed pretty quickly to the fact that they have to double-quote
those names in SQL.  I don't see why it's a surprise that that is also
true in \d commands.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 3 May 2024 21:06:20 -0700

> those names in SQL.  I don't see why it's a surprise that that is also
Every day the number of people increases who get mixed-case names in their
DB because their client language preserves case and doesn’t require quoting.

And it isn’t like any sane person is using case to distinguish names in
their DB and so would be at risk of seeing multiple unwanted matches for
any given pattern.

I don’t think name pattern matching during object lookup is a good tool to
wield to try and convince people that using camel case is a bad idea and
they should use snake case instead.  If they don’t write enough raw SQL to
be annoyed by their choice more power to them, making \d more accessible
for them is a win and snake case people won’t notice or care.

David J.

---

From: Tom Lane <[email-masked]>
Date: Sat, 04 May 2024 00:14:23 -0400

> DB because their client language preserves case and doesn’t require quoting.

And?  If they access the DB exclusively through their client language,
then yeah maybe they'll never know the difference.  But if they are
also using psql or other direct-SQL-access tools, they will learn
the quoting rules PDQ.  There's still no reason that \d should be
inconsistent with SQL.

			regards, tom lane

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 3 May 2024 21:22:55 -0700

On 5/3/24 21:06, David G. Johnston wrote:
> On Friday, May 3, 2024, Tom Lane <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
> 
> Every day the number of people increases who get mixed-case names in 
> their DB because their client language preserves case and doesn’t 
> require quoting.

In a sense they do by making quoting the default, which people find out 
when they stray from the language/ORM/framework and get pointed at:

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

> their DB and so would be at risk of seeing multiple unwanted matches for 

Have you met people?

> 
> I don’t think name pattern matching during object lookup is a good tool 
> to wield to try and convince people that using camel case is a bad idea 
> and they should use snake case instead.  If they don’t write enough raw 
> SQL to be annoyed by their choice more power to them, making \d more 
> accessible for them is a win and snake case people won’t notice or care.
> 
> David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 3 May 2024 21:47:23 -0700

> Have you met people?

I really don’t care enough to try and actually make converts here.  It
would have been a perfectly justifiable design choice to make our “pattern”
matching case-insensitive by default, probably with a case-sensitive mode
and no need to hack up quoting rules that are unique to it.  It’s a find
feature and search benefits from case-insensitivity.  There isn’t anything
so compelling about the current behavior that it seems like the superior
choice.  But maybe you are right and I just lack sufficient real-world
experience to see things differently.

I also get not wanting to change behavior at this point though I’d welcome
a modifier like “*” (like the ~* operator) to enable case-insensitive
matching.

David J.

---

From: Tom Lane <[email-masked]>
Date: Sat, 04 May 2024 00:57:55 -0400

"David G. Johnston" <[email-masked]> writes:
> ... I’d welcome

We could talk about that idea, certainly.  I'm afraid it's the sort
of edge case that would mainly be useful to newbies who haven't
read the docs closely enough to know the option exists, let alone
that it will help them.  But maybe there's more use-case than
I'm thinking of.

			regards, tom lane

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Sun, 5 May 2024 10:32:52 +0200

> owner is "cron_user".  \dt shows cron_user is the owner of the table.

Magnus already helped you, but you might want to check this:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names
and generally read the whole "Don't Do This" wiki page.

Best regards,

depesz