Zulander Zulander - 18 days ago 14
C# Question

Npgsql i get: 42P01: relation "sometable" does not exist

I am trying to run this query via Npgsql, this query work well when i run it directly on the server because i get: Query returned successfully with no result in 15 ms.However, when i use Npgsql i get: 42P01: relation "sometable" does not exist

I know that the error is is in the INSERT statement, am i missing something ?

connection string: Host=192.168.137.47;Port=5432;UserId=postgres;Password=test;Database=pg_database;

var m_createdb_cmd = new NpgsqlCommand();
m_createdb_cmd.Connection = _connPg;
m_createdb_cmd.CommandText = psSQL;
_connPg.Open();
m_createdb_cmd.ExecuteNonQuery();
_connPg.Close();


The query

BEGIN;
CREATE TABLE "sometable" (
"test" varchar(254));
INSERT INTO "sometable" ("test") VALUES ('Hello World');
COMMIT;


The Log

2015-10-01 07:08:46 EDT ERROR: relation "sometable" does not exist at character 13
2015-10-01 07:08:46 EDT STATEMENT: INSERT INTO "sometable" ("test") VALUES ('Hello World')

p.s.:i've also looked at PostgreSQL ERROR: 42P01: relation "[Table]" does not exist does not help

Answer

This is due to a known limitation introduced in Npgsql 3.x - when sending multiple SQL statements in one command, later statements can no longer depend on entities created in earlier statements. For example, you can't create a table and then insert into it in the same command. The workaround is to simply split the table creation and the insert into two commands (note that this has nothing to do with transactions).

The issue tracking this is https://github.com/npgsql/npgsql/issues/641.