Re: [GENERAL] Query that produces index information for a Table
I have been using PostgreSQL for a short while, but I have not had to use the pg_catalog tables before and the columns are a little cryptic to me. I think it ties to pg_class, but I am not sure how to relate them. Also, I have not had a chance to us the \dt function. Can you give me some pointers or point me to the docs? Thanks, Craig Boyd Tom Lane wrote: "Boyd, Craig" writes: I am trying to pull together some general information about indices (indexes?) for a particular table. I need the following: Index Name, Table Name, Column Name, Unique/Non-Unique, and ordinal position in the index. The information_schema.key_column_usage gets me most of the way there, I think, but does not tell me whether the index is unique and does not seem to differentiate between indices and other types of constraints. The information_schema gets you *none* of the way there, actually, because it's a creature of the SQL standard and indexes are outside the standard (yes, really). You can find out about unique constraints from the information_schema views, but not about non-unique indexes, nor even indexes that are unique but weren't created via unique-constraint syntax. If you want to know about all indexes, you'll need to get your hands dirty with looking at the PG system catalogs. I'd suggest looking at the queries psql generates for \dt (use psql -E to watch these) and then modifying them to suit your purposes. regards, tom lane -- I am using the free version of SPAMfighter. We are a community of 7 million users fighting spam. SPAMfighter has removed 1388 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
[GENERAL] Query that produces index information for a Table
Hello All, I am trying to pull together some general information about indices (indexes?) for a particular table. I need the following: Index Name, Table Name, Column Name, Unique/Non-Unique, and ordinal position in the index. The information_schema.key_column_usage gets me most of the way there, I think, but does not tell me whether the index is unique and does not seem to differentiate between indices and other types of constraints. I can't believe I am the first to ask this question so I am hoping someone can send me what they use to get this information. Thanks, Craigbert -- I am using the free version of SPAMfighter. We are a community of 7 million users fighting spam. SPAMfighter has removed 1387 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- 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] export a schema / import as new schema
Look here: http://sqlmanager.net/en/products/postgresql They aren't cheap, but they seem to work well. Thanks, Craig Boyd David Kerr wrote: On Fri, Aug 21, 2009 at 12:00:11PM -0700, Joshua D. Drake wrote: - On Fri, 2009-08-21 at 11:56 -0700, David Kerr wrote: - > Is there an easy way, that I'm missing, where I can export a schema from - > database A and then rename it on load into database B? - - pg_dump -s foo|psql bar Sorry, I wasn't clear. What I have is: [Database 1].[Schema 1]->[Data Set 1] [Database 2].[Schema 1]->[Data Set 2] What I want to do is: Export [Database 1].[Schema 1]->[Data Set 1] Import [Database 2].[Schema 2]->[Data Set 1] Leaving me with [Database 2].[Schema 1]-[Data Set 2] [Schema 2]-[Data Set 1] So that i can now compare Data Set 1 and Data Set 2 Currently, I'm creating the new schema in database 1 and then exporting that data into database 2, but i was hoping there was a better way. Thanks Dave -- 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] Schema diff tool?
We are on 7.3.0.1666. ODBC alter scripts do tend to be, um, ugly. When you do the CC are restricting the objects you CC? Try to keep it as minimal as possible. If I get some time over the weekend I will see what I can do. No promises though as it is already pretty booked. :) If I do get the time then I will send you the options xml and the steps I used to recreate. Thanks, Craig Boyd David Kerr wrote: we're on v7.2.8 there's no pg specific option so we've been using ODBC as the "database" type and the alter's it generates are just ugly. Dave Boyd, Craig wrote: What version of ERwin are you using? Thanks, Craig Boyd David Kerr wrote: Is there a default/standard (free) schema diff tool that's in use in the community? I'd like to be able to quickly identify new columns, data changes, new indexes, etc between 2 schema versions. (and then create an alter script for the original) We're using ERWin as our modeling tool, but it's diff's stink. Thanks Dave -- 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] Schema diff tool?
What version of ERwin are you using? Thanks, Craig Boyd David Kerr wrote: Is there a default/standard (free) schema diff tool that's in use in the community? I'd like to be able to quickly identify new columns, data changes, new indexes, etc between 2 schema versions. (and then create an alter script for the original) We're using ERWin as our modeling tool, but it's diff's stink. Thanks Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
