1

After reading PostgreSQL 9.3 documentation and having run a single simple query to make all column names lowercase I attempted to replicate this to make all the table names all lowercase...

UPDATE pg_attribute SET tablename=lower(tablename);

Unfortunately while the command line PSQL did not throw any errors none of the database table names were made lowercase.

How do I make all the table names lowercase in PostgreSQL?

I don't need anything complex as these are tables that will be dropped as the data is a mess so just a blatantly simple query would be great.

Improve this question
5
  • pg_attribute stores column names, not table names. Commented Nov 25, 2014 at 14:22
  • 2
    nonono, do not directly update the system catalogs. Really, really, do not do this. You have backups, right? (If you're dropping the tables, why are you trying to lowercase all the names?) Commented Nov 25, 2014 at 14:23
  • @CraigRinger This is imported data from MS Access, the whole thing is being outright replaced. The warnings don't apply here, at least to my question/situation directly. I can re-import endlessly/quickly. Most of the columns and tables are going to get renamed any way but I need to know this as there is a great need to automate importing dozens of databases for me to clean up and migrate them from MS Access to PHP/PostgreSQL. Commented Nov 25, 2014 at 14:47
  • @user312854 Ok, but don't do it this way. Use a pl/pgsql function to query the catalogs or information_schema and run execute format(...) ddl commands to make changes. Commented Nov 25, 2014 at 14:50
  • I'm going to do it either way and already have with column names. I'm building something from scratch and can re-import as much as I need to. Stop fighting me and give me the simple answer, total waste of time! Commented Nov 25, 2014 at 14:54

1 Answer 1

Reset to default
2

First off, don't do this.

Quote table names instead in the queries you need to run, then "MyTableName" will really be "MyTableName" in your query instead of being folded to lower case before the query is run the way MyTableName => mytablename would be.

If you feel a compelling need to do this -- then you should do it from an SQL dump of the DB 

pg_dump > backup.sql
sed -i s/SomeStuff/somestuff/g backup.sql
# ...etc.

If you have a list of table names, play with a big list of sed commands in a sed or shell script so you can tweak things until you've got it right.

That is not just safer, it is less insane hair pulling for you to deal with also. You can blow up the backup data by mistake; no harm done. You can't afford to screw up the data catalogues -- because you'll never get a clean dump again, and a mistake there may not be evident initially, especially if there are stored procedures involved.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes I'm doing this, I'm not being paid to maintain someone else's disaster, I'm being paid to replace it outright. I'm not going to vote you down though I'm not dealing with an additional export as migrating the client from MS Access is already painful enough.
@MikeSW Oh hey, all the data integrity is still there! Hey, I can still import data easily if it wasn't! -- Don't get me wrong I appreciate words of caution but just because my user has low reputation does not mean I'm some noob who doesn't understand what is at stake. I get this non-sense all the time, haven't run a pagefile on Linux or Windows in years and everything is solid. The larger your comfort zone and the more willing to take REASONABLE risks the more people will be able to relied on.
@user312854 No, seriously, if you have a real data model this doesn't work and will corrupt your db, but most likely not in a way you can discover when you first run it. For example, integrity checks, triggers and stored procedures which have table names saved within them will not have their table names changed by this method, which means they will fail the next time they are activated. It is unsurprising that a db import from MS Access lacks any integrity checks at all. The problem is that this page will float around SO forever, tricking Postgres noobs who don't understand into trying it.
@zxq9 I'm exporting the data from PostgreSQL and reimporting it after I review it because it originated from MS Access to begin with. You don't get answers by telling your life story on SO, way to go with the groupthink.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.