Skip site navigation (1) Skip section navigation (2)

Re: getting list of tables from command line

From: "T(dot)J(dot) Adami" <adamitj(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: getting list of tables from command line
Date: 2007-10-31 17:02:55
Message-ID: 1193850175.985840.233780@y42g2000hsy.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-general
On 31 out, 12:01, craigwh(dot)(dot)(dot)(at)azapple(dot)com (Craig White) wrote:
> I wrote a little script to individually back up table schemas, table
> data and then vacuum the table and it works nicely but I wanted a way to
> query a database and get a text file with just the table names and
> cannot figure out a way to do that.
>
> my script looks like this...
> (all I want is to get a list of the tables into a text file pg_tables)
>
> #/bin/sh
> #
> DB_NAME=whatever
> #
> for i in `cat pg_tables`
> do
>   pg_dump  --username=postgres \
>     --schema=db
>     --table=$i \
>     --schema-only \
>     $DB_NAME > schemas/$i.sql
>   pg_dump  --username=postgres \
>     --schema=db \
>     --table=$i \
>     --data-only \
>     $DB_NAME > data/$i.sql
>   vacuumdb --username=postgres \
>    --dbname=$DB_NAME \
>    --table=db.$i \
>    --verbose \
>    --full
> done
>
> Is there a way to do that?
>
> Craig
>
> PS there's a lack of cohesion between various commands such as vacuumdb
> and pg_dump for things like '--schema'
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

First you can create a file with the SQL statement:

select tablename from pg_tables where schemaname not in
('information_schema','pg_catalog' ) order by tablename;

After, run it by psql:

psql -U [postgres_user] -d [database_name] -f [file_created_with_SQL]
> [output_file]

This will dump all non-database schema tables into the output file, so
you can open it and read table names.


In response to

pgsql-general by date

Next:From: David FetterDate: 2007-10-31 17:13:50
Subject: Re: getting list of tables from command line
Previous:From: T.J. AdamiDate: 2007-10-31 16:52:16
Subject: Re: Replacing RDBMS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group