Postgres insert query max length or number of VALUES parameters

I'm trying to find a way to speedup population of my db.

I'd like to use COPY as suggested here, but I have only access to my db, not to the entire server. All other tips worked good for me, but unfortunately I need another speedup now.

So now I'm using just an INSERT ... VALUES without autocommit or any constraints.
Maybe I'm totally wrong, but I noticed that when I'm trying to execute lesser number of queries with more values in VALUES, population goes faster.

So I'd like to know what is the query length limit (or VALUES parameters limit).

The answer to your question is that it depends on your system. Eventually your client or server will run out of memory trying to process a huge statement. typical unimpressive hardware can handle a million (very skinny) rows without trouble.

The answer to your problem is that most widely used libraries have a way to use COPY...FROM STDIN which can be run from the client. If you use psql, that mechanism is \copy. In Perl's DBD::Pg, it is pg_putcopydata.

StackExchange Database Administrators Q#129972, answer score: 4