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.