Inserting data with parameters to PostgreSQL 16

We can’t really help without the real query. The problem is in that query, not PostgreSQL or NpgSQL. If PostgreSQL complains about mytypeid it means the real SQL query contains exactly this string, without the @ prefix or title-casing. As it is, the question’s code contains several problems, beyond the query itself.

The Parameters and Positional and Named Placeholders paragraphs in NpgSQL’s Basic Usage page show how easy it is to execute an INSERT with parameters.

Using positional parameters ($1, $2):

await using var cmd = new NpgsqlCommand("INSERT INTO table (col1) VALUES ($1), ($2)", conn)
{
    Parameters =
    {
        new() { Value = "some_value" },
        new() { Value = "some_other_value" }
    }
};

await cmd.ExecuteNonQueryAsync();

Or named parameters (p1, p2). The parameter name definitions do not contain the @ prefix

await using var cmd = new NpgsqlCommand("INSERT INTO table (col1) VALUES (@p1), (@p2)", conn)
{
    Parameters =
    {
        new("p1", "some_value"),
        new("p2", "some_other_value")
    }
};

await cmd.ExecuteNonQueryAsync();

In both cases, RETURNING id can be used to return an auto-generated ID value. To read it, ExecuteReader or ExecuteReaderAsync should be used :

var sql="""
        INSERT INTO table (col1) 
        VALUES 
            (@p1),
            (@p2)
        RETURNING id;
        """;

await using var cmd = new NpgsqlCommand(sql, conn)
{
    Parameters =
    {
        new("p1", "some_value"),
        new("p2", "some_other_value")
    }
};

using var reader=await cmd.ExecuteNonQueryAsync();
while(reader.Read())
{
    var id=reader.GetInt32(0);
    ...
}

Adopting this to the question’s query would look like this :

var sql="""
        INSERT INTO public.production_items (my_data_type_id, created_by, creation_time_utc)
        VALUES (@MyTypeId, @CreatedBy, @CreationTimeUtc)
        RETURNING Id;
        """;

await using var cmd = new NpgsqlCommand(sql, conn)
{
    Parameters =
    {
        new("MyTypeId", (int)DataTypeEnum.SomeTypeId),
        new("CreatedBy", 400),
        new("CreationTimeUtc",new DateTime(2018, 1, 1, 12, 0, 0,DateTimeKind.Utc)
    }
};

using var reader=await cmd.ExecuteNonQueryAsync();
...

In this case, since only one row is inserted, ExecuteScalar can be used to get the single result value:

var newId=await cmd.ExecuteScalarAsync();

This can be simplified to a single line using eg Dapper, to create the entire command and parameters automatically, from a parameter object’s properties:

var theParams=new {
    MyTypeId=DataTypeEnum.SomeTypeId,
    CreatedBy=400,
    CreationTimeUtc"=new DateTime(2018, 1, 1, 12, 0, 0,DateTimeKind.Utc)
};

var newId=con.ExecuteScalar(sql,theParams);

Batching

It seems the actual question is how to batch multiple queries. This is shown in the Batching section of the Basic Usage docs. There are two ways to do this.

The legacy way is to create a single string, separating the queries by a semicolon. This isn’t supported by PostgreSQL itself though. NpgSql has to parse the query, find the semicolons and split the string into individual statements :

var sql="""
        INSERT INTO table (col1) VALUES ('foo'); 
        SELECT * FROM table
        """;
await using var cmd = new NpgsqlCommand(sql, conn);
await using var reader = await cmd.ExecuteReaderAsync();

The current way is to use a NpgSqlBatch instance containing the individual commands:

await using var batch = new NpgsqlBatch(conn)
{
    BatchCommands =
    {
        new("INSERT INTO table (col1) VALUES ('foo')"),
        new("SELECT * FROM table")
    }
};

await using var reader = await batch.ExecuteReaderAsync();

Inserting the generated ID from the first INSERT into another table isn’t as simple as using currval in the second INSERT though. Sequences aren’t affected by transactions so currval will return the current sequence value, not the last value created in the same batch or transaction. If 2 or more connections execute INSERT at the same time, they may both end up using the same ID value.

In this particular case it’s possible to use a single statement, by using INSERT .. RETURNING as a normal query, in a CTE or subquery :

with times as (
    INSERT INTO public.production_items (
        my_data_type_id, 
        created_by, 
        creation_time_utc)
    VALUES (@MyTypeId, @CreatedBy, @CreationTimeUtc)
    RETURNING Id
)
INSERT INTO public.some_other_table (
    some_other_table_id, 
    some_additional_table)
SELECT 
    times.Id, 
    @SomeAdditionalTableValue
from times;