pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Davis <list-pgsql-general@dynworks.com>
To: Dmitry Tkach <dmitry@openratings.com>
To: pgsql-general@postgresql.org
Subject: Re: A general database question!
Date: Thu, 21 Mar 2002 15:25:19 -0800
Message-ID: <200203212329.PAA17841@mail.ucsd.edu> (raw)
In-Reply-To: <3C9A4759.6000607@openratings.com>
References: <3C9A4759.6000607@openratings.com>

> I have a general database design question for you...
> Suppose, I have a database of entities, that have two attributes, say,
> location and name. Location is numerical and name is a varchar.
> I need to be able to search by both location and name (e.g. get all the
> entries whose location is 1 and name is John etc...).
> It is simple so far - I'd just create an an index on both location and
> name...
>

I am not quite understanding why a multi-column index won't work. Can you 
explain a little more?

Here is what I was thinking:
 create table entity(
	type int,
	location int,
	name varchar(100) not null,
 );
 create unique index my_index on entity(location,name);

It seems as though an "and" query would run quickly. It also seems as though 
if you needed to get a few records that were one of several names, a query 
such as the following would run quickly:
select * from entity where location = 5 and (name = 'joe' or name='joseph');

or are the aliases a seperate attribute? I am not clear on exactly where the 
alias comes in. 

Regards,
	Jeff



view thread (3+ messages)  latest in thread

Message-ID: <200203212329.PAA17841@mail.ucsd.edu>
Permalink:  ../200203212329.PAA17841@mail.ucsd.edu/
Also on:    postgresql.org/message-id/200203212329.PAA17841@mail.ucsd.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: list-pgsql-general@dynworks.com, dmitry@openratings.com, pgsql-general@postgresql.org
  Subject: Re: A general database question!
  In-Reply-To: <200203212329.PAA17841@mail.ucsd.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox