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 the 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. 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

Reverse engineer a database with AspNetCore in Visual Studio

For some reason the .NETCore designers did not think, that developers want to follow best practices by separating the data layer from the presentation layer.

The Entity framework out of the box only works if the database is accessed from the main application project.

When we try to reverse engineer a PostgreSQL database from a class library with the command:

cd MY_CLASS_LIBRARY_DIRECTORY
dotnet ef dbcontext scaffold "Host=localhost;Database=MY_DATABASE_NAME;Username=MY_USERNAME;Password=MY_PASSWORD" Npgsql.EntityFrameworkCore.PostgreSQL

we get the error message:

The specified framework version ‘2.1’ could not be parsed
The specified framework ‘Microsoft.NETCore.App’, version ‘2.1’ was not found.
– Check application dependencies and target a framework version installed at:
/usr/local/share/dotnet/
– Installing .NET Core prerequisites might help resolve this problem:
http://go.microsoft.com/fwlink/?LinkID=798306&clcid=0x409
– The .NET Core framework and SDK can be installed from:
https://aka.ms/dotnet-download
– The following versions are installed:
2.0.0 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
2.1.2 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
2.1.3 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

The problem is, that the …runtimeconfig.json files are only automatically generated in the bin/netcoreapp2.1 directory of the main application project, those are missing from all class libraries.

To enable the automatic generation of the …runtimeconfig.json files, add a line to the <PropertyGroup> section of the data layer class library project (.csproj) file.

<PropertyGroup>
...
<GenerateRuntimeConfigurationFiles>True</GenerateRuntimeConfigurationFiles>
...
</PropertyGroup>