Re: [GENERAL] How to know the indexes on a Table
Hi all,
Is there any way, to know the name of indexes on a table, defined in
a database. I mean can I query something like
Select Index_name from pg_class where relname = "Table_name" .
Thanks in advance.
--- Thanks & Reagrds
Anirban Pal | Software Engineer
Newgen Software Technologies Ltd.
Contact: (011) 26815467-72 | Extn: 177
Disclaimer :- This e-mail and any attachment may contain
confidential, proprietary or legally privileged information. If you
are not the origin al intended recipient and have erroneously
received this message, you are prohibited from using, copying,
altering or disclosing the content of this message. Please delete it
immediately and notify the sender. Newgen Software Technologies Ltd
(NSTL) accepts no responsibilities for los s or damage arising from
the use of the information transmitted by this email including
damages from virus and further acknowledges that no bin ding nature
of the message shall be implied or assumed unless the sender does so
expressly with due authority of NSTL.
Just take a look at the system catalogs, you'll find the view pg_index
as well http://www.postgresql.org/docs/8.3/static/catalog-pg-index.html
Example:
SELECT
nmsp.nspname AS schemaname,
tcls.relname AS tablename,
icls.relname AS indexname
FROM
pg_index
JOIN pg_class AS icls ON pg_index.indexrelid = icls.oid
JOIN pg_class AS tcls ON pg_index.indrelid = tcls.oid
JOIN pg_namespace AS nmsp ON tcls.relnamespace = nmsp.oid
WHERE
nmsp.nspname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY
schemaname ASC,
tablename ASC,
indexname ASC;
Kind regards,
Frank
Re: [GENERAL] String Manipulation
On Jun 13, 2009, at 12:35 AM, Christine Penner wrote:
Sam,
The problem with making it a numeric field is that I have seen
things like A123, #123a or 23-233. This is only here to make most
sorting work better, not perfect. It all depends on how they enter
the data. Wont the different formats make it harder to convert to a
number?
I tried your suggestion and haven't had any luck. For a quick test I
did this:
select b_lot_or_st_no, substring('1a','^[0-9]+') as TEST from
F_BUILDINGS
With this I tried using b_lot_or_st_no instead of 1a, I also
replaced the , with for like they do in the manual. I looked through
the manual but I'm still stuck.
The above regular expression assumes values start with a number, so it
won't return anything useful for values like 'A123' or '#123a' and
will just return '23' for '23-233'. I don't think Sam intended it to
be used with the values in your database but just to illustrate how a
regular expression could be used.
I think what you want is something like:
select regex_replace(b_lot_or_st_no, '[^0-9]', '', 'g')
This globally replaces everything that's not a number by '',
effectively removing it from the text.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a33833c759151518024860!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Maintenance database SQL_ASCII
On Jun 13, 2009, at 1:52 AM, Brad Schick wrote: After a new pgsql installation the "postgres" maintenance database has an encoding of SQL_ASCII. pgAdmin III gave me a warning about that, and I may want to create users or databases that are not restricted 7bit ASCII. SQL_ASCII <> 7 bit ASCII. What SQL_ASCII does is accept any value, regardless of encoding. It basically just stores the bytes, even for multi-byte encodings. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a33842e759156622419335! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to store text files in the postgresql?
On Jun 12, 2009, at 11:53 AM, Yaroslav Tykhiy wrote: I cannot but ask the community a related question here: Can such design, that is, storing quite large objects of varying size in a PostgreSQL database, be a good idea in the first place? I used to believe that what RDBMS were really good at was storing a huge number of relations, each of a small and mostly uniform size if expressed in bytes; but today people tend to put big things, e.g., email or files, in relational databases because it's convenient to them. That's absolutely normal as typical data objects we have to deal with keep growing in size, but how well can databases stand the pressure? And can't it still be better to store large things as plain files and put just their names in the database? File systems were designed for such kind of job after all, unlike RDBMS. I've been thinking about this exact same problem. There's another drawback in storing files in the database BTW: They're not directly accessible from the file system. To illustrate, I was looking into storing images for a website into the database. It's much easier if those images are available to the web-server directly instead of having to go through a script that reads the image file from the database and streams the bytes to the client. What I came up with was to create a file system layer that needs to go through the database to be able to manipulate files. It's still a file system, so files are available, but the database gets to check its constraints against those operations as well and can throw an error that prevents the file-system operation from being performed. Apparently something like this shouldn't be too hard to implement using FuseFS. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a3388e3759153496917459! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] accessing anyarray elements
Michael Glaesemann writes: > I'd like to be able to access individual elements of anyarray, > treating them as type anyelement to take advantage of the > polymorphism. Using pg_stats.histogram_bounds as a convenient example > of an anyelement array, here's an example of the issue I'm running into. The problem with histogram_bounds is that there isn't any way to infer an element type for it in the abstract, and thus also no way to know what type anyelement is. When looking at an individual row you can know that it must have the type of the associated column and explain that to the parser via an explicit cast, but there's pretty much no hope of having that happen automagically. There are a few other problems, like array columns --- the contents of pg_statistic for them is actually an array of arrays, which simply has not got a representation in our type system. So you're pretty much out of luck. I think the only meaningful thing you can do with it in SQL is cast to text. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
