npgsql/npgsql

Npgsql is the .NET data provider for Postgresql. It allows any program developed for .NET framework to access a Postgresql database server. It is implemented in 100% C# code. Postgresql versions since 9.0 are officially supported, others may work.

Shay Rojansky

@roji

@Swoorup what are you using, EF Core? If so you can take a look at backing fields. Otherwise we'd need a bit more detail to understand what you need.

Babak

@babakarj

Does anyone has this issue with Npgsql.EntityFrameworkCore.PostgreSQL Version=3.0.1 ?
Npgsql.NpgsqlException (0x80004005): No password has been provided but the backend requires one (in MD5)
Just convert a .Net Core 2 to .Net Core 3. Also, set .pgpass file. Everything works fine in the previous version.

 Npgsql.NpgsqlException (0x80004005): No password has been provided but the backend requires one (in MD5)
    at Npgsql.NpgsqlConnector.AuthenticateMD5(String username, Byte[] salt, Boolean async)
    at Npgsql.NpgsqlConnector.Authenticate(String username, NpgsqlTimeout timeout, Boolean async)
    at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
    at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
    at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
 --- End of stack trace from previous location where exception was thrown ---
    at Npgsql.NpgsqlConnection.Open()
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction()
    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
    at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
    at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
    at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
    at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
    at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()

Shay Rojansky

@roji

@babakarj are you passing a password in the connection string? If so, nothing in that area should have changed... Double-check how you're running, and if necessary open an issue on the repo.

Babak

@babakarj

@roji No, it doesn't work without a password but works ok if pass it. Everything works fine with .net core 2 and Npgsql.EntityFrameworkCore.PostgreSQL version 2.2.4

Shay Rojansky

@roji

The message above is simply PostgreSQL telling you that a password is required to log in, and that you haven't provided one.

Are you saying that with previous versions you connect to PostgreSQL without a password? Or that you were using .pgpass before?

Babak

@babakarj

@roji Using .pgpass file.

Robert Mwangi Mathenge

@mr_mathenge_twitter

Hi guys! How do you import an .sql file into my DB using SQL shell on windows?

マジカルパティシエかばおちゃん!!

@Kyabatalian_twitter

Hi, I'm trying version up from 2.2.7.1 to 4.1.1.

In version 4.1.1, the parameter type defaults to NpgsqlTypes.NpgsqlDbType.Unknown. In contrast, SQLClient defaults to System.Data.SqlDbType.NVarChar. In version 2.2.7.1, NpgsqlTypes.NpgsqlDbType.Test was set. Is this an intended breaking change?

For example, the following code throws an exception in 4.1.1:

using (var conn = new NpgsqlConnection(connString))
{
   conn.Open();

   using (var cmd = conn.CreateCommand())
   {
     cmd.CommandText = "select @test is null;";

     var parameter = cmd.CreateParameter();
     parameter.ParameterName = "test";
     parameter.Value = DBNull.Value;

     cmd.Parameters.Add(parameter);

     var result = cmd.ExecuteScalar(); // => PostgresException: '42P18: could not determine data type of parameter $ 1'
     Console.WriteLine(result);
   }
}

Shay Rojansky

@roji

@Kyabatalian_twitter much has changed between 2.2 and 4.1... Yes, we don't by default assume parameters are string types: the moment you assign an actual value to the parameter its type will be inferred, but we can't do that for DBNull.Value. You have the option of setting DbType, NpgsqlDbType or DataTypeName on the parameter to explicitly specify the type.

マジカルパティシエかばおちゃん!!

@Kyabatalian_twitter

@roji Thank you for your reply. I'm using Dapper, so I can't set types manually...

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();

    object test = null;   // Arbitrary processing results, etc.
    conn.Execute("select @test;", new {test = test}); // => PostgresException: '42P18: could not determine data type of parameter $1'
}

It is used everywhere In my application, and it's too hard to fix everything. Is there a good solution?

Shay Rojansky

@roji

Any idea what this code is actually supposed to do? Because it's really quite useless, except maybe to check that the connection is still up or serve as a sort of keepalive (which would also be quite useless - Npgsql has something that does this automatically).

So bottom line, either investigate what this is supposed to do and consider removing it entirely, or simply replace it with SELECT NULL (no parameters) for the same effect.

マジカルパティシエかばおちゃん!!

@Kyabatalian_twitter

@roji The above comment is a simplified example to give a brief explanation of what is happening. As a specific example, consider the implementation of a method with the following specifications:

If userIds is null (unspecified), get the user names of all users.
Get the user name of the specified userIds .

private static IEnumerable<string> GetUserNames(int[] userIds)
{
    using (var conn = new NpgsqlConnection(connnectionString))
    {
        return conn.Query<string>("SELECT user_name FROM user_table WHERE @userIds is null OR user_id = any(@userIds);", new {userIds = userIds});
    }
}

When calling this method, the above error occurs if the value passed to the argument is null.

Eddie Parker

@eddieparker

Hello! I'm trying to integrate npsql into the aspnet core react template, and I almost have it working. Unfortunately I'm getting a ": column "EmailConfirmed" is of type integer but expression is of type boolean" which smells like a bad type conversion. Is this something I need to configure locally?

Eddie Parker

@eddieparker

Oh; I think I see now. I was running sqlite before so the migration was for it, which didn't have a boolean type. Rebuilding the migration stuff now...

Toxyno

@Toxyno

Helloo. Please am getting the below error trying to connect to Postgres DB

Unable to find the requested .Net Framework Data Provider for Postgres. It may not be installed

What can i do\

: 'Unable to find the requested .Net Framework Data Provider. It may not be installed.'

マジカルパティシエかばおちゃん!!

@Kyabatalian_twitter

@Toxyno Have you registered Npgsql's DbProviderFactory in your applications App.Config (or Web.Config) ? https://www.npgsql.org/doc/installation.html

Shay Rojansky

@roji

@Kyabatalian_twitter I understand the problem you're encountering, but what would you suggest that Npgsql do? Defaulting to Varchar as SqlClient does wouldn't help, as your type is actually an array of ints. The bottom line is that PostgreSQL expects the parameter to be typed, and Npgsql does do this for all cases of non-null values; however, for a null value it must be manually set.

You may want to look at Dapper's "custom type mappers" to manually set DbType/NpgsqlDbType on your parameter

@Toxyno you're not providing enough details - consider posting a question on stackoverflow tagged "npgsql"

lechgu

@lechgu

hey, I am getting "connection refused errors" while trying to connect my asp.net core app to postgresql running in docker
this is asp.net core 3.0 app in the docker container trying to connect to postgresql in another docker container orchestrated with docker-compose.
I am able to connect to the postgres in the container from the desktop.
I am able to connect from the asp.net container to the postgres container using psql.
any insights how to debug the issue?

Shay Rojansky

@roji

@lechgu You probably have some sort of docker port exposal (or other network setup) issue. Try to connect with the command-line psql (or just do telnet <host> 5432) from exactly where your application is running

lechgu

@lechgu

yes, I can psql command line successfully from the asp.net container to the postgres container

Matthijs ter Woord

@mterwoord

I have a Postgres 12 server, running in docker. I have 2 other docker conainers both connecting to that postgres server. Somehow recently (probably coinciding with going from a 7 month old pg 11 version to latest v12) connecting means very very slow responses, resulting in timeouts

does anybody have a clue?

Not sure where to look

I can repro it with my docker containers, but also with dbeaver

Any suggestions would be greatly appreciated

Matthijs ter Woord

@mterwoord

something got goofed in my docker images. but now i'm hitting a deadlock situation in the database.Any suggestions on how to find out what's going on?

Shay Rojansky

@roji

@lechgu if you can do psql from exactly the same machine/container to exactly the same machine/container, but can't use Npgsql, then double-check all the connection string details as you probably have some difference. They are virtually doing the same thing.

Richard Drent

@RichardDrent

Hi, have a project with npgsql linq up and running, but the NpgsqlTsVector gives a
"The type or namespace name 'NpgsqlTsVector' does not exist in the namespace 'Microsoft.EntityFrameworkCore'" Error

Anyone a suggestion why?

lechgu

@lechgu

this is just a follow up to my question on npgsql in the docker container. It was a config issue, now my app in asp.net core container happily connects to the postgres in another container. I am using postgress 11

Shay Rojansky

@roji

@lechgu thanks for reporting back!

@RichardDrent NpgsqlTsVector is in namespace NpgsqlTypes, not Microsoft.EntityFrameworkCore

Richard Drent

@RichardDrent

@roji Thanks

Andy Visser

@avisser

Hi all, I'm trying to connect my aspnet core app to an existing postgres db. I had some initial issues with case-sensitivity and I'm 95% sure I fixed those. At this point I get the error, PostgresException: 42P01: relation "AspNetUsers" does not exist; I see the query in my console,

SELECT a."Id", a."AccessFailedCount", ...
      FROM "AspNetUsers" AS a
      WHERE ((a."NormalizedUserName" = @__normalizedUserName_0) ...

I can run that query successfully from psql. I def have a table "AspNetUsers" with that capitalization. Can anyone suggest the next thing to debug?

lechgu

@lechgu

I would check the capitalization in the asp.net core migration files to see if it is consistent with what is expected.

varun kumar

@varunkumarmnnit

which is better npgsql or devart

for postgres

Shay Rojansky

@roji

@avisser the error seems to suggest that AspNetUsers is properly quoted in the generated SQL. What table exists in your database exactly? I'd guess maybe it's lower-case aspnet?

@varunkumarmnnit they are two "competing" providers. Npgsql is open-source and free, whereas devart is not. Devart also provides ADO providers for other databases, which probably provide better cross-provider compatibility.

Basically it depends on what you need, test both and make an informed decision.

Andy Visser

@avisser

@roji

But now you made me look at it. I was connecting to the wrong database (an old one where I hadn’t properly done casing). All is well. Thanks community!

Where communities thrive

People

Repo info

Activity