Martijn van Oosterhout <kleptog@svana.org> writes:
>> However, I'm wondering if there's a practical limit to how many rows you
>> can insert within one transaction?
> There's a limit of (I think) 2-4 billion commands per transaction. Each
> command can insert any number of tuples.
> So if you're doing one tuple per command that limits you to a few
> billion inserts per transaction. Ofcourse, COPY is always faster
> still...
If you have any deferred triggers (including foreign keys) on the table
then the practical limit is likely to be a lot less, say in the few
millions. However, in a bulk data load situation you probably don't
need to have such triggers; it's better to establish the constraint
after you load the data.
BTW, has the OP read
http://developer.postgresql.org/docs/postgres/populate.html
? Much of this thread seems to be rehashing that page ...
regards, tom lane