Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    3

    Unanswered: Table Pre existence check

    Hi All,

    I am an expert MSSQL developer but very new to PostGreSQL. Need alternate statement for for following MSSQL statement

    IF NOT EXISTS (SELECT * FROM sysobjects WHERE xtype = 'U' and name = <TableName>)
    BEGIN
    CREATE TABLE....
    END

    Thanks in advance
    amitm79

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Unortunately there is no equivalent. Postgres does not have "anonymous" blocks like SQL Server (or Oracle).

    If you just want to make sure that the table is created (and don't care if it contains data), you could first do a DROP TABLE <tablename> IF EXISTS;
    and then run the CREATE TABLE. Not a suitable solution if must keep the data in the table though.

    You will probably need to create a function to do that (or use a <insert your favorite scripting language> script)

    Check out the mailing list, there have been some suggestions on how to simulate this with PG. The searchable archive is here

  3. #3
    Join Date
    Jan 2007
    Posts
    3
    I don't want to loose data by recreating the table. Table should be created only if it doesn't exist

  4. #4
    Join Date
    Apr 2008
    Posts
    29
    SELECT * FROM pg_table WHERE tablename= <TableName>

    This will tell you if that table exists...

  5. #5
    Join Date
    Jan 2007
    Posts
    3
    yeah i figured it out but then i got stuck at using anonymous block

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by amitm79
    yeah i figured it out but then i got stuck at using anonymous block
    As I said, there is no such thing as an anonymous block in Postgres.

    This thread in the mailing list archive might be interesting for you.
    At least it should give you a headstart. The example could be expanded to create the table in case it does not exist.

Posting Permissions

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