Bulk data import into array field using Npgsql

Question

I have the following table in PostgreSQL:

+------------+----------+                   
|   Column   |   Type   |
+------------+----------+
| name       | text     |
| keywords   | text[]   |
+------------+----------+

I'd like to insert a whole lot of rows in it, but I can't figure out how to insert data into an array field. Using BeginTextImport does not work since the TextWriter doesn't accept arrays:

using (var writer = connection.BeginTextImport(
  "COPY table (name, keywords) FROM STDIN DELIMITER ';'"
))
{     
    foreach (var item in items)
    {
        writer.Write(item.Name + ";");
        // How to do this?
        writer.Write(item.Keywords.ToArray(), NpgsqlDbType.Array | NpgsqlDbType.Text);
    }
}

Using BeginBinaryImport gives an error: "Can't close writer, a row is still in progress, end it first NpgSql".

using (var writer = connection.BeginBinaryImport(
  "COPY table (name, keywords) FROM STDIN (FORMAT BINARY)"
))
{     
    foreach (var item in items)
    {
        writer.StartRow();
        writer.Write(item.Name, NpgsqlDbType.Text);
        writer.Write(item.Keywords.ToArray(), NpgsqlDbType.Array | NpgsqlDbType.Text);
    }
}// Exception thrown here

How can we bulk copy data into an array field?

Text import is inappropriate for this case - it means you're responsible for formatting everything in PostgreSQL's text format (delimiters and all). — Shay Rojansky
– Shay Rojansky, Commented May 11, 2016 at 16:47
I just tried your binary code example and it works just fine - what version of Npgsql are you using exactly? What type exactly is your items list? Can you submit the full source code which reproduces the issue? — Shay Rojansky
– Shay Rojansky, Commented May 11, 2016 at 16:48

s.m. · Accepted Answer · 2016-05-11 12:32:20Z

2

What about this? It references this library, which you could use (haven't used it myself so I can't comment on it).

If you don't want to take an additional dependency, you could at least look at what it does under the hood.

answered May 11, 2016 at 12:32

s.m.

8,0732 gold badges40 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sander Toonen Over a year ago

Thank you! The extra dependency is an issue indeed but based on how this library is implemented I noticed that I can better use BinairyImport. It is still not working (see my update) but i'm sure is a step towards a solution.

trajekolus · Accepted Answer · 2019-07-12 03:54:20Z

0

The error "Can't close writer, a row is still in progress, end it first NpgSql", is likely because the number of writer.Write items you use is less than the number of columns expected.

answered Jul 12, 2019 at 3:54

trajekolus

5637 silver badges17 bronze badges

Collectives™ on Stack Overflow

Bulk data import into array field using Npgsql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related