\dt shows table but \d <table> says the table doesn't exist ?
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.
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_userbut \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/>
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_userbut \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
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_userbut \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
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_userbut \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/>
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.
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
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
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.
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.
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
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.
"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
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
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.
"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
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