What is the maximum number of VALUES that can be put in a PostgreSQL INSERT statement?

Question

When inserting rows via INSERT INTO tbl VALUES (...), (...), ...;, what is the maximum number of values I can use?

To clarify, PostgreSQL supports using VALUES to insert multiple rows at once. My question isn't how many columns I can insert, but rather how many rows of columns I can insert into a single VALUES clause. The table in question has only ~10 columns.

Can I insert 100K+ rows at a time using this format?

I am assembling my statements using SQLAlchemy Core / psycopg2 if that matters.

Answer 1

As pointed out by Gordon, there doesn't appear to be a predefined limit on the number of values sets you can have in your statement. But you would want to keep this to a reasonable limit to avoid consuming too much memory at both the client and the server. The client only needs to build the string and the server needs to parse it as well.

If you want to insert a large number of rows speedily COPY FROM is what you are looking for.