Re: Creating schema in postgres

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 28 05:35 am, Jyoti Patil wrote:
> I would like to know how can we create a schema in postgres.

There are graphical front end tools available that will do this for you, but
it all comes down to CREATE statements in SQL.  You can communicate dirrectly
with the database (in SQL) using the psql command line utility that comes
with the database.  To create a schema (in psql), you might do something like
the following:

- -- table to list people
CREATE TABLE person (id SERIAL PRIMARY KEY,
  given_name TEXT NOT NULL,
  surname TEXT NOT NULL,
  birth_date DATE CHECK (birth_date > 1960-01-01),  -- no old people allowed
  eye_colour TEXT );

- -- table to list friendships ( n to m relationship of person to person)
CREATE TABLE friend (id SERIAL PRIMARY KEY,
  person_a INTEGER REFERENCES person(id),
  person_b INTEGER REFERENCES person(id) );

- -- pets ( 1 to n relationship of people to pets)
CREATE TABLE pet (id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('cat', 'dog', 'bird', 'llama')),
  owner INTEGER REFERENCES person(id) );

And, to increase the performance of sorts and selects, create some indices on
the above tables:

CREATE INDEX p_a_idx ON person (given_name, surname);
CREATE INDEX p_b_idx ON person (surname, given_name);
CREATE INDEX f_a_idx ON friend (person_a);
CREATE INDEX f_b_idx ON friend (person_b);
CREATE INDEX pet_owner_idx ON pet(owner);

Anyway, I hope this is enough to get you pointed in the right dirrection.
Feel free to ask me for more details.

- --
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjwFE1AACgkQCT73CrRXhLGQhACfZPFhzCDoODJh/fdL7e36uozP
iL4An1WmIo6bm1Bysu7WHbpQQUZ1gM0w
=vFbY
-----END PGP SIGNATURE-----