Postgresql Insert loop execution time is too long

Hi,

I'm currently trying to save test data into a PostGreSQL database. I loop on my table and insert my data as the following :

for i=1:size(Data,1)

exec(obj.conn,['INSERT INTO...........'])

end

The size of my data is about 234000 rows. The copy in the database take 2h30...

I'm using the database toolbox

Thank you in advance for you help !

Answer 1

[Sachin]

Hi

I understand that you are facing an issue with execution time. The following information might be of good help to you.

Inserting data into a PostgreSQL data row by row can be a slow process, especially if you are dealing with a large amount of data.

One way to improve the performance is to use the COPY command instead of INSERT statements.

Here is an example of how you could use the COPY command in MATLAB.

1. Create a CSV file with your data.
2. Use the COPY command to load the data into the database.

exec(conn, ‘COPY mytable FROM ‘/path to myData.csv’ WITH (FORMAT CSV);’

You can find more information about COPY command in PostgreSQL here :

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

[Vincent]

Worked really well, thanks !