pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT INTO relational tables
7+ messages / 4 participants
[nested] [flat]
* INSERT INTO relational tables
@ 2007-12-07 13:22 Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
2007-12-07 15:16 ` Re: INSERT INTO relational tables Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 7+ messages in thread
From: Stefan Scheidegger @ 2007-12-07 13:22 UTC (permalink / raw)
To: pgsql-sql
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
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO relational tables
2007-12-07 13:22 INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
@ 2007-12-07 15:16 ` Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2007-12-07 15:16 UTC (permalink / raw)
To: pgsql-sql
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 the actual 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°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO relational tables
@ 2007-12-09 07:50 Steve Midgley <public@misuse.org>
2007-12-10 10:42 ` Re: INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Steve Midgley @ 2007-12-09 07:50 UTC (permalink / raw)
To: pgsql-sql-owner@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO relational tables
2007-12-09 07:50 Re: INSERT INTO relational tables Steve Midgley <public@misuse.org>
@ 2007-12-10 10:42 ` Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
2007-12-10 11:08 ` Re: INSERT INTO relational tables A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stefan Scheidegger @ 2007-12-10 10:42 UTC (permalink / raw)
To: pgsql-sql
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 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 to MRS. 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 INSERT statements 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO relational tables
2007-12-09 07:50 Re: INSERT INTO relational tables Steve Midgley <public@misuse.org>
2007-12-10 10:42 ` Re: INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
@ 2007-12-10 11:08 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-10 11:29 ` Re: INSERT INTO relational tables A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-12-10 11:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO relational tables
2007-12-09 07:50 Re: INSERT INTO relational tables Steve Midgley <public@misuse.org>
2007-12-10 10:42 ` Re: INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
2007-12-10 11:08 ` Re: INSERT INTO relational tables A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-12-10 11:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-10 13:21 ` Re: INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-12-10 11:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO relational tables
2007-12-09 07:50 Re: INSERT INTO relational tables Steve Midgley <public@misuse.org>
2007-12-10 10:42 ` Re: INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
2007-12-10 11:08 ` Re: INSERT INTO relational tables A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-10 11:29 ` Re: INSERT INTO relational tables A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-12-10 13:21 ` Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
0 siblings, 0 replies; 7+ messages in thread
From: Stefan Scheidegger @ 2007-12-10 13:21 UTC (permalink / raw)
To: pgsql-sql
-------- 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-12-10 13:21 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-07 13:22 INSERT INTO relational tables Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
2007-12-07 15:16 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-12-09 07:50 Re: INSERT INTO relational tables Steve Midgley <public@misuse.org>
2007-12-10 10:42 ` Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
2007-12-10 11:08 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-10 11:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-10 13:21 ` Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox