Postgres case sensitive table & column names

dhutton@creativeone.com

I have a c# asp.net core 2.1 application using Identity. Identity creates its tables used for users and their roles, claims, etc. as AspNetUsers, etc. I have the datatables / editor successfully implemented in other views, but this particular view I'm working on manages users and I want to access the tables and columns created by Identity (my own tables I've been using snake_case and all lowercase for table names and columns and it's working fine).

In my controller, when I instantiate a new Editor and pass in the db, table name and primary key (Id), I'm getting an error in my debugger that says:

Npgsql.PostgresException: '42601: zero-length delimited identifier at or near """"' (attaching)

At first it wasn't finding my table and column names and in postgres you have to wrap those in double quotes if the case is mixed. I escaped my double-quotes and got further to this error but am not sure if there's a way I can easily resolve without messing with my db table and column names, etc.

allan

Hi,

The .NET libraries for Editor should actually be doing the quoting automatically which would preserve the case in Postgres (I find it so frustrating it folds to lowercase!).

If you just use:

new Editor(db, "AspNetUsers", "Id")

and add .Debug(true) before the .Process() call, could you let me know what the JSON returned from the server on load is please?

Thanks,
Allan

dhutton@creativeone.com

I haven't used debug before so let me know if I need to do anything in addition - I may need help finding the json returned from the server or I may not be getting that far. Attaching response from visual studio's debug when I removed those escaped quotes:

colin

Hi @dhutton@creativeone.com ,

The debug will be sent from the server, so you can see that in the browser's network tab of the developer's tools.

Cheers,

Colin

dhutton@creativeone.com

Here's the network tab and console output from not escaping those quotes. Those columns can't be found. If I do escape those quotes it can find the columns but I get the zero-length delimited identifier at or near """"'

dhutton@creativeone.com

Here's my stack trace and below are the statements and other info I pulled from the exception:

Npgsql.PostgresException
HResult=0x80004005
Message=42601: zero-length delimited identifier at or near """"
Source=Npgsql
StackTrace:
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at DataTables.DatabaseUtil.Postgres.Query._Exec()
at DataTables.Query._Select()
at DataTables.Query.Exec(String sql)
at DataTables.Editor._Get(Object id, DtRequest http)
at DataTables.Editor._Process(DtRequest data)
at DataTables.Editor.Process(DtRequest data)
at DataTables.Editor.Process(HttpRequest request)
at Agents.Areas.Users.UsersController.Users() in C:\Users\Dave\source\repos\Agents\Agents\Areas\Users\Controllers\UsersController.cs:line 36
at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

---

• $exception {Npgsql.PostgresException (0x80004005): 42601: zero-length delimited identifier at or near """"
  at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
  at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
  --- End of stack trace from previous location where exception was thrown ---
  at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
  at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
  at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1219
  at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
  at System.Data.Common.DbCommand.ExecuteReader()
  at DataTables.DatabaseUtil.Postgres.Query._Exec()
  at DataTables.Query._Select()
  at DataTables.Query.Exec(String sql)
  at DataTables.Editor._Get(Object id, DtRequest http)
  at DataTables.Editor._Process(DtRequest data)
  at DataTables.Editor.Process(DtRequest data)
  at DataTables.Editor.Process(HttpRequest request)
  at Agents.Areas.Users.UsersController.Users() in C:\Users\Dave\source\repos\Agents\Agents\Areas\Users\Controllers\UsersController.cs:line 36
  at lambda_method(Closure , Object , Object[] )
  at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
  at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
  at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()} Npgsql.PostgresException

---

dhutton@creativeone.com

I messed around with a Verbatim String Literal and felt like I got close. I used the below string literal and kept checking the exception's SQL statement until I got something I thought would work. I'll show you as close as I can get and what actually works when I tweaked it in pgAdmin:

The above got me:
SELECT "Id" as ""Id"", UserName as "UserName", Email as "Email", PhoneNumber as "PhoneNumber" FROM "AspNetUsers"

What I tweaked in pgAdmin that actually works is this:
SELECT "Id" as "Id", "UserName" as "UserName", "Email" as "Email", "PhoneNumber" as "PhoneNumber" FROM "AspNetUsers"

It's gotta have those quotes on the first column name as well as the second and it wants to put 2 quotes just on the 2nd Id which is weird ... If there's a way I can control that output then it would probably work for me. Any ideas?

allan

Thanks for this. I'll try it on my local dev VM and let you know what I find.

Allan

dhutton@creativeone.com

If needed, maybe some option to format the sql generation or a layer to map the existing output with the needed output. As a bonus, it could potentially be useful for other purposes - not just thinking of PostgreSQL but also no sql databases like MongoDB (if possible). Just a thought.

Let me know if you have any luck with this one! Thanks so much!

Dave

dhutton@creativeone.com

I've tabled the issue for now and am able to work around it by forcing everything to snake_case for PostgreSQL. That seems to have made PostgreSQL, identity, datatables / editor all happy and working well together. I can now use editor to manage my AspNetUsers table (which became asp_net_users) with largely very little fuss. My entity framework migrations all seem to be working correctly also.

For others, absorbing / implementing this link will help greatly getting you to the same place I am by overriding the OnModelCreating() method. In the end I did have to alter my table / column names to play nicely with PostgreSQL / Editor but there haven't been any negative ramifications from it so far.

https://andrewlock.net/customising-asp-net-core-identity-ef-core-naming-conventions-for-postgresql/