[SQL] Rule Error
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
Re: [SQL] Rule Error
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Rule Error
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" <[EMAIL PROTECTED]> 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
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
Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?
Shane Ambler wrote: > Stephan Szabo wrote: >> On Tue, 2 Oct 2007, Jeff Frost wrote: >>> I expected these numbers to be in sync, but was suprised to see that the >>> sequence skips a values after every generate series. >>> >>> CREATE TABLE jefftest ( id serial, num int ); >>> INSERT INTO jefftest (num) values (generate_series(1,10)); >>> INSERT INTO jefftest (num) values (generate_series(11,20)); >>> INSERT INTO jefftest (num) values (generate_series(21,30)); >> It seems to do what you'd expect if you do >> INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); >> INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); >> INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); >> I tried a function that raises a notice and called it as >> select f1(1), generate_series(1,10); >> and got 11 notices so it looks like there's some kind of phantom involved. > > That's interesting - might need an answer from the core hackers. > I am posting this to pgsql-hackers to get their comments and feedback. > I wouldn't count it as a bug but it could be regarded as undesirable side > effects. Don't use set-returning functions in "scalar context". If you put them in the FROM clause, as Stephan says above, it works fine. Anywhere else they have strange behavior and they are supported only because of backwards compatibility. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why does the sequence skip a number with generate_series?
Stephan Szabo wrote: On Tue, 2 Oct 2007, Jeff Frost wrote: I expected these numbers to be in sync, but was suprised to see that the sequence skips a values after every generate series. CREATE TABLE jefftest ( id serial, num int ); INSERT INTO jefftest (num) values (generate_series(1,10)); INSERT INTO jefftest (num) values (generate_series(11,20)); INSERT INTO jefftest (num) values (generate_series(21,30)); It seems to do what you'd expect if you do INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); I tried a function that raises a notice and called it as select f1(1), generate_series(1,10); and got 11 notices so it looks like there's some kind of phantom involved. That's interesting - might need an answer from the core hackers. I am posting this to pgsql-hackers to get their comments and feedback. I wouldn't count it as a bug but it could be regarded as undesirable side effects. My guess is that what appears to happen is that the sequence is created by incrementing as part of the insert steps and the test to check the end of the sequence is - if last_inserted_number > end_sequence_number rollback_last_insert This would explain the skip in sequence numbers. My thoughts are that - if last_inserted_number < end_sequence_number insert_again would be a better way to approach this. Of course you would also need to check that the (last_insert + step_size) isn't greater than the end_sequence_number when the step_size is given. I haven't looked at the code so I don't know if that fits easily into the flow of things. The as foo(a) test would fit this as the sequence is generated into the equivalent of a temporary table the same as a subselect, then used as insert data. The rollback would be applied during the temporary table generation so won't show when the data is copied across to fulfill the insert. Maybe the planner or the generate series function could use a temporary table to give the same results as select from generate_series() -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Shane Ambler wrote:
>>> CREATE TABLE jefftest ( id serial, num int );
>>> INSERT INTO jefftest (num) values (generate_series(1,10));
>>> INSERT INTO jefftest (num) values (generate_series(11,20));
>>> INSERT INTO jefftest (num) values (generate_series(21,30));
> Don't use set-returning functions in "scalar context".
I think what is actually happening is that the expanded targetlist is
nextval('seq'), generate_series(1,10)
On the eleventh iteration, generate_series() returns ExprEndResult to
show that it's done ... but the 11th nextval() call already happened.
If you switched the columns around, you wouldn't get the extra call.
If you think that's bad, the behavior with multiple set-returning
functions in the same targetlist is even stranger. The whole thing
is a mess and certainly not something we would've invented if we
hadn't inherited it from Berkeley.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] What SQL is running against my DB?
Hello all, I have an application running against my postgres 8.2 database (on Windows Vista) for which I do not have access to the source code. I would like to know what SQL statements are being executed by the application. Is there a way to turn on logging in the server so that obtain this information? Thanks Tore ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] What SQL is running against my DB?
Tore Lukashaugen <[EMAIL PROTECTED]> schrieb: > Hello all, > > I have an application running against my postgres 8.2 database (on Windows > Vista) for which I do not have access to the source code. > > I would like to know what SQL statements are being executed by the > application. Is there a way to turn on logging in the server so that obtain > this information? Yes, of course. You can set in your postgresql.conf: log_statement = 'all' to log all statements. Read this file for more details, it is well documented. Andreas -- 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° ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Rule Error
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Rule Error
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
