Thread: Get object creation sql script in psql client

Is there a way to get i.e. table creation sql script from an existing
table in psql (not postgresql, but psql client), like it is in pgAdmin?

I.e. i point it to existing table 'foo', and it writes:
CREATE TABLE foo (
   bar int
);

pg_dump -t foo database

Grzegorz Jaśkiewicz wrote:
> pg_dump -t foo database
>
Thanks, but pg_dump is not psql client (i meant the */bin/psql
interactive shell), and there is only an option for table objects, and
no one for i.e. indices.

pg_dump -t ANYOBJECT database
afaik., try it - play with it.


--
GJ

Grzegorz Jaśkiewicz wrote:
> pg_dump -t ANYOBJECT database
> afaik., try it - play with it.
>
>
>
that does not work for indices. But the index creation is shown when
placing it's parent table into -t. Thanks for the help, Grzegorz, the
issue is solved.

try exhausting possibilities there. Many ppl don't know that you can
have multiple -t objects , and than use -T for stuff you don't want as
well. It does the job pretty often I have to say.

In response to Igor Katson :
> Is there a way to get i.e. table creation sql script from an existing
> table in psql (not postgresql, but psql client), like it is in pgAdmin?
>
> I.e. i point it to existing table 'foo', and it writes:
> CREATE TABLE foo (
>   bar int
> );

wait for 8.4:
http://developer.postgresql.org/pgdocs/postgres/functions-info.html
- pg_get_functiondef(func_oid)
- pg_get_indexdef(index_oid)
- pg_get_ruledef(rule_oid)
- pg_get_triggerdef(trigger_oid)
- pg_get_viewdef(view_name)

Unfortunately, i can't see such a function for tabledef.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On 2009-01-20, Igor Katson <descentspb@gmail.com> wrote:
> Is there a way to get i.e. table creation sql script from an existing
> table in psql (not postgresql, but psql client), like it is in pgAdmin?
>
> I.e. i point it to existing table 'foo', and it writes:
> CREATE TABLE foo (
>    bar int
> );

pg_dump dbname --table=foo --schema-only

I realise this is not what exactly you asked for, nor does it give the
most efficient SQL for many tables.