How to insert large .sql files without running out of memory

[natjw]

I have a bunch of .sql insert files stored on an sftp server.
I retrieve them using s.Client.Open(filePath) and get a sftp.File object.

How can I insert this without loading the complete file into memory?
conn.Exec expects a string. Is there any way to stream the file or something?

[jackc]

There's no way to stream the value of a single conn.Exec in pgx / pgconn. And if these files are large enough to cause a problem, then streaming the files probably wouldn't be a good solution as PostgreSQL will be buffering the entire file on it's side.

The solution is to send one SQL statement at a time. There is a parser designed to split statements internal to one of my other projects. https://pkg.go.dev/github.com/jackc/tern/v2@v2.3.2/migrate/internal/sqlsplit However, it only works on string inputs where you would need it to work on an io.Reader. But perhaps it can be a starting place for you.