PostgreSQL – Describing Tables with \d Command

postgresql

I want to describe every table in a postgres database.

Currently I have this:

psql -d db -c "\d *" > db_description.txt

But this also includes all sequences, views, etc.

Is there a command where the * pattern only matches tables?
Or is there another way to do this?

Best Answer

How about simply using the \dt command? It's here to list tables only. You can get more help on PostgreSQL inner commands by issuing the \? command.

If you want to know what those commands do behind the scenes, you could also start psql with the -E option, as asked here. You could therefore rewrite this query to get exactly what you want, be it views, sequences, tables... or any combination of the above.

Related Solutions

Postgresql – psql, getting a list of databases to be parsed by a script

I don't see how your two solutions are different... Since it still has to actually CONNECT to a database instance to see what databases are there, I'm willing to bet the only different is HOW you are connecting.

psql -l -A -t

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

Try instead

psql -q -A -t -c "SELECT datname FROM pg_database"

This should use the default connection settings (database and login as local account, local connection, 5432 port). If you really need to, then specify the user and database with -U postgres -d postgres .. but otherwise just leave the host and port unspecified.

I'm willing to bet the only reason psql -l -A -t isn't asking for a password is because you have that information in your .pgpass file.

Postgresql – Uniq join 3 tables with relationship table not uniq

There are many ways. Here is one (should be a fast variant to retrieve all rows):

SELECT t1.*, t2.*
FROM   table1 t1
JOIN  (
    SELECT DISTINCT table_1_id, table_2_id
    FROM   table1_2
    ) t1_2 ON t1_2.table_1_id = t1.id
JOIN   table2 t2 ON t2.id = t1_2.table_2_id;

Why not remove the duplicates permanently? And add a UNIQUE constraint ...

Best Answer

Related Solutions

Postgresql – psql, getting a list of databases to be parsed by a script

Postgresql – Uniq join 3 tables with relationship table not uniq

Related Question