Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Testing for postgres index

Reply
Thread Tools

Testing for postgres index

 
 
Mr. M.J. Lush
Guest
Posts: n/a
 
      01-25-2005
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.
 
Reply With Quote
 
 
 
 
phaylon
Guest
Posts: n/a
 
      01-25-2005
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...

 
Reply With Quote
 
 
 
 
Mr. M.J. Lush
Guest
Posts: n/a
 
      01-25-2005
In article <(E-Mail Removed)>,
phaylon <(E-Mail Removed)> 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.
 
Reply With Quote
 
Gregory Toomey
Guest
Posts: n/a
 
      01-25-2005
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
 
Reply With Quote
 
phaylon
Guest
Posts: n/a
 
      01-25-2005
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«

 
Reply With Quote
 
Mr. M.J. Lush
Guest
Posts: n/a
 
      01-25-2005
In article <(E-Mail Removed)>,
phaylon <(E-Mail Removed)> 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.
 
Reply With Quote
 
phaylon
Guest
Posts: n/a
 
      01-25-2005
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.

 
Reply With Quote
 
Mr. M.J. Lush
Guest
Posts: n/a
 
      01-25-2005
In article <(E-Mail Removed)>,
Gregory Toomey <(E-Mail Removed)> 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.
 
Reply With Quote
 
Richard Gration
Guest
Posts: n/a
 
      01-25-2005
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
 
Reply With Quote
 
phaylon
Guest
Posts: n/a
 
      01-25-2005
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«

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index" camelean@shaw.ca ASP .Net 3 02-22-2011 07:06 PM
sorting index-15, index-9, index-110 "the human way"? Tomasz Chmielewski Perl Misc 4 03-04-2008 05:01 PM
Postgres/Postgres-pr - some confusion Nick Black Ruby 1 11-16-2006 08:01 PM
index.htm or index.html ? Robert Cooze NZ Computing 15 12-13-2005 05:53 PM
problem with index.html .(page is automatically gettin redirected to index.html) karthikeyavenkat Java 2 03-17-2005 10:01 PM



Advertisments