Postgres Pro
Downloads
Home   >   mailing lists

Thread: Creating schema in postgres

Creating schema in postgres

From
Jyoti Patil
Date:
Hi,

I would like to know how can we create a schema in postgres.

Many thanks
Jyoti.


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

Re: Creating schema in postgres

From
Stephan Szabo
Date:
On Wed, 28 Nov 2001, Jyoti Patil wrote:

> Hi,
>
> I would like to know how can we create a schema in postgres.

You can't yet.  Hopefully it'll be in 7.3.

Re: Creating schema in postgres

From
"Andrew G. Hammond"
Date:
-----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-----