0

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

Improve this question
4
  • Please show command you ran, and the full error message it generated. Commented Jan 4, 2020 at 21:21
  • 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 Commented Jan 4, 2020 at 21:23
  • 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. Commented Jan 4, 2020 at 21:30
  • Can you show us an EXPLAIN select distinct x from y? Commented Jan 4, 2020 at 21:31

2 Answers 2

Reset to default
0

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

Improve this answer
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.