Re: [SQL] May I have an assistance on CREATE TABLE Command
I think (one of) the point(s) of id fields is not to change them. You can update the region_name field (eg a correct a misspelling), but the id stays the same. That way the district stays connected to the same region. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>> Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara
Re: [SQL] May I have an assistance on CREATE TABLE Command
Thank you ! But I think that there is a solution. If it happens that you have the following data in your tables REGION -- region_id | region_name -- 11| Dodoma 22| Tabora 99 | Dar es Salaam THIS ROW WAS SUPPOSED TO BE: '33', 'Dar es Salaam' DISTRICT dist_id | dist_name | region_id 001 | Kongwa | 11 002 | Ilala | 99 003 | Temeke |99 003 | Kinondoni | 99 For this UPDATE I wanted, when I change the region _id from '99' to '33' of the last ROW in REGION table AUTOMATICALLY to change the last three ROWS of the DISTRICT table which reference to '99', 'Dar es Salaam'. If I do this, I will get the error message "You can not change region_id other tables are reference to it. HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY) ---ORGINAL MESSAGE--- I think (one of) the point(s) of id fields is not to change them. You can update the region_name field (eg a correct a misspelling), but the id stays the same. That way the district stays connected to the same region. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>> Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara
Re: [SQL] May I have an assistance on CREATE TABLE Command
James Kitambara wrote: > For this UPDATE I wanted, when I change the region _id from '99' to > '33' of the last ROW in REGION table AUTOMATICALLY to change the > last three ROWS of the DISTRICT table which reference to '99', 'Dar > es Salaam'. > > If I do this, I will get the error message "You can not change > region_id other tables are reference to it. > > HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY) When you define your foreign key mark it "ON UPDATE CASCADE" (there is a similar option for ON DELETE). See manuals for details. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] May I have an assistance on CREATE TABLE Command
The idea of id's is that they are meaningless, so saying "this row was supposed to be 33" is senseless. If you want Dar es Salaam to be 33 because eg it's the postal code, then add a column postal_code to your region table but keep the id to make the reference. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 11:13 >>> Thank you ! But I think that there is a solution. If it happens that you have the following data in your tables REGION -- region_id | region_name -- 11| Dodoma 22| Tabora 99| Dar es Salaam THIS ROW WAS SUPPOSED TO BE: '33', 'Dar es Salaam' DISTRICT dist_id | dist_name |region_id 001 | Kongwa |11 002 | Ilala|99 003 | Temeke |99 003 | Kinondoni |99 For this UPDATE I wanted, when I change the region _id from '99' to '33' of the last ROW in REGION table AUTOMATICALLY to change the last three ROWS of the DISTRICT table which reference to '99', 'Dar es Salaam'. If I do this, I will get the error message "You can not change region_id other tables are reference to it. HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY) ---ORGINAL MESSAGE--- I think (one of) the point(s) of id fields is not to change them. You can update the region_name field (eg a correct a misspelling), but the id stays the same. That way the district stays connected to the same region. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>> Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara
Re: [SQL] surrogate vs natural primary keys
On Tue, 16 Sep 2008 20:34:51 -0600, "Scott Marlowe" <[EMAIL PROTECTED]> wrote: [...] > create table t2 ( > d1 varchar(200), > d2 int8, > d3 varchar(1000), > foreign key t2_fk references t1(c1,c2) ); Thanks Scott, I guess you meant: CREATE TABLE t2 ( d1 varchar(200), d2 int8, d3 varchar(1000), PRIMARY KEY (d1, d2) FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) ); But this makes it difficult to work with t2 because it has 2 fields that are the same as in t1. Isn't it better to just use a surrogate key and use a single field in t2, thereby avoiding repeating multiple pieces of information? Thanks, -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] surrogate vs natural primary keys
On Wed, Sep 17, 2008 at 7:20 AM, Seb <[EMAIL PROTECTED]> wrote: > On Tue, 16 Sep 2008 20:34:51 -0600, > "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > > [...] > >> create table t2 ( >> d1 varchar(200), >> d2 int8, >> d3 varchar(1000), >> foreign key t2_fk references t1(c1,c2) ); > > Thanks Scott, I guess you meant: > > CREATE TABLE t2 ( >d1 varchar(200), >d2 int8, >d3 varchar(1000), >PRIMARY KEY (d1, d2) >FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) ); > > But this makes it difficult to work with t2 because it has 2 fields that > are the same as in t1. Isn't it better to just use a surrogate key and > use a single field in t2, thereby avoiding repeating multiple pieces of > information? Yes and no. If you're gonna hit table t2 a lot by itself, then it's more efficient to have the data there in t2 and not have to join to t1 to get it. There are always use cases that go either way in this kind of situation. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger Procedure Error: NEW used in query that is not in a rule
HI,
On Sat, Aug 11, 2007 at 9:07 PM, hubert depesz lubaczewski
<[EMAIL PROTECTED]> wrote:
> On Sat, Aug 11, 2007 at 02:45:09AM -0500, Javier Fonseca V. wrote:
>> I think that it's working alright except for the next line:
>
> doing this in plpgsql is very complicated (or even impossible assuming
> that any table can have the same trigger). i would rather suggest using
> pl/perl - writing something like this in pl/perl is very simple.
>
I am in the same situation where I would like to execute a query similar to
EXECUTE 'INSERT INTO ' || quote_ident(SOMEDYNAMICNTABLENAME) || '
SELECT new.*';
I've looked at the plperl documentation, and experimented a bit, but
I'm not even sure how to start this in pl/perl. I hoped to extract
columns from $_TD->{new} but it doesn't seem to work.
Would you have a little example on how you would do it?
Thanks in advance!
Raph
> depesz
>
> --
> quicksil1er: "postgres is excellent, but like any DB it requires a
> highly paid DBA. here's my CV!" :)
> http://www.depesz.com/ - blog dla ciebie (i moje CV)
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] surrogate vs natural primary keys
On Wed, Sep 17, 2008 at 7:45 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote: >> CREATE TABLE t2 ( >>d1 varchar(200), >>d2 int8, >>d3 varchar(1000), >>PRIMARY KEY (d1, d2) >>FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) ); >> >> thereby avoiding repeating multiple pieces of >> information? > > Yes and no. If you're gonna hit table t2 a lot by itself, then it's > more efficient to have the data there in t2 and not have to join to t1 > to get it. There are always use cases that go either way in this kind > of situation. Also, doing this allows you to apply more constraints to T2 (if you ever wanted to add them). For example, what if you only wanted to allow a sub-set of T1(c1,c2) in T2(d1,d2), you could use a check constraint to enforce this more restrictive relationship. This wouldn't be possible (without adding custom triggers) if you only used a surrogate key. My experience is that many more validation constraints are possible if use natural keys are used. Whether this feature is a good thing or not is up to you. My opinion is that the database constraints are the last line of defense to ensure business rules and data integrity are not violated. Since I highly value the ability to enforce business rules using ordinary table DDL, I try to use natural keys as often as I can. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger Procedure Error: NEW used in query that is not in a rule
On Wed, Sep 17, 2008 at 5:45 PM, hubert depesz lubaczewski
<[EMAIL PROTECTED]> wrote:
> On Wed, Sep 17, 2008 at 05:08:39PM +0200, Raphael Bauduin wrote:
>> Would you have a little example on how you would do it?
>
> show us what you have done - it will be easier to find/fix/explain than
> to write code for you.
Well, I experimented a lot but didn't come to any useful result.
Actually I'm working on table partitioning as described at
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html , and
I wanted to write a trigger that would insert the data in the correct
table, and so I got the same problem with plpsql's NEW.* not usable in
a dynamically created query to be run by EXECUTE:
CREATE OR REPLACE FUNCTION part_test() RETURNS TRIGGER AS $$
DECLARE
current_time timestamp := now();
suffix text := date_part('month', now())||'_'||date_part('day', now()) ;
BEGIN
RAISE NOTICE '%', suffix;
execute 'insert into t1_'||suffix||' values( NEW.* )';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
I searched the archives here and after reading your previous mail in
this thread, I started to look at plperl, with which I have no
experience at all.
As $_TD{new}{column} gives the value of field column, I thought to
extract all columns from keys($_TD{new}), but it doesn't seem to see
$_TD{new} as a hash:
Type of arg 1 to keys must be hash (not hash element)
And that's where I'm at now.
Raph
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] prepared query plan did not update
Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? Thanks alot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Wednesday, September 17, 2008 2:55 PM To: pgsql-sql@postgresql.org Subject: [SQL] prepared query plan did not update Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? Thanks alot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql WHERE col1 AND col2 = '$1' ; Are you looking for both: col1 and col2 - to be equal to '$1'? Then it should be: WHERE col1 = '$1' AND col2 = '$1'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? WHERE col1 AND col2 = '$1' ; Are you looking for both: col1 and col2 - to be equal to '$1'? Then it should be: WHERE col1 = '$1' AND col2 = '$1'; Not exactly, col1 is boolean value WHERE col1 = true AND col2 = '$1'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
On Wed, 17 Sep 2008, Emi Lu wrote: > Good morning, > > I tried to use prepared query plan to update columns, but it did not > update at all. > > PREPARE pname(varchar) AS > UPDATE t1 > SETcol1 = false > WHERE col1 AND > col2 = '$1' ; I don't think you want those quotes in the second part of the where clause. I'm pretty sure that means you're comparing against the literal string with a dollar sign and one rather than the value given at execute time for $1. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
Stephan Szabo wrote: On Wed, 17 Sep 2008, Emi Lu wrote: Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; I don't think you want those quotes in the second part of the where clause. I'm pretty sure that means you're comparing against the literal string with a dollar sign and one rather than the value given at execute time for $1. Do you mean: PREPARE pname(varchar) AS UPDATE t1 SET col1 = false WHERE col1 AND col2 = $1 ; But still does not work? Strange, right? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
On Wed, 17 Sep 2008, Emi Lu wrote: > Stephan Szabo wrote: > > On Wed, 17 Sep 2008, Emi Lu wrote: > > > >> Good morning, > >> > >> I tried to use prepared query plan to update columns, but it did not > >> update at all. > >> > >> PREPARE pname(varchar) AS > >> UPDATE t1 > >> SETcol1 = false > >> WHERE col1 AND > >> col2 = '$1' ; > > > > I don't think you want those quotes in the second part of the where > > clause. I'm pretty sure that means you're comparing against the literal > > string with a dollar sign and one rather than the value given at execute > > time for $1. > > Do you mean: > > PREPARE pname(varchar) AS > UPDATE t1 > SET col1 = false > WHERE col1 AND >col2 = $1 ; > > But still does not work? Strange, right? I think we'll need to see a complete example with table definitions and sample data because it does appear to work for me in simple tests. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] May I have an assistance on CREATE TABLE Command
You could: INSERT INTO REGION VALUES (33, 'New Dar'); UPDATE DISTRICT SET region_id = 33 WHERE region_id = 99; DELETE FROM REGION WHERE region_id = 99; UPDATE REGION SET region_name = 'Dar es Salaam' WHERE region_id = 33; Of course, if there is no uniqueness constraint on region_name then you can just put the final region_name in the INSERT and you won't need to do the final UPDATE. This won't break any Foreign Keys. (been to Dodoma and Dar, but not Tabora - yet). Cheers, Bob Edwards. James Kitambara wrote: Thank you ! But I think that there is a solution. If it happens that you have the following data in your tables REGION -- region_id | region_name -- 11| Dodoma 22| Tabora 99| Dar es Salaam THIS ROW WAS SUPPOSED TO BE: '33', 'Dar es Salaam' DISTRICT dist_id | dist_name |region_id 001 | Kongwa |11 002 | Ilala|99 003 | Temeke |99 003 | Kinondoni |99 For this UPDATE I wanted, when I change the region _id from '99' to '33' of the last ROW in REGION table AUTOMATICALLY to change the last three ROWS of the DISTRICT table which reference to '99', 'Dar es Salaam'. If I do this, I will get the error message "You can not change region_id other tables are reference to it. HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY) ---ORGINAL MESSAGE--- I think (one of) the point(s) of id fields is not to change them. You can update the region_name field (eg a correct a misspelling), but the id stays the same. That way the district stays connected to the same region. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>> Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] surrogate vs natural primary keys
To: pgsql-sql@postgresql.org From: Seb <[EMAIL PROTECTED]> Subject: Re: surrogate vs natural primary keys Date: Mon, 15 Sep 2008 17:56:31 -0500 Organization: Church of Emacs Lines: 20 Message-ID: <[EMAIL PROTECTED]> References: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> X-Archive-Number: 200809/101 X-Sequence-Number: 31553 On Mon, 15 Sep 2008 16:45:08 -0600, "Scott Marlowe" <[EMAIL PROTECTED]> wrote: [...] > I think this question is a lot like "how large should I set > shared_buffers?" There's lots of different answers based on how you > are using your data. Yes, this is precisely what I'm after: *criteria* to help me decide which approach to take for different scenarios. Such guidance is what seems to be lacking from most of the discussions I've seen on the subject. It's hard to distill this information when most of the discussion is centered on advocating one or the other approach. I think Scott and others have laid out the main ideas in a very cool-headed way already, but here's my follow-on input: I agree with Andrew Sullivan that using industry standard id's as your primary key can be problematic. But I do sometimes apply unique indices to such "industry standard" columns to ensure they are in fact unique and can be a surrogate for the "real" integer/serial primary key. As a rule, I have decided to stay away from "meaningful" (natural) primary keys for these reasons: 1) They sometimes change b/c of business rule changes, forcing technical changes to the relationship model, when only internal table schema changes should be required to support the new business requirements. 2) Generating arbitrary/surrogate keys is easier b/c you can use sequence generators. (When creating a new record, I have to figure out the value of a meaningful column before saving the record which sometimes I don't want to do!) 3) Surrogate keys are guaranteed unique regardless of semantic content of the table. 4) All tables can all join to each other in the same ways: property.id holds the same data type as contact.id. All id fields are the same in type/format. I think there's even a reasonable argument for "globally unique" surrogate keys: all keys for any table use the same sequence of id's. I implemented a system in the 90's that used globally unique id's and it opened up some interesting solutions that I wouldn't have thought of when I started the project (self joins were the same as foreign joins since the id's in both entities were guaranteed unique). I've heard some people argue the use of GUID's for id's but I've been too scared to try that in a real system. Sequential, arbitrary primary keys (as surrogate keys) are predictable though. So if you share those keys with the public (via URL's for example), then competitors can learn information about your business (how fast keys are generated for a certain table for example). That's an argument for random, arbitrary primary keys though, not for compound/meaningful keys. I think natural or compound keys make more sense to DBA's and let you implement some kinds of database solutions more quickly. All in all, I don't really understand the merits of natural keys outside of data warehouse applications. In data warehouses, in my experience, compound natural keys just end up turning into fact tables! :) In summary: I've never heard someone say they've been bitten by using an arbitrary surrogate key system, but I myself have been bitten and have heard lots of stories of problems when using natural keys. I hope this helps some, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
