> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of amit sehas > Sent: Tuesday, August 14, 2012 12:55 PM > To: pgsql-sql@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx > Subject: Indexing question > > In SQL, given a table T, with two fields f1, f2, > > is it possible to create an index such that the same record is indexed in the > index, once with field f1 and once with field f2. (I am not looking for a > compound index in which the key would look like <f1, f2>, instead there > should be two entries in the index <f1> and <f2>). > > we have a few use cases for the above, perhaps we need to alter the > schema somehow to accommodate the above, > > any advice is greatly appreciated .. > > thanks > In short: No, you cannot create an index on T in the way you describe. You need to create a new table: TF, with columns {T(id), f}, and having rows 1 and 2 with the same T(id) value; An index over "f" on table TF will then contain both values. Slightly longer: This seems like a classic case of column duplication. I am assuming that the columns in question are, say, phone1 and phone2 an you want to be able to search by phone number without having to specify the two fields separately. The correct way to do this is to create a "phone" table and add a single line for each phone number you want to store (along with the corresponding FK value of the original table) - with possibly a "phone_type" column. If this is not what you are after then you should be more explicit in your requirements. Why is creating two separate indexes (on f1 and f2) not acceptable? If indeed you are dealing with variations of the above example you really want to consider modifying your schema to use two tables with a one-to-many relationship because the current scenario begs the question(s): "why only f1 and f2? Why isn't there an f3?". The idea is that there are generally 3 separate cardinalities {0, 1, >1}. Zero you ignore, 1 you generally put on the same table - though not always, and more-than-one you create a separate table and store multiple values as separate rows instead of as columns. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
