FAQ
Hello

For a new project where I am running 2 versions of Postgres (8.4 and 9.0) simultaneously on an Ubuntu server:
I need the syntax of an SQL statement in order to create tables and all index from one database to the other via a utility program I have in Perl.
I see PgAdmin shows on the lower pane all the SQL needed to create a table and all its index, but I do not know how to obtain that info.
It would be optimal if I can get all the info in a single query (the way pgdump does).
Also I need the SQL to create all the SEQUENCES.

Thank you

Search Discussions

  • Michael Glaesemann at Apr 11, 2011 at 4:09 pm

    On Apr 11, 2011, at 7:55, Francisco Leovey wrote:

    Hello

    For a new project where I am running 2 versions of Postgres (8.4 and 9.0) simultaneously on an Ubuntu server:
    I need the syntax of an SQL statement in order to create tables and all index from one database to the other via a utility program I have in Perl.
    I see PgAdmin shows on the lower pane all the SQL needed to create a table and all its index, but I do not know how to obtain that info.
    It would be optimal if I can get all the info in a single query (the way pgdump does).
    Also I need the SQL to create all the SEQUENCES.
    Can you call pg_dump?

    pg_dump -s -t your_table

    Otherwise, I recommend taking a look at the pg_dump source code.

    Michael Glaesemann
    grzm seespotcode net
  • Francisco Leovey at Apr 11, 2011 at 6:40 pm
    I am not sure I will understand the code inside PG_DUMP

    I could call PG_DUMP and store the info on a text file and then read it into my program and then issue an SQL command
    But I was hoping to get the info directly from the Postgres tables using some fancy query.

    I am using some queries that give me a list of tables and a list of fields in a table but now I need the actual SQL as shown on PgAdmin

    Maybe somebody knows the way PgAdmin generates it



    --- On Mon, 4/11/11, Michael Glaesemann wrote:


    From: Michael Glaesemann <grzm@seespotcode.net>
    Subject: Re: [NOVICE] Need SQL of create TABLE including INDEX and SEQUENCE
    To: "Francisco Leovey" <fleovey@yahoo.com>
    Cc: pgsql-novice@postgresql.org
    Date: Monday, April 11, 2011, 1:06 PM


    On Apr 11, 2011, at 7:55, Francisco Leovey wrote:

    Hello

    For a new project where I am running 2 versions of Postgres (8.4 and 9.0) simultaneously on an Ubuntu server:
    I need the syntax of an SQL statement in order to create tables and all index from one database to the other via a utility program I have in Perl.
    I see PgAdmin shows on the lower pane all the SQL needed to create a table and all its index, but I do not know how to obtain that info.
    It would be optimal if I can get all the info in a single query (the way pgdump does).
    Also I need the SQL to create all the SEQUENCES.
    Can you call pg_dump?

    pg_dump -s -t your_table

    Otherwise, I recommend taking a look at the pg_dump source code.

    Michael Glaesemann
    grzm seespotcode net
  • Vibhor Kumar at Apr 11, 2011 at 8:51 pm

    On Apr 11, 2011, at 11:54 PM, Francisco Leovey wrote:

    I am not sure I will understand the code inside PG_DUMP

    I could call PG_DUMP and store the info on a text file and then read it into my program and then issue an SQL command
    But I was hoping to get the info directly from the Postgres tables using some fancy query.

    I am using some queries that give me a list of tables and a list of fields in a table but now I need the actual SQL as shown on PgAdmin

    Maybe somebody knows the way PgAdmin generates it
    pgAdmin runs multiple commands to get the definition of Table. Some of those commands are given below, which you can wrap in function to give you the definition of table:

    SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, format_type(ty.oid,att.atttypmod) AS displaytypname, tn.nspname as typnspname, et.typname as elemtypname,
    ty.typstorage AS defaultstorage, cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS serschema,
    (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey,
    CASE
    WHEN inh.inhparent IS NOT NULL AND att.attinhcount>0
    THEN inh.inhparent::regclass
    ELSE NULL
    END AS inhrelname,
    attoptions,
    EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk
    FROM pg_attribute att
    JOIN pg_type ty ON ty.oid=atttypid
    JOIN pg_namespace tn ON tn.oid=ty.typnamespace
    JOIN pg_class cl ON cl.oid=att.attrelid
    JOIN pg_namespace na ON na.oid=cl.relnamespace
    LEFT OUTER JOIN pg_inherits inh ON inh.inhrelid=att.attrelid
    LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
    LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
    LEFT OUTER JOIN pg_description des ON des.objoid=att.attrelid AND des.objsubid=att.attnum
    LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
    LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
    LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
    WHERE att.attrelid = 53395::oid
    AND att.attnum > 0
    AND att.attisdropped IS FALSE
    ORDER BY att.attnum;
    SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
    indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description,
    pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
    , substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
    FROM pg_index idx
    JOIN pg_class cls ON cls.oid=indexrelid
    JOIN pg_class tab ON tab.oid=indrelid
    LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
    JOIN pg_namespace n ON n.oid=tab.relnamespace
    JOIN pg_am am ON am.oid=cls.relam
    LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')
    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
    LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
    LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0)
    WHERE indrelid = 53395::oid AND contype='p'

    ORDER BY cls.relname;
    SELECT ct.oid, conname, condeferrable, condeferred, confupdtype, confdeltype, confmatchtype, conkey, confkey, confrelid, nl.nspname as fknsp, cl.relname as fktab, nr.nspname as refnsp, cr.relname as reftab, description
    FROM pg_constraint ct
    JOIN pg_class cl ON cl.oid=conrelid
    JOIN pg_namespace nl ON nl.oid=cl.relnamespace
    JOIN pg_class cr ON cr.oid=confrelid
    JOIN pg_namespace nr ON nr.oid=cr.relnamespace
    LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid
    WHERE contype='f' AND conrelid = 53395::oid
    ORDER BY conname;

    SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
    indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description,
    pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
    , substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
    FROM pg_index idx
    JOIN pg_class cls ON cls.oid=indexrelid
    JOIN pg_class tab ON tab.oid=indrelid
    LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
    JOIN pg_namespace n ON n.oid=tab.relnamespace
    JOIN pg_am am ON am.oid=cls.relam
    LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')
    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
    LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
    LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0)
    WHERE indrelid = 53395::oid AND contype='u'

    ORDER BY cls.relname;
    SELECT c.oid, conname, relname, nspname, description,
    pg_get_expr(conbin, conrelid, true) as consrc
    FROM pg_constraint c
    JOIN pg_class cl ON cl.oid=conrelid
    JOIN pg_namespace nl ON nl.oid=relnamespace
    LEFT OUTER JOIN pg_description des ON des.objoid=c.oid
    WHERE contype = 'c' AND conrelid = 53395::oid
    ORDER BY conname;
    SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
    indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description,
    pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
    , substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
    FROM pg_index idx
    JOIN pg_class cls ON cls.oid=indexrelid
    JOIN pg_class tab ON tab.oid=indrelid
    LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
    JOIN pg_namespace n ON n.oid=tab.relnamespace
    JOIN pg_am am ON am.oid=cls.relam
    LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')
    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
    LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
    LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0)
    WHERE indrelid = 53395::oid
    AND conname IS NULL
    ORDER BY cls.relname;
    SELECT rw.oid, rw.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable, nspname, description,
    pg_get_ruledef(rw.oid, true) AS definition
    FROM pg_rewrite rw
    JOIN pg_class cl ON cl.oid=rw.ev_class
    JOIN pg_namespace nsp ON nsp.oid=cl.relnamespace
    LEFT OUTER JOIN pg_description des ON des.objoid=rw.oid
    WHERE ev_class = 53395
    ORDER BY rw.rulename;
    SELECT t.oid, t.xmin, t.*, relname, nspname, des.description, l.lanname, p.prosrc,
    trim(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), '()') AS whenclause
    FROM pg_trigger t
    JOIN pg_class cl ON cl.oid=tgrelid
    JOIN pg_namespace na ON na.oid=relnamespace
    LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
    LEFT OUTER JOIN pg_proc p ON p.oid=t.tgfoid
    LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
    WHERE NOT tgisinternal
    AND tgrelid = 53395::oid
    ORDER BY tgname;


    Thanks & Regards,
    Vibhor Kumar
    EnterpriseDB Corporation
    The Enterprise PostgreSQL Company
    vibhor.kumar@enterprisedb.com
    Blog:http://vibhork.blogspot.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 11, '11 at 3:55p
activeApr 11, '11 at 8:51p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase