Re: [SQL] "on insert" rules happen before the insert?

2004-01-04 Thread Tom Lane
[EMAIL PROTECTED] (Thomas Reat) writes: > The postgresql documentation claims that "on insert" rules are executed > after the insert. This is not happening for me. I have a rule that is being > executed even though the insert should have failed due to a foreign key check. The INSERT certainly happ

Re: [SQL] not in vs not exists - vastly diferent performance

2004-01-04 Thread Iain
Hi Tom, > You didn't say what PG version you are using, but I'd venture to bet > it is pre-7.4. Sorry I didn't give the version number, here is the output from select version(): PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.95.3 This is the first time that

Re: [SQL] DO INSTEAD in rule

2004-01-04 Thread Tatsuo Ishii
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO > > INSTEAD in the rule and the default value for j is 0. Am I missing > > something? > > > CREATE rule t1_ins AS ON INSERT TO t1 > > WHERE (EXISTS (SELECT 1 FROM t1 > >

Re: [SQL] Radius of a zip code

2004-01-04 Thread Randolf Richardson
"[EMAIL PROTECTED] (Tom Lane)" wrote in comp.databases.postgresql.sql: [sNip] > I'm guessing that the big problem is that you didn't measure longitude > and latitude in identical units in your table, so your "circle" isn't > real circular, and the smaller problem is that "miles" converts to > "deg

[SQL] functional index

2004-01-04 Thread Hijax
HAPPY NEW YEAR EVERYONE ! Hi all.. Is there any method of creating functinal index using date() function? I can't create such an index because of uncacheable function... I have to select gathered data from specific day of year... So I should create an index for better performance. How can I

Re: [SQL] not in vs not exists - vastly diferent performance

2004-01-04 Thread Randolf Richardson
"[EMAIL PROTECTED] (Tom Lane)" wrote in comp.databases.postgresql.sql: > "Iain" <[EMAIL PROTECTED]> writes: > >> I found this interesting and thought I'd offer it up for comment. > > You didn't say what PG version you are using, but I'd venture to bet > it is pre-7.4. This must've been

[SQL] "on insert" rules happen before the insert?

2004-01-04 Thread Thomas Reat
The postgresql documentation claims that "on insert" rules are executed after the insert. This is not happening for me. I have a rule that is being executed even though the insert should have failed due to a foreign key check. The rule does an insert that is failing, and the whole insert fails. So

[SQL] Complex Update

2004-01-04 Thread John Coryat
Problem: I have a table that has a field I want to plug with a value from other tables. I can do it with a perl program and a loop, but want to do it with a single SQL statement. Is there a way to do an update that combines a select statement containing a "where" that uses a field from the row

[SQL] Historic Query using a view/function ?

2004-01-04 Thread Chris Gamache
...Postgresql 7.2... I'm building the history of a table using rules. I've been trying to figure out a way to select on a table as it would have appeared at a point-in-time. I can't seem to wrap my brain around the problem, tho. Given some tables CREATE TABLE list ( num int4 NOT NULL, name

Re: [SQL] DO INSTEAD in rule

2004-01-04 Thread Tom Lane
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO > INSTEAD in the rule and the default value for j is 0. Am I missing > something? > CREATE rule t1_ins AS ON INSERT TO t1 > WHERE (EXISTS (SELECT 1 FROM t1 >

[SQL] DO INSTEAD in rule

2004-01-04 Thread Tatsuo Ishii
Hi, In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO INSTEAD in the rule and the default value for j is 0. Am I missing something? DROP TABLE t1 CASCADE; DROP TABLE CREATE TABLE t1 ( i INTEGER, j INTEGER DEFAULT 0 ); CREATE TABLE CREATE rule t1_ins AS ON INSER