Обсуждение: \dt shows table but \d says the table doesn't exist ? Поиск
Список
Период
Сортировка

\dt shows table but \d

says the table doesn't exist ?
От
David Gauthier
Дата:
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.


Re: \dt shows table but \d

says the table doesn't exist ?
От
Magnus Hagander
Дата:


On Fri, May 3, 2024 at 10:58 PM David Gauthier <dfgpostgres@gmail.com> wrote:
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".


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

--

Re: \dt shows table but \d

says the table doesn't exist ?
От
Adrian Klaver
Дата:
On 5/3/24 13:58, David Gauthier wrote:
> 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 ?

Try:

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

Best guess there are hidden characters.

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: \dt shows table but \d

says the table doesn't exist ?
От
Adrian Klaver
Дата:
On 5/3/24 14:06, Magnus Hagander wrote:
> 
> 
> On Fri, May 3, 2024 at 10:58 PM David Gauthier <dfgpostgres@gmail.com 
> <mailto:dfgpostgres@gmail.com>> wrote:
> 
>     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".
> 
> 
> 
> 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.

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: \dt shows table but \d

says the table doesn't exist ?
От
Magnus Hagander
Дата:


On Fri, May 3, 2024 at 11:08 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 5/3/24 14:06, Magnus Hagander wrote:
>
>
> On Fri, May 3, 2024 at 10:58 PM David Gauthier <dfgpostgres@gmail.com
> <mailto:dfgpostgres@gmail.com>> wrote:
>
>     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".
>
>
>
> 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.

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"".

--

Re: \dt shows table but \d

says the table doesn't exist ?
От
"David G. Johnston"
Дата:
On Fri, May 3, 2024 at 2:08 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 5/3/24 14:06, Magnus Hagander wrote:
>
>
> On Fri, May 3, 2024 at 10:58 PM David Gauthier <dfgpostgres@gmail.com
> <mailto:dfgpostgres@gmail.com>> wrote:
>
>     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

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


More likely there is confusion between an upper case I "eye" and a lower case l "el".

David J.

Re: \dt shows table but \d

says the table doesn't exist ?
От
Tom Lane
Дата:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 5/3/24 14:06, Magnus Hagander wrote:
>> 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.

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;
/************************/

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

So it is in fact looking for public.some_idids.

            regards, tom lane

Re: \dt shows table but \d

says the table doesn't exist ?
От
Tom Lane
Дата:
Magnus Hagander <magnus@hagander.net> writes:
> On Fri, May 3, 2024 at 11:08 PM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>> This:
>> "Did not find any relation named "public.some_idIds"."
>> 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"".

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

Re: \dt shows table but \d

says the table doesn't exist ?
От
Isaac Morland
Дата:
On Fri, 3 May 2024 at 17:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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.

Re: \dt shows table but \d

says the table doesn't exist ?
От
jian he
Дата:
On Sat, May 4, 2024 at 5:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
> > On 5/3/24 14:06, Magnus Hagander wrote:
> >> 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.
>
> 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;
> /************************/
>
> Did not find any relation named "public.some_idIds".
>
> So it is in fact looking for public.some_idids.
>

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.

Re: \dt shows table but \d

says the table doesn't exist ?
От
Tom Lane
Дата:
jian he <jian.universality@gmail.com> writes:
> 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

Re: \dt shows table but \d

says the table doesn't exist ?
От
"David G. Johnston"
Дата:
On Friday, May 3, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.


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.

Re: \dt shows table but \d

says the table doesn't exist ?
От
Tom Lane
Дата:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, May 3, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

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

Re: \dt shows table but \d

says the table doesn't exist ?
От
Adrian Klaver
Дата:
On 5/3/24 21:06, David G. Johnston wrote:
> On Friday, May 3, 2024, Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
> 
>     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.
> 
> 
> 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

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

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.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: \dt shows table but \d

says the table doesn't exist ?
От
"David G. Johnston"
Дата:
On Friday, May 3, 2024, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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.

Re: \dt shows table but \d

says the table doesn't exist ?
От
Tom Lane
Дата:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> ... I’d welcome
> a modifier like “*” (like the ~* operator) to enable case-insensitive
> matching.

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

Re: \dt shows table but \d

says the table doesn't exist ?
От
hubert depesz lubaczewski
Дата:
On Fri, May 03, 2024 at 04:58:26PM -0400, David Gauthier wrote:
> Soooooo... what am I missing ?
> 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