Thread: Exporting just schema/metadata (w/o data) in Postgres

I was just wondering if we can export just the schema/metadata in Postgres (i.e. just the table and column information, without the data). Is there an easy way to do this in Postgres?

Thanks in advance.

Gandalf Me wrote:
> I was just wondering if we can export just the schema/metadata in
> Postgres (i.e. just the table and column information, without the data).
> Is there an easy way to do this in Postgres?

pg_dump --help is your friend.

You are looking for the -s flag.

Sincerely,

Joshua D. Drake


>
> Thanks in advance.
>


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Sat, Oct 01, 2005 at 06:05:27PM +0530, Gandalf Me wrote:
> Thanks!
>  Is there a tool which allows me to import this metadata across different
> databases (postgres, oracle, db2)? Any standard format for storing the
> metadata.
>  Basically, I want my application to run on multiple DBs, and I want to
> create the same schema in all DBs with minimal effort.
>  I understand different DBs ahve different types and one need to do the
> mapping and all, but does such a generic tool exists.

For things like creating tables and queries, as long as you keep to the
SQL standard you should be fine. The syntax for indexes may vary (I
don't know) but even that should be manageable. Use SQL standard types
wherever possible to minimise pain.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

When I worked at United Devices we created an XML schema representation
that did a number of things:

Created schemas for DB2, Oracle, and eventually PostgreSQL
Created many simple stored procs automatically (DB2 and Oracle
syntax-check stored procs at compile, so this was a great way to catch
bad SQL)
Created documentation
Created a C interface layer

Unfortunately, I never could convince the guy who wrote all this to
release it because he felt it was an ugly hack.

On Sat, Oct 01, 2005 at 06:05:27PM +0530, Gandalf Me wrote:
> Thanks!
>  Is there a tool which allows me to import this metadata across different
> databases (postgres, oracle, db2)? Any standard format for storing the
> metadata.
>  Basically, I want my application to run on multiple DBs, and I want to
> create the same schema in all DBs with minimal effort.
>  I understand different DBs ahve different types and one need to do the
> mapping and all, but does such a generic tool exists.
>
> Regards.
>
>  On 9/30/05, Joshua D. Drake <jd@commandprompt.com> wrote:
> >
> > Gandalf Me wrote:
> > > I was just wondering if we can export just the schema/metadata in
> > > Postgres (i.e. just the table and column information, without the data).
> > > Is there an easy way to do this in Postgres?
> >
> > pg_dump --help is your friend.
> >
> > You are looking for the -s flag.
> >
> > Sincerely,
> >
> > Joshua D. Drake
> >
> >
> > >
> > > Thanks in advance.
> > >
> >
> >
> > --
> > Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
> > PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
> > Managed Services, Shared and Dedicated Hosting
> > Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
> >

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461