Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Get tables definitions

    Is there a way to get with a query in postgres the definitions off all tables?

    For Example in SQLite (if i have a db with 4 tables with some columns) is "select sql from sqlite_master" and it returns:

    CREATE TABLE foo (bar STRING)
    CREATE TABLE foo2 (bar2 STRING)
    CREATE TABLE foo3 (bar3 NUMERIC, bar4 NUMERIC)
    CREATE TABLE foo4 (
    a integer,
    b varchar PRIMARY KEY,
    c BOOLEAN
    )

    so the sql query used to create all the tables.

    How can i do for postgres?

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    Mmmmhhh... i've forgotten to say that i need to get it from a PHP script...

    La php page must print on screen the database schema

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Then you'll have to build it using this info:
    Code:
    SELECT t.table_name, c.*
    FROM information_schema.tables t
    JOIN information_schema.columns c ON t.table_catalog = c.table_catalog
      AND t.table_schema = c.table_schema
      AND t.table_name = c.table_name
    WHERE t.table_catalog = 'dbname' -- INSERT YOUR DB HERE
      AND t.table_schema = 'public'     -- INSERT YOUR SCHEMA HERE
      AND t.table_type = 'BASE TABLE'
    ORDER BY t.table_name, c.ordinal_position

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •