PostgreSQL dump distinct column without memory consumption

Question

I'm trying to dump csv of distinct varchar(43) column to file, table has around billion rows, select distinct command either runs out of memory and command is aborted or server just closes all connections. Does PostgreSQL have built in methods for such task?

psql (10.9 (Ubuntu 10.9-0ubuntu0.18.10.1)) 16GB RAM

• jjanes: Please show command you ran, and the full error message it generated.
• Phalelashvili: It's just ERROR: out of memory. I ran copy (select distinct x from y) to 'path'; but it's select distinct command that's causing problem
• jjanes: Try doing a set enable_hashagg=off first. Inappropriate hash aggregates is what usually causes this in my experience. The planner should know better than to use them with very large result sizes, but if the estimates are way off it might not know there is a very large result size.
• jjanes: Can you show us an EXPLAIN select distinct x from y?

Answer 1

Solution suggested by jjanes worked, run set enable_hashagg=off in psql terminal

Answer 2

You can dump it without distinct and than use the Linux "uniq" command. Just make sure that you defined the swap file that is good idea in any case if your system doesn't use it constantly.