Loading a Huge Textfile into PostgreSQL 11 on Windows 10
On Windows and as of 11.5, PostgreSQL has a bug when trying to
copy from a huge file. According to stack overflow, this does not manifest on earlier releases like PostgreSQL 10.
For example, given a table
create table pwned( passwd sha1 not null, count int4 not null );
and when trying to copy from the pwned password list which is about 24GB uncompressed,
copy pwned( passwd, count ) from 'c:\src\postgres\pwned\pwned-passwords-sha1-ordered-by-hash-v5.txt' with ( format text, delimiter ':' );
fails and the message follows.
ERROR: could not stat file "c:\src\postgres\pwned\pwned-passwords-sha1-ordered-by-hash-v5.txt": Unknown error
To work around this, we can pipe the file through a command;
copy pwned( passwd, count ) from program 'cmd /c "type c:\src\postgres\pwned\pwned-passwords-sha1-ordered-by-hash-v5.txt"' with ( format text, delimiter ':' );
which results in
and we can happily create an index and query the table.
The trick here is that we run
cmd in sigle command mode with the
/c flag, and tell it to
type out the file we want to
Update. There is now a bug report.
The author can be reached at firstname.lastname@example.org.
4 thoughts on “Loading a Huge Textfile into PostgreSQL 11 on Windows 10”
This work properly on my side but all the CSV are opened by the Notepad. Is there any possibility to not open the CSVs?
I apologize for the late reply. If you still need further assistance, it’s best to send me an email.
This is awesome! Thanks much!
I apologize for the late reply. I’m glad it helped you.