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]> 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 21:06 Magnus Hagander <[email protected]> parent: David Gauthier <[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 21:06 Adrian Klaver <[email protected]> parent: David Gauthier <[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 21:08 Adrian Klaver <[email protected]> parent: Magnus Hagander <[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 21:13 Magnus Hagander <[email protected]> parent: Adrian Klaver <[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 21:13 David G. Johnston <[email protected]> parent: Adrian Klaver <[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 21:15 Tom Lane <[email protected]> parent: Adrian Klaver <[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 21:28 Tom Lane <[email protected]> parent: Magnus Hagander <[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 22:08 Isaac Morland <[email protected]> parent: Tom Lane <[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-04 03:40 jian he <[email protected]> parent: 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-04 03:51 Tom Lane <[email protected]> parent: jian he <[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-04 04:06 David G. Johnston <[email protected]> parent: Tom Lane <[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-04 04:14 Tom Lane <[email protected]> parent: David G. Johnston <[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-04 04:22 Adrian Klaver <[email protected]> parent: 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-04 04:47 David G. Johnston <[email protected]> parent: Adrian Klaver <[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-04 04:57 Tom Lane <[email protected]> parent: David G. Johnston <[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-05 08:32 hubert depesz lubaczewski <[email protected]> parent: David Gauthier <[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