Thread: Dumping table definitions
I am looking for a way to reformat the information that is generated from \d mytable into SQL syntax, such that the table can be recreated with 'psql -f mytable.sql' complete with index and constraint definitions. I can do awk and sed commands to do this if I need to, but first wanted to check if Pg already had tools to export the table structure (without the data). Does it? Also, Is there a way to export the structure of all tables at once instead of one table at a time? Mark
Attachment
"Mark Fenbers" <Mark.Fenbers@noaa.gov> writes: > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? pg_dump with the -s switch is a much better way ... regards, tom lane
use pg_dump with the --schema-only and --table= params ---------- Original Message ----------- From: "Mark Fenbers" <Mark.Fenbers@noaa.gov> To: Pg SQL Discussion Group <pgsql-sql@postgresql.org> Sent: Mon, 18 Jul 2005 12:50:54 -0400 Subject: [SQL] Dumping table definitions > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? > > Also, Is there a way to export the structure of all tables at once > instead of one table at a time? > > Mark ------- End of Original Message -------
On 7/18/05, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote: > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? pg_dump is your friend! Use option -s (--schema-only). It will dump the information you want. :-) Regards, Dawid