Testing for postgres index

[Mr. M.J. Lush]

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?

 

[phaylon]

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

 

[Mr. M.J. Lush]

Tried the Postgres Documentation?

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

 

[Gregory Toomey]

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

 

[phaylon]

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

 

[Mr. M.J. Lush]

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.

 

[phaylon]

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*

 

[Mr. M.J. Lush]

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.

 

[Richard Gration]

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

 

[phaylon]

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

 

[A. Sinan Unur]

(e-mail address removed) (Mr. M.J. Lush) wrote in

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.

 

[Mr. M.J. Lush]

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.

 

[Mr. M.J. Lush]

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

 

[phaylon]

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

 

[Gunnar Hjalmarsson]

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.

 

[Mr. M.J. Lush]

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.

 

[phaylon]

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

 

[Tad McClellan]

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]

Don't worry about it.

My mummy told me not to feed the Trolls.

Off to the killfile you go!

Good luck.