0

I'm wondering if there is a way to use ::regclass to convert a string to a pg_class.oid in a way that respects case sensitivity. Let me give an example. It's often handy to use ::regclass to get the oid of a table, e.g. to list all columns of a table

SELECT * FROM pg_attribute WHERE attrelid = 'public.my_table'::regclass

However, ::regclass implicitly converts the input to all lowercase before doing the search. (This is similar to how PGSQL will interpret table names in SQL commands, if you don't surround them with double quotes.) This means if your table is called MY_table, then you cannot use casting to ::regclass to get its oid.

I know you can use other means, e.g. use pg_class.relname and pg_class.relnamespace. This question is specifically about using ::regclass, because ::regclass is more convenient (if I can find a way to make it work in a case sensitive way).

I've tried

SELECT * FROM pg_attribute WHERE attreloid = '"public.my_table"'::regclass

but that includes the double-quotes in the name it searches.

Andrew
  • 529
  • 1
  • 3
  • 12
  • Related: [Postgres Case Sensitivity](https://stackoverflow.com/questions/21796446): *"When creating entities (tables, views, procedures, etc) in PostgreSQL, you should specify them either unquoted, or quoted-but-lowercased"* – Robert Harvey May 20 '19 at 20:49
  • 4
    Don't you mean `'"public"."my_table"'::regclass`? i.e. quote each component of the fully qualified name separately since the `.` is not part of the name itself. – mu is too short May 20 '19 at 21:58
  • oh. ::facepalm::. Putting the quotes so they don't surround the . fixes the second query. thanks – Andrew May 20 '19 at 22:24

1 Answers1

4

The reason my second query didn't work is because the double quotes were around the . character. So here's how to do it.

SELECT * FROM pg_attribute WHERE attreloid = '"public"."my_table"'::regclass
Andrew
  • 529
  • 1
  • 3
  • 12