public inbox for [email protected]
help / color / mirror / Atom feed\dt shows table but \d <table> says the table doesn't exist ?
17+ messages / 8 participants
[nested] [flat]
* \dt shows table but \d <table> says the table doesn't exist ?
@ 2024-05-03 20:58 David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-05 08:32 ` Re: \dt shows table but \d <table> says the table doesn't exist ? hubert depesz lubaczewski <[email protected]>
0 siblings, 3 replies; 17+ messages in thread
From: David Gauthier @ 2024-05-03 20:58 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
@ 2024-05-03 21:06 ` Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Magnus Hagander @ 2024-05-03 21:06 UTC (permalink / raw)
To: David Gauthier <[email protected]>; +Cc: [email protected]
On Fri, May 3, 2024 at 10:58 PM David Gauthier <[email protected]>
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?
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
@ 2024-05-03 21:08 ` Adrian Klaver <[email protected]>
2024-05-03 21:13 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:13 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
0 siblings, 3 replies; 17+ messages in thread
From: Adrian Klaver @ 2024-05-03 21:08 UTC (permalink / raw)
To: Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; +Cc: [email protected]
On 5/3/24 14:06, Magnus Hagander wrote:
>
>
> On Fri, May 3, 2024 at 10:58 PM David Gauthier <[email protected]
> <mailto:[email protected]>> 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
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
@ 2024-05-03 21:13 ` Magnus Hagander <[email protected]>
2024-05-03 21:28 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Magnus Hagander @ 2024-05-03 21:13 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: David Gauthier <[email protected]>; [email protected]
On Fri, May 3, 2024 at 11:08 PM Adrian Klaver <[email protected]>
wrote:
> On 5/3/24 14:06, Magnus Hagander wrote:
> >
> >
> > On Fri, May 3, 2024 at 10:58 PM David Gauthier <[email protected]
> > <mailto:[email protected]>> 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"".
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:13 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
@ 2024-05-03 21:28 ` Tom Lane <[email protected]>
2024-05-03 22:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Isaac Morland <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-05-03 21:28 UTC (permalink / raw)
To: Magnus Hagander <[email protected]>; +Cc: Adrian Klaver <[email protected]>; David Gauthier <[email protected]>; [email protected]
Magnus Hagander <[email protected]> writes:
> On Fri, May 3, 2024 at 11:08 PM Adrian Klaver <[email protected]>
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:13 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:28 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
@ 2024-05-03 22:08 ` Isaac Morland <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Isaac Morland @ 2024-05-03 22:08 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Magnus Hagander <[email protected]>; Adrian Klaver <[email protected]>; David Gauthier <[email protected]>; [email protected]
On Fri, 3 May 2024 at 17:28, Tom Lane <[email protected]> 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
@ 2024-05-03 21:13 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2024-05-03 21:13 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected]
On Fri, May 3, 2024 at 2:08 PM Adrian Klaver <[email protected]>
wrote:
> On 5/3/24 14:06, Magnus Hagander wrote:
> >
> >
> > On Fri, May 3, 2024 at 10:58 PM David Gauthier <[email protected]
> > <mailto:[email protected]>> 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
@ 2024-05-03 21:15 ` Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-05-03 21:15 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected]
Adrian Klaver <[email protected]> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
@ 2024-05-04 03:40 ` jian he <[email protected]>
2024-05-04 03:51 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: jian he @ 2024-05-04 03:40 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected]
On Sat, May 4, 2024 at 5:15 AM Tom Lane <[email protected]> wrote:
>
> Adrian Klaver <[email protected]> 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
@ 2024-05-04 03:51 ` Tom Lane <[email protected]>
2024-05-04 04:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-05-04 03:51 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected]
jian he <[email protected]> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
2024-05-04 03:51 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
@ 2024-05-04 04:06 ` David G. Johnston <[email protected]>
2024-05-04 04:14 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 04:22 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: David G. Johnston @ 2024-05-04 04:06 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: jian he <[email protected]>; Adrian Klaver <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected] <[email protected]>
On Friday, May 3, 2024, Tom Lane <[email protected]> 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
2024-05-04 03:51 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 04:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
@ 2024-05-04 04:14 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2024-05-04 04:14 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: jian he <[email protected]>; Adrian Klaver <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Friday, May 3, 2024, Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
2024-05-04 03:51 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 04:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
@ 2024-05-04 04:22 ` Adrian Klaver <[email protected]>
2024-05-04 04:47 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2024-05-04 04:22 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; Tom Lane <[email protected]>; +Cc: jian he <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected] <[email protected]>
On 5/3/24 21:06, David G. Johnston wrote:
> On Friday, May 3, 2024, Tom Lane <[email protected]
> <mailto:[email protected]>> 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
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
2024-05-04 03:51 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 04:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
2024-05-04 04:22 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
@ 2024-05-04 04:47 ` David G. Johnston <[email protected]>
2024-05-04 04:57 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2024-05-04 04:47 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Tom Lane <[email protected]>; jian he <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected] <[email protected]>
On Friday, May 3, 2024, Adrian Klaver <[email protected]> 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-03 21:15 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 03:40 ` Re: \dt shows table but \d <table> says the table doesn't exist ? jian he <[email protected]>
2024-05-04 03:51 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Tom Lane <[email protected]>
2024-05-04 04:06 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
2024-05-04 04:22 ` Re: \dt shows table but \d <table> says the table doesn't exist ? Adrian Klaver <[email protected]>
2024-05-04 04:47 ` Re: \dt shows table but \d <table> says the table doesn't exist ? David G. Johnston <[email protected]>
@ 2024-05-04 04:57 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2024-05-04 04:57 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; jian he <[email protected]>; Magnus Hagander <[email protected]>; David Gauthier <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
@ 2024-05-03 21:06 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: Adrian Klaver @ 2024-05-03 21:06 UTC (permalink / raw)
To: David Gauthier <[email protected]>; [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: \dt shows table but \d <table> says the table doesn't exist ?
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
@ 2024-05-05 08:32 ` hubert depesz lubaczewski <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: hubert depesz lubaczewski @ 2024-05-05 08:32 UTC (permalink / raw)
To: David Gauthier <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-05-05 08:32 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-03 20:58 \dt shows table but \d <table> says the table doesn't exist ? David Gauthier <[email protected]>
2024-05-03 21:06 ` Magnus Hagander <[email protected]>
2024-05-03 21:08 ` Adrian Klaver <[email protected]>
2024-05-03 21:13 ` Magnus Hagander <[email protected]>
2024-05-03 21:28 ` Tom Lane <[email protected]>
2024-05-03 22:08 ` Isaac Morland <[email protected]>
2024-05-03 21:13 ` David G. Johnston <[email protected]>
2024-05-03 21:15 ` Tom Lane <[email protected]>
2024-05-04 03:40 ` jian he <[email protected]>
2024-05-04 03:51 ` Tom Lane <[email protected]>
2024-05-04 04:06 ` David G. Johnston <[email protected]>
2024-05-04 04:14 ` Tom Lane <[email protected]>
2024-05-04 04:22 ` Adrian Klaver <[email protected]>
2024-05-04 04:47 ` David G. Johnston <[email protected]>
2024-05-04 04:57 ` Tom Lane <[email protected]>
2024-05-03 21:06 ` Adrian Klaver <[email protected]>
2024-05-05 08:32 ` hubert depesz lubaczewski <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox