Thread: Tables

On Thu, Oct 20, 2005 at 09:37:07AM -0700, Bob Pawley wrote:
> I have table 1, a primary source, which generates a serial number
> to make each item unique.

Do you mean that the table has a serial column (which is just a
convenient way to declare an integer column that takes its default
value from a sequence)?  That's what one could infer from "generates
a serial number" but I'd like to make sure.

> I want to use this number to generate a row in table 2 linking
> the two rows and allowing specific information on each item to be
> developed..

What do you mean by "generate a row"?  Do you want to insert a row
into table 1, then use that row's sequence number in an insert into
table 2?  If so then see the nextval() and currval() functions.

http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html

You'll probably also want a foreign key constraint in table 2.

http://www.postgresql.org/docs/8.0/interactive/tutorial-fk.html
http://www.postgresql.org/docs/8.0/interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK

If that doesn't help then please provide more information.  An
example that illustrates what you're trying to do might be useful.

--
Michael Fuhr

On 10/20/05 12:37 PM, "Bob Pawley" <rjpawley@shaw.ca> wrote:

> I am new to databases.
>
> I have table 1, a primary source, which generates a serial number to make each
> item unique.
>
> I want to use this number to generate a row in table 2 linking the two rows
> and allowing specific information on each item to be developed..
>
> I have a number of books, including one specifically for Postgres.
>
> What I don't have is the language to look this function up.
>
> Concepts like JOIN appear to used to create views not new rows on other
> tables.

This was confusing to me at first, also.  There is no generic function to
create rows in two tables simultaneously.  One simply creates the row in the
first table and then creates rows in the second table in a second step.  The
"link" between the two tables is a single column that contains the same id.
See any of MANY online tutorials on SQL for an introduction or get a book on
SQL.  I found that I had to sit and type verbatim from multiple sources
before I really understood what was going on, so you may want to try that.

Google for SQL tutorial for starters.

Sean