How to Hide Result Set Decoration in Psql Output

How to hide result set decoration in Psql output

You can use the -t or --tuples-only option:

psql --user=myuser -d mydb --output=result.txt -t -c "SELECT * FROM mytable;"

---

Edited (more than a year later) to add:

You also might want to check out the COPY command. I no longer have any PostgreSQL instances handy to test with, but I think you can write something along these lines:

psql --user=myuser -d mydb -c "COPY mytable TO 'result.txt' DELIMITER ','"

(except that result.txt will need to be an absolute path). The COPY command also supports a more-intelligent CSV format; see its documentation.

Disable wrapping in Psql output

Psql uses a system viewer to show its output in the console. In bash it likely uses less for the scrollable/page-able features it provides. To use a different viewer or use different settings, you just need to set the PAGER environment variable.

Running psql to use less with the -S or --chop-long-lines option seemed to work for me:

PAGER="less -S" psql

You can also enable this feature while viewing output in less by typing -S and Enter.

how to turn off setval output when import psql dump

The "quiet" option -q is defined as: "run quietly (no messages, only query output)".

The result of setval() is a query result, not a message, so the quiet option doesn't suppress this.

If you don't want to see query results, you can redirect their output to /dev/null using the -o switch:

psql -o /dev/null -q -U postgres -d myDB -f /Users/hoaphan/dev/postgres_dump -p 5432

^{(I can't test it on Linux right now, but the equivalent thing works on Windows)}

How to hide column header from the result set output?

Assuming your mean the column header then give the first field in the query an alias like:

select email_address AS Whatever
from company_digital
where email_address is not null
and email_address is not null
and hash_id >=700 and hash_id <800
union
select email_address_2
from company_digital
where email_address_2 is not null
and email_address_2 is not null
and hash_id >=700 and hash_id <800
union
select email_address_3
from company_digital
where email_address_3 is not null
and email_address_3 is not null
and hash_id >=700 and hash_id <800;

Which would output:

| Whatever |
_____________
test@gmail.com

test123@gmail.com
_________________

Or

select email_address AS ' '
from company_digital
where email_address is not null
and email_address is not null
and hash_id >=700 and hash_id <800
union
select email_address_2
from company_digital
where email_address_2 is not null
and email_address_2 is not null
and hash_id >=700 and hash_id <800
union
select email_address_3
from company_digital
where email_address_3 is not null
and email_address_3 is not null
and hash_id >=700 and hash_id <800;

Which would output:

|  |
_____________
test@gmail.com

test123@gmail.com
_________________

EDIT CLEAN UP CODE:

SELECT email_address AS ' '
FROM company_digital
WHERE email_address IS NOT NULL
AND hash_id BETWEEN 700 AND 800
UNION
SELECT email_address_2
FROM company_digital
WHERE email_address_2 IS NOT NULL
AND hash_id BETWEEN 700 AND 800
UNION
SELECT email_address_3
FROM company_digital
WHERE email_address_3 IS NOT NULL
AND hash_id BETWEEN 700 AND 800;

Or depending on your data...

SELECT CASE WHEN email_address IS NOT NULL 
            THEN email_address
            WHEN email_address_2 IS NOT NULL 
            THEN email_address_2 
            WHEN email_address_3 IS NOT NULL 
            THEN email_address_3
       END AS ' '
FROM company_digital
WHERE hash_id BETWEEN 700 AND 800

disable NOTICES in psql output

SET client_min_messages TO WARNING;

That could be set only for the session or made persistent with ALTER ROLE or ALTER DATABASE.

Or you could put that in your ".psqlrc".

PostgreSQL - How to suppress query statement message

-a or --echo-all echoes all input from script. You won't need that. Include --tuples-only or the -t flag to print rows only like so:

psql -U postgres -d rails_development --tuples-only -f ProjectApp/db/Query.sql

psql --help says:

...
Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  ...

Output format options:
  ...
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -t, --tuples-only        print rows only
  ...

How to get output of a sql with additional special characters

Since you're using double quotes around the argument, double quotes inside the argument must be escaped with a backslash:

psql -c "select distinct '\"'||runweek||'\" numeric ,' from calendar where runweek between current_runweek()-2 and current_runweek() order by 1;"

Heredoc can also be used instead of -c. It accepts multi-line formatting so that makes the whole thing more readable.

(psql [arguments] <<EOF
  select distinct '"'||runweek||'" numeric ,'
     from calendar 
     where runweek between current_runweek()-2 and current_runweek()
   order by 1;
EOF
) > output

By using quote_ident which is specifically meant to produce a quoted identifier from a text value, you don't even need to add the double quotes. The query could be like:

select string_agg( quote_ident(runweek::text), ',' order by runweek)
 from calendar
 where runweek between current_runweek()-2 and current_runweek();

which also solves the problem that your original query has a stray ',' at the end, whereas this form does not.

---

Related Topics