Error loading table definition of a PostgreSQL table

When a PostgreSQL table name contains uppercase letters there is no known way to get the table definition SQL script.

When we right-click a table name in SQLPro for Postgres, and the column name has an uppercase letter and select Script as, CREATE to, New query window

we get the error message

/*
* Error loading table definition.
* ERROR: relation “public.users” does not exist
*/

As the error message tells us, PostgreSQL is looking for the table name with all lowercase letters.

---

The pg_dump utility acts the same way.

pg_dump -s -h MY_SERVER_ADDRESS -U M_USERNAME -t Users MY_DATABASE

The error message is

pg_dump: no matching tables were found

---

The same process works if the table name only contains lowercase letters.

Convert an existing PostgreSQL database to all lowercase table and column names

To avoid similar errors caused by upper case letters in table and column names, convert the existing database definition to all lowercase

1. 1. Backup your database from the command prompt

      PGPASSWORD=MY_PASSWORD pg_dump -h MY_DATABASE_ADDRESS -U MY_DATABSE_USER -f MY_BACKUP_FILENAME.bak MY_DATABASE_NAME

   2. Open the backup file in a text editor
   3. Add underscores to the table and column names to match the .NET entity framework user identity processes.
      1. The PostgreSQL driver converts the first letter of all lowercase table and column names to upper-case and the letter that follows an underscore. For example, asp_net_users will be converted to AspNetUsers.
         1. Replace the table names with TableName -> table_name ( leave __EFMigrationsHistory as is)
         2. Replace the column names with “ColumnName” -> “column_name”
         3. Replace the primary key names with PK_ -> pk_
         4. Replace the index names with IX_ -> ix_
         5. Replace the foreign key names with FK_ -> fk_
   4. Drop the database. In SQL Pro for Postgres
      1. Right-click the database and select Drop database
         
   5. Re-create the database. In SQL Pro for Postgres
      1. Rith-click the server name and select Create database
         
   6. Import the modified backup file from the command prompt

      PGPASSWORD=MY_PASSWORD psql -h MY_DATABASE_ADDRESS -U MY_DATABSE_USER -d MY_DATABASE_NAME -f MY_BACKUP_FILENAME.bak

   7. Reverse engineer the database back to the .NET data layer project
      1. Delete the class files that correspond to the database tables
      2. Delete the MY_DATABSEContext.cs file
      3. Import execute the command in the data layer project directory

         dotnet ef dbcontext scaffold --force "Host=MY_DATABASE_ADDRESS;Database=MY_DATABASE_NAME;Username=MY_DATABSE_USER;Password=MY_PASSWORD" Npgsql.EntityFrameworkCore.PostgreSQL