How could I solve the error of psql: Argument list too long?

Question

I have a script that contains the following psql command:

psql -h $PHOST -p $PPORT -U $PUSER -d $PDATABASE -c "SELECT COUNT(*) FROM (${SQL%?}) AS query;"

when I passed a simple query that query one or two relations, no problem. However, when I passed a long query, I got this error:

/usr/bin/psql: Argument list too long

I found solutions for "Argument list too long" with mv command I tried it, but nothing changed.

I used ulimit -S -s unlimited before the psql command to increase the MAX_ARG size, but still getting the error.

How could I solve the error of psql: Argument list too long?

Kusalananda · Accepted Answer · 2020-05-12 06:12:27Z

5

The xargs trick will not work here because psql requires -c to be one big string.

Try an alternative approach by putting all the sql in a file called statements.sql and launch the psql like this:

psql -h "$PHOST" -p "$PPORT" -U "$PUSER" -d "$PDATABASE" -f statements.sql

To create statemements.sql:

printf 'SELECT COUNT(*) FROM (%s) AS query;\n' "${SQL%?}" >statements.sql

This command would not suffer from the "Argument list too long" issue as printf is a built-in utility in the bash shell.

Kusalananda♦

356k42 gold badges735 silver badges1.1k bronze badges

answered May 12, 2020 at 5:57

GMaster

6,8873 gold badges34 silver badges35 bronze badges

1 Answer 1