Hacker News Reader

Comment by levkk | Hacker News Reader

The Postgres query parameters one is funny. 65k parameters is not enough for you?!

As it says, bulk inserts with large datasets can fail. Inserting a few thousand rows into a table with 30 columns will hit the limit. You might run into this if you were synchronising data between systems or running big batch jobs.

Sqlite used to have a limit of 999 query parameters, which was much easier to hit. It's now a roomy 32k.

COPY is often a usable alternative.

Right, for postgres I would use unnest for inserting a non-static amount of rows.

In the past I've used batches of data, inserted into a separate table with all the constraints turned off and using UNNEST, and then inserted into the final table once it was done. We ended up both batching the data and using UNNEST because it was faster but it still let us resume midway through.

We probably should have been partitioning the data instead of inserting it twice, but I never got around to fixing that.

COPY is likely a better option if you have access to the host, or provider-specific extensions like aws_s3 if you have those. I'm sure a data engineer would be able to suggest a better ETL architecture than "shove everything into postgres", too.

Was MERGE too slow/expensive? We tend to MERGE from staging or temporary tables when we sync big data sets. If we were on postgres I think we'd use ... ON CONFLICT, but MERGE does work.

> PostgreSQL USER is cursed > The USER keyword in PostgreSQL is cursed because you can select from it like a table, which leads to confusion if you have a table name user as well.

is even funnier :D

SQL's "feature" of having table and field names in the same syntactic namespace as an ever expanding set of english language keywords is the original eldritch curse behind it all.

Reply on news.ycombinator.com