7

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.

Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
  • 3
    This is probably limited by the maximum length of a query string. I don't think there is an *a priori* limit on the number of rows. – Gordon Linoff Apr 27 '16 at 02:22
  • Any idea of the length of that query string? I'm accessing the db via TCP. – Jeff Widman Apr 27 '16 at 02:23
  • . . I haven't found a specific reference. At least 16 Mbytes seems to work -- but I don't know if Python and the intermediate layers also support that length. – Gordon Linoff Apr 27 '16 at 02:27

1 Answers1

6

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.

Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
middlestump
  • 1,035
  • 8
  • 22