Results 1 to 8 of 8

Thread: Indexes

  1. #1
    Join Date
    May 2013
    Posts
    17

    Unanswered: Indexes

    Hello,

    How can we create an index when its not already created. Can I do something like this in postgres ? (it works in sql server 2008)

    BEGIN
    IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'my_index')
    CREATE INDEX my_index ON myTable(ID);
    ELSE
    ALTER INDEX [my_index] ON [dbo].[myTable] REBUILD ;
    END;

    I know we can use pg_indexes or pg_class instead of sysindexes, however, am not able to replicate "IF NOT EXISTS" clause.

    Please help!!!

    Zubi

  2. #2
    Join Date
    Mar 2014
    Posts
    17
    Quote Originally Posted by zubi6050 View Post
    Hello,

    How can we create an index when its not already created. Can I do something like this in postgres ? (it works in sql server 2008)

    BEGIN
    IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'my_index')
    CREATE INDEX my_index ON myTable(ID);
    ELSE
    ALTER INDEX [my_index] ON [dbo].[myTable] REBUILD ;
    END;

    I know we can use pg_indexes or pg_class instead of sysindexes, however, am not able to replicate "IF NOT EXISTS" clause.

    Please help!!!

    Zubi
    Refer this link PostgreSQL: Documentation: 9.3: ALTER INDEX

  3. #3
    Join Date
    May 2013
    Posts
    17
    hi,

    Thanks for the response but I do not have to alter the properties of the index. I need to create an index if it does not exist and if it already exists then i should not get an error that index already exists.


    Zubi

  4. #4
    Join Date
    Mar 2014
    Posts
    17
    try this

    DROP Index index_name IF EXISTS;
    CREATE INDEX indexname

  5. #5
    Join Date
    May 2013
    Posts
    17
    Thanks BUhuvnesh. This does solve a part of the problem.

    I also have to rebuild the index if it exists. Is it possible that postgres checks that if an index does not exist then create it else rebuild it??

    I know that we can use REINDEX to rebuild the index but can we make it condition based? like we can do in sql server something like:

    BEGIN
    IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'my_index')
    CREATE INDEX my_index ON myTable(ID);
    ELSE
    ALTER INDEX [my_index] ON [dbo].[myTable] REBUILD ;
    END;

  6. #6
    Join Date
    May 2013
    Posts
    17
    Sorry typo in your name Bhuvnesh

  7. #7
    Join Date
    Mar 2014
    Posts
    17
    Quote Originally Posted by zubi6050 View Post
    I also have to rebuild the index if it exists. Is it possible that postgres checks that if an index does not exist then create it else rebuild it??;
    Friend think logically how can you make the CREATE statement conditional here .
    you need two things before hand.

    Name of index while creating it becaise same will be pass to ELSE part(for rebuild)

    Second, which column will participate in CREATE.

    Better , create index wherever query need them

    and index rebuild all existing ones.
    .

    Keep both the task on separate plates.

  8. #8
    Join Date
    May 2013
    Posts
    17
    Thank you Bhuvnesh, will do something about it

Posting Permissions

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