Linux - Postgres psql retrieving undesired table

Question

I've got the following problem:

There is a Postgres database which I need to get data from, via a Nagios Linux distribution. My intention is to make a resulting SELECT be saved to a .txt, that would be sent via email to me using MUTT.

Until now, I've done:

#!/bin/sh

psql -d roaming -U thdroaming -o saida.txt << EOF
        \d
        \pset border 2 
        SELECT central, imsi, mapver, camel, nrrg, plmn, inoper, natms, cba, cbaz, stall, ownms, imsi_translation, forbrat FROM  vw_erros_mgisp_totalizador
EOF

My problem is: The .txt "saida.txt" is bringing me info about the database, as follows:

                          Lista de relações
 Esquema |               Nome               |   Tipo    |    Dono    
---------+----------------------------------+-----------+------------
 public  | apns                             | tabela    | jmsilva
 public  | config_imsis_centrais            | tabela    | thdroaming
 public  | config_imsis_sgsn                | tabela    | postgres
(3 Registers)

+---------+---------+----------+---------+---------+--------+------------+-------+---------+----------+-------+-------+------------------+-----------+
| central |  imsi   |  mapver  |  camel  |  nrrg   |  plmn  |   inoper   | natms |   cba   |   cbaz   | stall | ownms | imsi_translation |  forbrat  |
+---------+---------+----------+---------+---------+--------+------------+-------+---------+----------+-------+-------+------------------+-----------+
| MCTA02  |   20210 |          |         |         |        | INOPER-127 |       |         |          |       |       |                  |           |
| MCTA02  |   20404 |          |         |         |        | INOPER-127 |       |         |          |       |       |                  |           |
| MCTA02  |   20408 |          |         |         |        | INOPER-127 |       |         |          |       |       |                  |           |
| MCTA02  |   20412 |          |         |         |        | INOPER-127 |       |         |          |       |       |                  |           |

.
.
.

How could I make the first table not to be imported to the .txt?

Ben · Accepted Answer · 2014-04-10 23:23:51Z

1

Remove the '\d' portion of the script which causing listing the tables in the DB you see at the top of your output. So your script will become:

#!/bin/sh

psql -d roaming -U thdroaming -o saida.txt << EOF
        \pset border 2 
        SELECT central, imsi, mapver, camel, nrrg, plmn, inoper, natms, cba, cbaz, stall, ownms, imsi_translation, forbrat FROM  vw_erros_mgisp_totalizador
EOF

To get the output to appear CSV formatted in a file named /tmp/output.csv do you can do the following:

#!/bin/sh

psql -d roaming -U thdroaming -o saida.txt << EOF
        \pset border 2 
        COPY (SELECT central, imsi, mapver, camel, nrrg, plmn, inoper, natms, cba, cbaz, stall, ownms, imsi_translation, forbrat FROM  vw_erros_mgisp_totalizador) TO '/tmp/output.csv' WITH (FORMAT CSV)
EOF

edited Apr 10, 2014 at 23:23

answered Apr 7, 2014 at 20:36

Ben

1364 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ZeldaElf Over a year ago

So simple! That what happens when you don't know what you're typing... Thanks a lot, fella!

ZeldaElf Over a year ago

can you make this table come in an excel, for example? The format that is exported is horrible to filter :/

ZeldaElf Over a year ago

I'm having an error on '(':$ ./TESTE.sh Estilo de borda Ã© 2. ERROR: Sintax error near "(" in caracter 6 LINE 1: COPY (SELECT central, imsi, mapver, camel, nrrg, plmn, inope...

ZeldaElf Over a year ago

I took a long time to see it too haha

Collectives™ on Stack Overflow

Linux - Postgres psql retrieving undesired table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related