Thread: INSERT INTO relational tables

Hi all

I'm new to SQL and I'm facing a problem I can't find any information about (google, forums). By now I wonder if I
understoodsomething wrong about relational DBs.
 

An example to explain my Problem:
Lets say I have a table containing information about the customer (name, address, ...) and about his order (pieces,
product-name,price). Because one customer can order several products I split the table into two relational tables to
preventredundancy:
 

tbl_customer (cust_id, cust_name, cust_address)
and
tbl_order (ord_pieces, ord_productname, ord_price, ord_customer REFERENCES tbl_customer(cust_id))

Now I want to insert several new customers with several orders each at once. If I had all information in one table,
thiswould be easy with something like:
 

INSERT INTO tbl_customerorders (name, address, pieces, porductname, price) VALUES ('MR. X', '1st street', 3,
't-shirts',30), ('MR. X', '1st street', 5, 'books', 50),  ('MRS. Y', '2nd street', 1, 't-shirt', 10),...
 

But how can I do this in one query if I split the table? I can add one new customer, get his ID with curval() and then
addhis orders. But this won’t work if I want to add several customers at once.
 

To read this information I can do a query with the argument WHERE cust_id=ord_customer. I can create a VIEW doing this
soI can read the data as if it was stored in only one table. But is there in posgres/sql an abstraction-layer that
allowsme to insert as if the information was stored in one table? (Something like a VIEW that provides INSERT, UPDATE,
…and automatically inserts the referenced ID.)
 

Thanks for any help!
Stefan


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

Stefan Scheidegger <Stefan.Scheidegger@gmx.net> schrieb:
> An example to explain my Problem: Lets say I have a table containing
> information about the customer (name, address, ...) and about his
> order (pieces, product-name, price). Because one customer can order
> several products I split the table into two relational tables to
> prevent redundancy:
> 
> tbl_customer (cust_id, cust_name, cust_address) and tbl_order
> (ord_pieces, ord_productname, ord_price, ord_customer REFERENCES
> tbl_customer(cust_id))
> 
> Now I want to insert several new customers with several orders each at
> once. If I had all information in one table, this would be easy with
> something like:
> 
> INSERT INTO tbl_customerorders (name, address, pieces, porductname,
> price) VALUES ('MR. X', '1st street', 3, 't-shirts', 30), ('MR. X',
> '1st street', 5, 'books', 50),  ('MRS. Y', '2nd street', 1, 't-shirt',
> 10),...
> 
> But how can I do this in one query if I split the table? I can add one
> new customer, get his ID with curval() and then add his orders. But
> this won???t work if I want to add several customers at once.

There are any ways:
- use a special import table like your last example, insert your data in this table and use later regular SQL to fill
theactual tables.
 
- create a own function with parameters like your last SQL. This function can do the job (check if customer exists, if
no,create them, insert the actual order)
 
- You can create a special VIEW with RULEs on INSERT and this RULEs can do the job.
- Maybe there are other solutions...


> data as if it was stored in only one table. But is there in
> posgres/sql an abstraction-layer that allows me to insert as if the
> information was stored in one table? (Something like a VIEW that
> provides INSERT, UPDATE, ??? and automatically inserts the referenced
> ID.)

You can create such an abstraction layer.


Andreas (from germany...)
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

At 09:23 AM 12/7/2007, pgsql-sql-owner@postgresql.org wrote:
>Date: Fri, 07 Dec 2007 14:22:26 +0100
>From: "Stefan Scheidegger" <Stefan.Scheidegger@gmx.net>
>To: pgsql-sql@postgresql.org
>Subject: INSERT INTO relational tables
>Message-ID: <20071207132226.281710@gmx.net>
>
>Hi all
>
>I'm new to SQL and I'm facing a problem I can't
>find any information about (google, forums). By
>now I wonder if I understood something wrong about relational DBs.
>
>An example to explain my Problem:
>Lets say I have a table containing information
>about the customer (name, address, ...) and
>about his order (pieces, product-name, price).
>Because one customer can order several products
>I split the table into two relational tables to prevent redundancy:
>
>tbl_customer (cust_id, cust_name, cust_address)
>and
>tbl_order (ord_pieces, ord_productname,
>ord_price, ord_customer REFERENCES tbl_customer(cust_id))
>
>Now I want to insert several new customers with
>several orders each at once. If I had all
>information in one table, this would be easy with something like:
>
>INSERT INTO tbl_customerorders (name, address,
>pieces, porductname, price) VALUES ('MR. X',
>'1st street', 3, 't-shirts', 30), ('MR. X', '1st
>street', 5, 'books', 50),  ('MRS. Y', '2nd
>street', 1, 't-shirt', 10),...
>
>But how can I do this in one query if I split
>the table? I can add one new customer, get his
>ID with curval() and then add his orders. But
>this won’t work if I want to add several customers at once.
>
>To read this information I can do a query with
>the argument WHERE cust_id=ord_customer. I can
>create a VIEW doing this so I can read the data
>as if it was stored in only one table. But is
>there in posgres/sql an abstraction-layer that
>allows me to insert as if the information was
>stored in one table? (Something like a VIEW that
>provides INSERT, UPDATE, … and automatically
>inserts the referenced ID.)
>
>Thanks for any help!
>Stefan

Andrea has given you some "deep" answers (i.e.
smarter than what I'm going to say) but since you
say you're a newbie to SQL maybe this will help.

Perhaps all you need is to wrap your entire set
of statements into a transaction
(http://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html)
to ensure that your entires are "acid"
(http://databases.about.com/od/specificproducts/a/acid.htm).

BEGIN
insert tbl_customer...;
insert tbl_order...;
insert tbl_order...;
END

It's not clear whether you're looking for syntax
simplicity or referential integrity (or both). If
syntax is your need, then Andreas has given some good ideas.

As another thought about syntax enhancement:
there are lots of object-relational mappers out
there that let you model your relations in a
higher level language (I use Ruby and
ActiveRecord which are easy to write and learn,
but Java, .NET, etc all have great stuff). Then
you just build your "entities" in the domain
specific language and it handles all the id
inserts and relational mapping for you. They can
even handle mapping many-to-many joined entities,
if you're careful in setting it up.

I hope this is helpful,

Steve

Thanks for your hints so far.

I'm looking for both syntax simplicity and referential integrity. I'm using c++ with libpqxx, but I didn't find a good
object-relationalmapper. And I'm too lazy to implement a whole persistency layer as I don't have a huge DB with many
relations.

I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I created the VIEW composing my table with customers and
orders:

CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer, tbl_order WHERE cust_id = ord_customer;

Then I added a rule for the inserting:
CREATE RULE rl_customer_insert ASON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO tbl_customer VALUES (DEFAULT,
new.cust_name,new.cust_address);  INSERT INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname, NEW.ord_price,
currval('"tbl_cusomer_cust_id_seq"'));
);

But this results in the same problem: It works fine if I insert just one new customer with one new order. But if I want
toinsert several new entries:
 

INSERT INTO vw_customer_insert(cust_name, cust_address, ord_pieces, ord_productname, ord_price)VALUES ), (‘MR. X’, ‘1st
street’,5, ‘books’, 50),  (‘MRS. Y’, ‘2nd street’, 1, ‘t-shirt’, 10);
 

This doesn't work correctly; all orders are now related to the last customer. In this example, both orders are related
toMRS. Y because I use currval() in my insert rule.
 

Do you see any solution for this? I thought that the performance of this would be better than INSERTing to
tbl_customer,fetching the ID and then do several INSERTS to tbl_order in c++. But actually I tend to do it with several
INSERTstatements in one transaction, as Steve proposed. 
 

Cheers Stefan


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

am  Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger folgendes:
> Thanks for your hints so far.
> 
> I'm looking for both syntax simplicity and referential integrity. I'm
> using c++ with libpqxx, but I didn't find a good object-relational
> mapper. And I'm too lazy to implement a whole persistency layer as I
> don't have a huge DB with many relations.
> 
> I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I
> created the VIEW composing my table with customers and orders:
> 
> CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer,
> tbl_order WHERE cust_id = ord_customer;
> 
> Then I added a rule for the inserting: CREATE RULE rl_customer_insert
> AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO
> tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT
> INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
> NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"')););
> 
> But this results in the same problem: It works fine if I insert just
> one new customer with one new order. But if I want to insert several
> new entries:

Insert the new customer only into the table if this customer doesn't
exist there. If the customer exist in the table, obtain the id for this
customer.



simplified:

- detect, if the customer exists yes: obtain the id as id no: insert and use currval() as id
- insert the order with the id


I would write a function for this.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

am  Mon, dem 10.12.2007, um 12:08:48 +0100 mailte A. Kretschmer folgendes:
> am  Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger folgendes:
> > Thanks for your hints so far.
> > 
> > I'm looking for both syntax simplicity and referential integrity. I'm
> > using c++ with libpqxx, but I didn't find a good object-relational
> > mapper. And I'm too lazy to implement a whole persistency layer as I
> > don't have a huge DB with many relations.
> > 
> > I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I
> > created the VIEW composing my table with customers and orders:
> > 
> > CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer,
> > tbl_order WHERE cust_id = ord_customer;
> > 
> > Then I added a rule for the inserting: CREATE RULE rl_customer_insert
> > AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO
> > tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT
> > INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
> > NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"')););
> > 
> > But this results in the same problem: It works fine if I insert just
> > one new customer with one new order. But if I want to insert several
> > new entries:
> 
> Insert the new customer only into the table if this customer doesn't
> exist there. If the customer exist in the table, obtain the id for this
> customer.
> 
> 
> 
> simplified:
> 
> - detect, if the customer exists
>   yes: obtain the id as id
>   no: insert and use currval() as id
> - insert the order with the id
> 
> 
> I would write a function for this.

a little example:

test=# create table customer (id serial primary key, name text);
NOTICE:  CREATE TABLE will create implicit sequence "customer_id_seq" for serial column "customer.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
CREATE TABLE
test=*# create table orders (customer int references customer, val int);
CREATE TABLE
test=*# create or replace function orders_insert (text, int) returns int
as $$declare i int;begin select into i id from customer where name=$1;
if i is null then insert into customer (name) values ($1); select into i
currval('customer_id_seq'); end if; insert into orders values (i,$2);
return i; end; $$language plpgsql;
CREATE FUNCTION
test=*# select * from customer;id | name
----+------
(0 rows)

test=*# select * from orders ;customer | val
----------+-----
(0 rows)

test=*# select orders_insert('foo',1);orders_insert
---------------            1
(1 row)

test=*# select orders_insert('foo',2);orders_insert
---------------            1
(1 row)

test=*# select orders_insert('foo',3);orders_insert
---------------            1
(1 row)

test=*# select orders_insert('bar',4);orders_insert
---------------            2
(1 row)

test=*# select * from customer;id | name
----+------ 1 | foo 2 | bar
(2 rows)

test=*# select * from orders ;customer | val
----------+-----       1 |   1       1 |   2       1 |   3       2 |   4
(4 rows)




HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-------- Original-Nachricht --------
> Datum: Mon, 10 Dec 2007 12:29:15 +0100
> Von: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] INSERT INTO relational tables

> am  Mon, dem 10.12.2007, um 12:08:48 +0100 mailte A. Kretschmer folgendes:
> > am  Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger
> folgendes:
> > > Thanks for your hints so far.
> > > 
> > > I'm looking for both syntax simplicity and referential integrity. I'm
> > > using c++ with libpqxx, but I didn't find a good object-relational
> > > mapper. And I'm too lazy to implement a whole persistency layer as I
> > > don't have a huge DB with many relations.
> > > 
> > > I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I
> > > created the VIEW composing my table with customers and orders:
> > > 
> > > CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer,
> > > tbl_order WHERE cust_id = ord_customer;
> > > 
> > > Then I added a rule for the inserting: CREATE RULE rl_customer_insert
> > > AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO
> > > tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT
> > > INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
> > > NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"')););
> > > 
> > > But this results in the same problem: It works fine if I insert just
> > > one new customer with one new order. But if I want to insert several
> > > new entries:
> > 
> > Insert the new customer only into the table if this customer doesn't
> > exist there. If the customer exist in the table, obtain the id for this
> > customer.
> > 
> > 
> > 
> > simplified:
> > 
> > - detect, if the customer exists
> >   yes: obtain the id as id
> >   no: insert and use currval() as id
> > - insert the order with the id
> > 
> > 
> > I would write a function for this.
> 
> a little example:
> 
> test=# create table customer (id serial primary key, name text);
> NOTICE:  CREATE TABLE will create implicit sequence "customer_id_seq" for
> serial column "customer.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "customer_pkey" for table "customer"
> CREATE TABLE
> test=*# create table orders (customer int references customer, val int);
> CREATE TABLE
> test=*# create or replace function orders_insert (text, int) returns int
> as $$declare i int;begin select into i id from customer where name=$1;
> if i is null then insert into customer (name) values ($1); select into i
> currval('customer_id_seq'); end if; insert into orders values (i,$2);
> return i; end; $$language plpgsql;
> CREATE FUNCTION
> test=*# select * from customer;
>  id | name
> ----+------
> (0 rows)
> 
> test=*# select * from orders ;
>  customer | val
> ----------+-----
> (0 rows)
> 
> test=*# select orders_insert('foo',1);
>  orders_insert
> ---------------
>              1
> (1 row)
> 
> test=*# select orders_insert('foo',2);
>  orders_insert
> ---------------
>              1
> (1 row)
> 
> test=*# select orders_insert('foo',3);
>  orders_insert
> ---------------
>              1
> (1 row)
> 
> test=*# select orders_insert('bar',4);
>  orders_insert
> ---------------
>              2
> (1 row)
> 
> test=*# select * from customer;
>  id | name
> ----+------
>   1 | foo
>   2 | bar
> (2 rows)
> 
> test=*# select * from orders ;
>  customer | val
> ----------+-----
>         1 |   1
>         1 |   2
>         1 |   3
>         2 |   4
> (4 rows)
> 


Thanks a lot, this is exactly what I was looking for.

I realize I need to learn more about functions in SQL. Hopefully my questions will be more sophisticated next time ;-)


Greets Stefan


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger