Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Exclamation Unanswered: Create table if doesnt exists Postgre

    Hi, using Postgre DB, I need to create a Postgre DDL to check if a table exists and, if it exists, check the table fields and create the fields that doenst already exists.

    Any help?

    Thanks
    Renato

  2. #2
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    For the DDL part...

    From your command line:
    pg_dump -s -f mydb_ddl.sql mydb

    where "mydb" is the name of your database.

    Marc
    Last edited by marc_; 09-09-11 at 12:49. Reason: added switch -s

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Rather than dump the entire database to a script file, I would instead use the Information Schema.

    For your example, to determine the table/field existence:

    Code:
    Select table_name, column_name
    From information_schema.columns
    Where Table_Name = 'your_tablename' and schema_name = 'your_schemaname'
    The records returns using the above query are the table name and column name(s) which are defined in the database.

    If no records are returned, then there are no columns defined. (which would imply that the table doesn't exist... although it is possible, I suppose, that someone created a table, then deleted all the columns from that table.)

    To verify table existence, whether columns exist or not, use
    Code:
    Select table_name, table_type
    From information_schema.tables
    Where Table_Name = 'your_tablename' and schema_name = 'your_schemaname'
    If a record is returned, your table exists.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Aug 2011
    Posts
    42
    use

    select * from pg_tables where tablename = "mytable"
    to verify if the table is present.

  5. #5
    Join Date
    Nov 2011
    Posts
    1

    hello

    Yangtze River Cruise
    i like the forum

Tags for this Thread

Posting Permissions

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