0

I find myself in a position that I'm sure many others have before. I generated my PostgreSQL schema using a generator and now I'm in production(!), I realise I should really try to remove the double quotes from the table and column names that the generator added i.e.

CREATE TABLE "myschema"."mytable" (....

should have been

CREATE TABLE myschema.mytable (....

I'm writing a PL/pgsql function to loop through information_schema.tables and information_schema.columns and then to execute ALTER TABLE statements to drop the doube quotes.

What I can't grasp from the documentation and searhcing is how to issue that statement.

I know it's an EXECUTE I need but can someone help me with the syntax to rename "mytable" to mytable using that. I'm lost with $$ and format() and quote_ident() and think I'm overcomplicating how to indicate that my original table names will have double quotes.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
royneedshelp
  • 97
  • 1
  • 9
  • Double quotes like you show are not part of identifiers, only decorators to preserve original spelling. And just noise in the given example. **No action needed.** See: https://stackoverflow.com/a/20880247/939860 – Erwin Brandstetter Dec 06 '21 at 16:31
  • Thanks Erwin. When you say no action needed, are you suggesting that I leave my schema as it is. Won't that mean that any users will need to be aware of the presence of the quotes, or the case used in the initial object creation statements? – royneedshelp Dec 06 '21 at 16:48

1 Answers1

1

The double-quotes in your examples are not part of the names, just decorators to preserve original spelling of identifiers. See:

While working with lower-case, legal identifiers (like your examples suggest), those double-quotes are purely optional. Add them or leave them.

If you've been unwise enough to create objects with illegal names or with mixed case, those double-quotes are required at all times. The cure is to stick to legal, lower-case identifiers.

Start by reading the manual about identifiers.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks again for the pointers. I understand your rationale and agree I will likely be OK doing nothing. However I have a system that is running on one production site and will now be rolling out many more each with their own database. For support purposes I would like the schemas as aligned as possible. Is there a way to make Postgres forget a column/table was created with quotation marks? – royneedshelp Dec 07 '21 at 09:55
  • @royneedshelp: Postgres does not remember whether double-quotes were used. All it remembers is the effective name (as stored in catalog tables - `pg_attribute` for column names). If you used double-quotes on a legal, lower-case name, then that was just noise. Else, you preserved an otherwise illegal spelling and you have to double-quote for the rest of the life of this object. Or you change names with `ALTER` commands. – Erwin Brandstetter Dec 07 '21 at 10:38
  • This was a good discussion thanks, and sure it will be useful to others who think they have a problem. You've helped me see the way out of my issue and I've realised I only had one table 'illegally named' which I can handle with a one off rename statement. Everything else, as you say is noise. – royneedshelp Dec 09 '21 at 10:29