2

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

Community
  • 1
  • 1
Zulander
  • 648
  • 1
  • 10
  • 22

3 Answers3

4

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.

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • Hello, for some reason my file was updated to 3.xx but i know for a fact that the version 2.2.3 is working with out any errors! thank's for you help – Zulander Oct 02 '15 at 02:23
  • Note that 2.2.7 is out, it's a good idea to upgrade. Npgsql 3.x also has a lot of performance and feature improvements, if you can transition to it by splitting your commands it's highly recommended. – Shay Rojansky Oct 02 '15 at 13:27
0

Create the table. Commit that Transaction. Then in a separate transaction, insert the data.

jaredlee.exe
  • 81
  • 1
  • 9
0

I had similar problem with running the DB on docker, locally it was running fine.

The solution was to add [Table("MissingTableName")] above the class.

GDBxNS
  • 139
  • 6