Search Postgresql Archives

Re: How to load data from CSV into a table that has array types in its columns?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10/26/22 17:30, Siddharth Jain wrote:
Hello,

Given a long list like this:

1,2
2,4 --> 2 appears once
7,9
8,9
5,3
2,5 --> note 2 appears twice

I want to load it into this table:

create table tbl (
   id integer primary key,
   fids integer[]
)

so we will have 2 -> [4,5] where 2 is id and [4,5] are the fids

My actual dataset is very large and has 100M rows in it. How can it be efficiently loaded into postgres?

I think I have to use a program for this and am trying to use the pg library that comes with Node.js. I am reading the data in batches of 1M or 100k rows for example. I have created a dictionary in Node.js where I am storing the hashmap. The part where I am stuck is how to generate the SQL command?

1. I want to make one call to the server for the batch of 1M rows, not 1M calls 2. Some keys in the hashmap might already exist in the database. For these keys we want to append to the array
3. Some keys will not exist and for these we want to insert new rows

Can someone help me please? I am using Postgres for the first time.

The best way to deal with importing large batches of data is to use COPY

https://www.postgresql.org/docs/current/sql-copy.html

But that will not play well with modifying the data as you input it.

What I can see doing is:

1) COPY the data into a staging table:

create staging_tbl (id integer, fid integer)

2) Then using SQL statements to move the data to the final table.

As example of one possibility, using ON CONFLICT from here:

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

insert into table tbl select id, array[fid] from staging_table on conflict(id) DO UPDATE SET fids = array_append(fids, excluded.fid);

I would test with a smaller example data set to vetify.




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux