Re: table has many to many relationship with itself - how

SCassidy@overlandstorage.com wrote:

> Starting with this:
>
> create sequence languages_seq increment by 1;
> create table languages (
>   id integer primary key default nextval('languages_seq'),
>   language_name varchar(100)
> );

> (I like specifying my own sequence names, instead of using "serial",
> plus
> using a default this way lets me insert an integer directly, when
> necessary, or letting it default, but you can use serial, if you want).

You can always insert your own value into a SERIAL column.  From the
8.1 docs:

> The data types serial and bigserial are not true types, but merely a
> notational convenience for setting up unique identifier columns
> (similar to the AUTO_INCREMENT property supported by some other
> databases). In the current implementation, specifying
>
> CREATE TABLE tablename (
>     colname SERIAL
> );
>
> is equivalent to specifying:
>
> CREATE SEQUENCE tablename_colname_seq;
> CREATE TABLE tablename (
>     colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
> );

(http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-
SERIAL)

Your example above differs only in the sequence name (plus you have a
PK constraint, but you can do this on a SERIAL column too).

- John D. Burger
   MITRE