Testing for postgres index

Discussion in 'Perl Misc' started by Mr. M.J. Lush, Jan 25, 2005.

  1. How can I test for the existance of an index in a postgres (7.4) table
    via the perl DBI?

    I want to write an function that I can pass database, table and column,
    which, if the table already has an index uses REINDEX to rebuild the
    index and if the index does not exist use CREATE INDEX.

    I've had a look through <http://search.cpan.org/~timb/DBI-1.46/DBI.pm>
    and could not see anything relevent (I would have assumend column_info()
    would do the job)

    A bit of Googling gave me a MySQL solution ("SHOW INDEX FROM table")
    but I can't find a postgers equivilant.

    What am I missing?
    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #1
    1. Advertising

  2. Mr. M.J. Lush

    phaylon Guest

    Mr. M.J. Lush wrote:

    > A bit of Googling gave me a MySQL solution ("SHOW INDEX FROM table") but I
    > can't find a postgers equivilant.


    Tried the Postgres Documentation?


    p

    --
    http://www.dunkelheit.at/
    thou shallst fear...
    phaylon, Jan 25, 2005
    #2
    1. Advertising

  3. In article <>,
    phaylon <> wrote:
    >Mr. M.J. Lush wrote:
    >
    >> A bit of Googling gave me a MySQL solution ("SHOW INDEX FROM table") but I
    >> can't find a postgers equivilant.

    >
    >Tried the Postgres Documentation?


    Yes, I understand that SHOW INDEX is specific MySQLs SQL implementation.
    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #3
  4. Mr. M.J. Lush wrote:

    > How can I test for the existance of an index in a postgres (7.4) table
    > via the perl DBI?
    >
    > I want to write an function that I can pass database, table and column,
    > which, if the table already has an index uses REINDEX to rebuild the
    > index and if the index does not exist use CREATE INDEX.
    >
    > I've had a look through <http://search.cpan.org/~timb/DBI-1.46/DBI.pm>
    > and could not see anything relevent (I would have assumend column_info()
    > would do the job)
    >
    > A bit of Googling gave me a MySQL solution ("SHOW INDEX FROM table")
    > but I can't find a postgers equivilant.
    >
    > What am I missing?


    Most relational databases have a data dictionary (stored as tables) that you
    can query. Look at the Postgres documentation.

    gtoomey
    Gregory Toomey, Jan 25, 2005
    #4
  5. Mr. M.J. Lush

    phaylon Guest

    Mr. M.J. Lush wrote:

    > Yes, I understand that SHOW INDEX is specific MySQLs SQL implementation.


    That's good. Have you looked for _your question_ in the postgres-manual?


    p

    --
    http://www.dunkelheit.at/

    The mind is its own place, and in itself
    Can make a heaven of hell, a hell of heaven. -- Milton, »Paradise Lost«
    phaylon, Jan 25, 2005
    #5
  6. In article <>,
    phaylon <> wrote:
    >Mr. M.J. Lush wrote:
    >
    >> Yes, I understand that SHOW INDEX is specific MySQLs SQL implementation.

    >
    >That's good. Have you looked for _your question_ in the postgres-manual?


    Don't worry about it.

    My mummy told me not to feed the Trolls.
    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #6
  7. Mr. M.J. Lush

    phaylon Guest

    Mr. M.J. Lush wrote:

    > My mummy told me not to feed the Trolls.


    If you don't want help, quit asking. And I don't answer questions which
    sound like "Would please one of you search that for me?".

    oh, btw: *plonk*

    --
    http://www.dunkelheit.at/
    sapere aude.
    phaylon, Jan 25, 2005
    #7
  8. In article <>,
    Gregory Toomey <> wrote:
    >Mr. M.J. Lush wrote:
    >> How can I test for the existance of an index in a postgres (7.4) table
    >> via the perl DBI?
    >>

    >Most relational databases have a data dictionary (stored as tables) that you
    >can query. Look at the Postgres documentation.


    Thanks for the pointer

    SELECT relname FROM pg_class WHERE relname = 'foo_bar_baz_index';

    does the trick.


    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #8
  9. On Tue, 25 Jan 2005 11:38:21 +0000, Mr. M.J. Lush wrote:

    > How can I test for the existance of an index in a postgres (7.4) table
    > via the perl DBI?
    >
    > I want to write an function that I can pass database, table and column,
    > which, if the table already has an index uses REINDEX to rebuild the
    > index and if the index does not exist use CREATE INDEX.
    >
    > I've had a look through <http://search.cpan.org/~timb/DBI-1.46/DBI.pm>
    > and could not see anything relevent (I would have assumend column_info()
    > would do the job)
    >
    > A bit of Googling gave me a MySQL solution ("SHOW INDEX FROM table")
    > but I can't find a postgers equivilant.
    >
    > What am I missing?


    The willingness to try? 60 seconds in psql gives me this:

    select count(*) from pg_indexes where tablename = '....' and indexname = '....';

    And being told to read the documentation does not qualify as trolling.

    Rich
    Richard Gration, Jan 25, 2005
    #9
  10. Mr. M.J. Lush

    phaylon Guest

    Mr. M.J. Lush wrote:

    > Gregory Toomey told me to read the documentation, but he rather kindly
    > provided me with a clue where to read.


    I didn't know where to find it. I knew it /was/ there, because I can
    remember it. Please tell me now why _I_ should start searching it for you?


    p

    --
    http://www.dunkelheit.at/

    »Better to reign in hell than to serve in heaven«
    -- John Milton, »Paradise Lost«
    phaylon, Jan 25, 2005
    #10
  11. (Mr. M.J. Lush) wrote in
    news:ct5i1f$re8$:

    > In article <>,
    > phaylon <> wrote:
    >>Mr. M.J. Lush wrote:
    >>
    >>> Yes, I understand that SHOW INDEX is specific MySQLs SQL
    >>> implementation.

    >>
    >>That's good. Have you looked for _your question_ in the
    >>postgres-manual?

    >
    > Don't worry about it.
    >
    > My mummy told me not to feed the Trolls.


    His suggestion was extremely reasonable. It seems like your mummy forgot
    to teach you not to make off-topic posts and to graciously accept helpful
    pointers.

    Sinan.
    A. Sinan Unur, Jan 25, 2005
    #11
  12. In article <>,
    Richard Gration <> wrote:
    >On Tue, 25 Jan 2005 11:38:21 +0000, Mr. M.J. Lush wrote:
    >>
    >> What am I missing?

    >
    >The willingness to try? 60 seconds in psql gives me this:
    >
    >select count(*) from pg_indexes where tablename = '....' and indexname = '....';
    >
    >And being told to read the documentation does not qualify as trolling.


    Saying 'An answer exists' without providing at least a clue as to the nature
    of the answer is unhelpful

    Gregory Toomey told me to read the documentation, but he rather
    kindly provided me with a clue where to read.

    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #12
  13. In article <>,
    phaylon <> wrote:
    >Mr. M.J. Lush wrote:
    >
    >> Gregory Toomey told me to read the documentation, but he rather kindly
    >> provided me with a clue where to read.

    >
    >I didn't know where to find it. I knew it /was/ there, because I can
    >remember it. Please tell me now why _I_ should start searching it for you?


    Gregory provided no references and did no searching for me all he
    told me was:-

    "Most relational databases have a data dictionary (stored as tables) that you
    can query. Look at the Postgres documentation."

    Which narrowed the search are down from all of the DBI documentation
    and all postgres documentation down to Chapter VII. Internals

    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #13
  14. Mr. M.J. Lush

    phaylon Guest

    Mr. M.J. Lush wrote:

    > Which narrowed the search are down from all of the DBI documentation and
    > all postgres documentation down to Chapter VII. Internals


    And asking you if you looked to the _postgres docs_ was trolling in which
    way? I would like to understand that, because I want to help people if I
    can. But it get's lesser fun every time someone calls you a "troll" for
    providing the idea to look in a particular place.


    p

    --
    http://www.dunkelheit.at/

    Ordinary morality is only for ordinary people.
    -- Aleister Crowley
    phaylon, Jan 25, 2005
    #14
  15. Mr. M.J. Lush wrote:
    > Gregory provided no references and did no searching for me all he
    > told me was:-
    >
    > "Most relational databases have a data dictionary (stored as tables) that you
    > can query. Look at the Postgres documentation."
    >
    > Which narrowed the search are down from all of the DBI documentation
    > and all postgres documentation down to Chapter VII. Internals


    That's fine. But how it makes other posters who try to help "trolls" or
    "unhelpful" is a mystery.

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
    Gunnar Hjalmarsson, Jan 25, 2005
    #15
  16. In article <>,
    phaylon <> wrote:
    >Mr. M.J. Lush wrote:
    >
    >> Which narrowed the search are down from all of the DBI documentation and
    >> all postgres documentation down to Chapter VII. Internals

    >
    >And asking you if you looked to the _postgres docs_ was trolling in which
    >way? I would like to understand that, because I want to help people if I
    >can. But it get's lesser fun every time someone calls you a "troll" for
    >providing the idea to look in a particular place.


    I am very sorry I called you a troll (honestly), I had spent the morning
    trying to find a solution and writing the post and was convinced
    that I had missed something DBI documentation, partucuarly column_info().

    Your posts came across to me as 'I know exactly where the answer is,
    but I am going to make you crawl across broken glass before I will tell
    you' and I posted in haste, I'm sorry.
    --
    Michael
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    NPC rights activist | Nameless Abominations are people too.
    Mr. M.J. Lush, Jan 25, 2005
    #16
  17. Mr. M.J. Lush

    phaylon Guest

    Mr. M.J. Lush wrote:

    > Your posts came across to me as 'I know exactly where the answer is, but I
    > am going to make you crawl across broken glass before I will tell you'
    > and I posted in haste, I'm sorry.


    No problem. It wasn't meant that way, just also posted in haste between
    working phases.

    so long,
    p

    --
    http://www.dunkelheit.at/

    The first rule of project mayhem is: you do not ask questions.
    -- Fight Club
    phaylon, Jan 25, 2005
    #17
  18. Mr. M.J. Lush <> wrote:


    > Saying 'An answer exists' without providing at least a clue as to the nature
    > of the answer is unhelpful



    See what you get for asking an off-topic question?

    It is better *for you* to ask postgres questions in the postgres
    newsgroup. That is where the people that know about postgres
    hang out after all!


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
    Tad McClellan, Jan 25, 2005
    #18
  19. Mr. M.J. Lush <> wrote:
    > In article <>,
    > phaylon <> wrote:
    >>Mr. M.J. Lush wrote:
    >>
    >>> Yes, I understand that SHOW INDEX is specific MySQLs SQL implementation.

    >>
    >>That's good. Have you looked for _your question_ in the postgres-manual?

    >
    > Don't worry about it.
    >
    > My mummy told me not to feed the Trolls.



    Off to the killfile you go!

    Good luck.


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
    Tad McClellan, Jan 25, 2005
    #19
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. karthikeyavenkat
    Replies:
    2
    Views:
    576
    Bryce
    Mar 17, 2005
  2. Nick Black
    Replies:
    1
    Views:
    152
    Kashia Buch
    Nov 16, 2006
  3. Shawn W_
    Replies:
    5
    Views:
    270
    Aldric Giacomoni
    Sep 16, 2009
  4. ngoc
    Replies:
    5
    Views:
    171
    Tad McClellan
    May 11, 2006
  5. Tomasz Chmielewski

    sorting index-15, index-9, index-110 "the human way"?

    Tomasz Chmielewski, Mar 4, 2008, in forum: Perl Misc
    Replies:
    4
    Views:
    279
    Tomasz Chmielewski
    Mar 4, 2008
Loading...

Share This Page