pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule Error
6+ messages / 4 participants
[nested] [flat]
* Rule Error
@ 2007-10-04 11:22 Hengky Lie <hengkyliwandouw@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Hengky Lie @ 2007-10-04 11:22 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
I have problem with rule and tried several times to solve it but not yet
success. Hope someone can help me.
I have 2 tables : tblmasdbt and tblmasgl.
I want on every record insertion in tblmasdbt, that record also
automatically insert into tblmasdbt. I need only 2 related field.
So I create rule like this
--------------- SQL ---------------
CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
But I always get this error :
---------- ERROR MESSAGE ----------
ERROR: column "kodegl" of relation "tblmasgl" does not exist
Here is the Table Structure
---------------------------
CREATE TABLE "public"."tblmasgl" (
"KODEGL" VARCHAR(15) NOT NULL,
"NAMAREK" VARCHAR(50),
"GOLONGAN" VARCHAR(10),
"AWAL" DOUBLE PRECISION DEFAULT 0,
"Operator" VARCHAR(3),
CONSTRAINT "tblmasgl_pkey" PRIMARY KEY("KODEGL"),
CONSTRAINT "tblmasgl_fk" FOREIGN KEY ("KODEGL")
REFERENCES "public"."tbltragl"("KODEGL")
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
CREATE TABLE "public"."tblmasdbt" (
"KODEGL" VARCHAR(15) NOT NULL,
"NAMAREK" VARCHAR(50),
"ALAMAT" VARCHAR(75),
"Telp" VARCHAR(50),
"Facs" VARCHAR(50),
"KOTA" VARCHAR(30),
"HP" VARCHAR(20),
"Plafond" DOUBLE PRECISION DEFAULT 0,
"Operator" VARCHAR(3),
"SALDOAWAL" DOUBLE PRECISION DEFAULT 0,
CONSTRAINT "tblmasdbt_pkey" PRIMARY KEY("KODEGL")
) WITHOUT OIDS;
Hope someone could help me. Thanks a lot
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rule Error
@ 2007-10-04 12:53 Richard Huxton <dev@archonet.com>
parent: Hengky Lie <hengkyliwandouw@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2007-10-04 12:53 UTC (permalink / raw)
To: Hengky Lie <hengkyliwandouw@gmail.com>; +Cc: pgsql-sql
Hengky Lie wrote:
>
> I have 2 tables : tblmasdbt and tblmasgl.
>
> I want on every record insertion in tblmasdbt, that record also
> automatically insert into tblmasdbt. I need only 2 related field.
You probably want triggers rather than rules, but anyway.
> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
> But I always get this error :
>
> ERROR: column "kodegl" of relation "tblmasgl" does not exist
There is not a column called kodegl on table tblmasgl.
> Here is the Table Structure
> CREATE TABLE "public"."tblmasgl" (
> "KODEGL" VARCHAR(15) NOT NULL,
There you go - you double-quoted the column-name when creating the
table. That means that it is literally "KODEGL" and will not match
kodegl or KoDeGl or any other combination of upper and lower case.
If you double-quote column-names when you create a table you'll want to
double-quote them every time you use them too.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rule Error
@ 2007-10-04 12:57 Bart Degryse <Bart.Degryse@indicator.be>
parent: Hengky Lie <hengkyliwandouw@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Bart Degryse @ 2007-10-04 12:57 UTC (permalink / raw)
To: pgsql-sql
You have defined the fields KODEGL and NAMAREK as uppercased field names.
In your rule you refer to an unquoted field KODEGL twice and twice to an unquoted field NAMAREK.
Default behaviour of PostgreSQL for unquoted fieldnames is to lowercase them.
As such these fields effectively don't exist in your tables.
Try
CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
DO (insert into tblmasgl ("KODEGL","NAMAREK") VALUES (new."KODEGL", new."NAMAREK"));
>>> "Hengky Lie" <hengkyliwandouw@gmail.com> 2007-10-04 13:22 >>>
Dear Friends,
I have problem with rule and tried several times to solve it but not yet success. Hope someone can help me.
I have 2 tables : tblmasdbt and tblmasgl.
I want on every record insertion in tblmasdbt, that record also automatically insert into tblmasdbt. I need only 2 related field.
So I create rule like this
--------------- SQL ---------------
CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
But I always get this error :
---------- ERROR MESSAGE ----------
ERROR: column "kodegl" of relation "tblmasgl" does not exist
Here is the Table Structure
---------------------------
CREATE TABLE "public"."tblmasgl" (
"KODEGL" VARCHAR(15) NOT NULL,
"NAMAREK" VARCHAR(50),
"GOLONGAN" VARCHAR(10),
"AWAL" DOUBLE PRECISION DEFAULT 0,
"Operator" VARCHAR(3),
CONSTRAINT "tblmasgl_pkey" PRIMARY KEY("KODEGL"),
CONSTRAINT "tblmasgl_fk" FOREIGN KEY ("KODEGL")
REFERENCES "public"."tbltragl"("KODEGL")
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
CREATE TABLE "public"."tblmasdbt" (
"KODEGL" VARCHAR(15) NOT NULL,
"NAMAREK" VARCHAR(50),
"ALAMAT" VARCHAR(75),
"Telp" VARCHAR(50),
"Facs" VARCHAR(50),
"KOTA" VARCHAR(30),
"HP" VARCHAR(20),
"Plafond" DOUBLE PRECISION DEFAULT 0,
"Operator" VARCHAR(3),
"SALDOAWAL" DOUBLE PRECISION DEFAULT 0,
CONSTRAINT "tblmasdbt_pkey" PRIMARY KEY("KODEGL")
) WITHOUT OIDS;
Hope someone could help me. Thanks a lot
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rule Error
@ 2007-10-04 13:00 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Hengky Lie <hengkyliwandouw@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2007-10-04 13:00 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 04.10.2007, um 19:22:32 +0800 mailte Hengky Lie folgendes:
> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
>
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
>
>
>
> But I always get this error :
>
>
> ERROR: column "kodegl" of relation "tblmasgl" does not exist
>
>
> Here is the Table Structure
>
> CREATE TABLE "public"."tblmasgl" (
>
> "KODEGL" VARCHAR(15) NOT NULL,
Okay, you need to quote the column-name with " since they are in
uppercase.
DO (insert into tblmasgl ("KODEGL","NAMAREK") and maybe also
new."KODEGL" and new."NAMAREK".
Try it and tell if you have success.
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] 6+ messages in thread
* Re: Rule Error
@ 2007-10-04 23:59 Hengky Lie <hengkyliwandouw@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Hengky Lie @ 2007-10-04 23:59 UTC (permalink / raw)
To: pgsql-sql
Yes, it works now ! Wow, the problem is in the field name. Changed it to
lowercase solved the problem. Thank you to all ho give me this advice.
But now I have another question regarding to this field, what command I can
use in UPDATE RULE to make these 2 fields (KODEGL and NAMAREK) keep syncron
between these 2 tables (tblmasdbt and tblmasgl) ?
Thank you so much.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of A. Kretschmer
Sent: 04 Oktober 2007 21:00
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Rule Error
am Thu, dem 04.10.2007, um 19:22:32 +0800 mailte Hengky Lie folgendes:
> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
>
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL,
new.NAMAREK));
>
>
>
> But I always get this error :
>
>
> ERROR: column "kodegl" of relation "tblmasgl" does not exist
>
>
> Here is the Table Structure
>
> CREATE TABLE "public"."tblmasgl" (
>
> "KODEGL" VARCHAR(15) NOT NULL,
Okay, you need to quote the column-name with " since they are in
uppercase.
DO (insert into tblmasgl ("KODEGL","NAMAREK") and maybe also
new."KODEGL" and new."NAMAREK".
Try it and tell if you have success.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rule Error
@ 2007-10-05 04:56 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2007-10-05 04:56 UTC (permalink / raw)
To: pgsql-sql
am Fri, dem 05.10.2007, um 7:16:06 +0800 mailte Hengky Lie folgendes:
> Yes, it works now ! Wow, the problem is in the field name. Changed it to
> lowercase solved the problem. Thank you to all ho give me this advice.
>
> But now I have another question regarding to this field, what command I can
> use in UPDATE RULE to make these 2 fields (KODEGL and NAMAREK) keep syncron
> between these 2 tables (tblmasdbt and tblmasgl) ?
As Richard suggested, use TRIGGER instead RULE, but okay.
A little example, i hope, it helps:
(2 little tables t1 and t2 and a UPDATE-RULE on t1 with a 'do also')
test=# create table t1 (id int, val int);
CREATE TABLE
test=*# create table t2 (id int, val int);
CREATE TABLE
test=*# create rule r1 as on update to t1 do also update t2 set val =
new.val where id=new.id;
CREATE RULE
test=*# commit;
COMMIT
test=# insert into t1 values (1,1);
INSERT 0 1
test=*# insert into t1 values (2,2);
INSERT 0 1
test=*# insert into t2 values (1,1);
INSERT 0 1
test=*# insert into t2 values (2,2);
INSERT 0 1
test=*# update t1 set val =10 where id=1;
UPDATE 1
test=*# select * from t2;
id | val
----+-----
2 | 2
1 | 10
(2 rows)
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] 6+ messages in thread
end of thread, other threads:[~2007-10-05 04:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-04 11:22 Rule Error Hengky Lie <hengkyliwandouw@gmail.com>
2007-10-04 12:53 ` Richard Huxton <dev@archonet.com>
2007-10-04 12:57 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-10-04 13:00 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-10-05 04:56 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-10-04 23:59 Re: Rule Error Hengky Lie <hengkyliwandouw@gmail.com>
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