1

I have a integer[] slots in my PostgreSQL table and would like to use unnest to fill it in bulk.

 cmd.CommandText = "INSERT INTO inventory (slots) " +
                "SELECT t.slots " +
                      "FROM UNNEST(@slots) AS t(slots) " +
                      "RETURNING inventory_id";

cmd.Parameters.AddWithValue("@slots", new int[10][]);0

When I try this approach (just to test 10 entries with empty array of ints), I'm getting the following error:

Error: The CRL array type System.Int32[][] isn't supported by Npgsql or your PostgreSQL. If you wish to map it to an PostgreSQL composite type array you need to register it before usage.

Is there a simple solution to it?

I tried to add "NpgsqlDbType.Array | NpgsqlDbType.Integer", but to no avail.

Improve this question
4
  • 2
    "I have a integer[] slots" - but you're supplying int[10][] (a jagged array that is an array of 10 int[] arrays, all of which are null); did you mean instead new int[10] (an array of 10 int values, all of which are 0)? Commented Nov 19, 2024 at 16:45
  • Also do yourself a favour and use a raw or verbatim string, rather than + concatenation. Commented Nov 19, 2024 at 16:53
  • The sql column slots is integer[] ... when I tried new int[10] UNNEST makes it integer per entry, so that's not what I want. Commented Nov 19, 2024 at 16:54
  • A valid data in the record would be [1, 2, 3, 6] or [] (empty array of ints). So with int[10][] I tried to add 10 records each with slots value being empty array of ints. Commented Nov 19, 2024 at 16:57
Related questions
2
Array parameters to and from postgresql stored procedures using Npgsql from F#
3
How to map a F# enum style discriminated union to postgres with npgsql
11
How do I pass JSON as a primitive PostgreSQL type to a function using Dapper?
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.