Re: [SQL] No Documentation for to_char(INTERVAL, mask)
On Mon, Mar 05, 2001 at 08:39:05PM -0800, Josh Berkus wrote: > Bruce, Tom, et. al., > > I can't find any documentation for what masks to use with the function > TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what > masks are there? If not, how would you suggest I convert an interval The 'interval' version of to_char() isn't implemented -- may be in 7.2 (it's high in my TODO list:-) Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problems with RULE
Hello all, I tried to implement the following rule: if someone wants to delete a record from a table t_xyz (id integer, deleted boolean) the record should get a delete-flag (deleted = true). When this "pre-deleted" record is deleted for the next time it should be physically deleted from the database. I implemented the following rule: CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (old.deleted = false) DO INSTEAD UPDATE t_xyz SET deleted = true WHERE id = old.id; Now I tested the new rule: INSERT INTO t_xyz VALUES (1, false); INSERT INTO t_xyz VALUES (2, false); DELETE FROM t_xyz WHERE id = 1; SELECT * FROM t_xyz ; id | deleted +- 2 | f What has happened? The rule seems to be ignored and the record was deleted! I dropped the rule, deleted all records and recreated the rule without the additional WHERE-Clause in the UPDATE-Statement: DROP RULE r_del_xyz; DELETE FROM t_xyz; CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (old.deleted = false) DO INSTEAD UPDATE t_xyz SET deleted = true; INSERT INTO t_xyz VALUES (1, false); INSERT INTO t_xyz VALUES (2, false); The same test again: DELETE FROM t_xyz WHERE id = 1; SELECT * FROM t_xyz ; id | deleted +- 2 | t It seems to me that PostgreSQL executed the rule, but ignored the keyword INSTEAD and deleted the record after having updated it?! One last test with a slightly different rule (look at the WHERE-clause in the "AS-ON"-clause): DROP RULE r_del_xyz; DELETE FROM t_xyz; CREATE RULE r_del_xyz AS ON DELETE TO t_xyz WHERE (1 = 1) DO INSTEAD UPDATE t_xyz SET deleted = true WHERE id = old.id; INSERT INTO t_xyz VALUES (1, false); INSERT INTO t_xyz VALUES (2, false); DELETE FROM t_xyz WHERE id = 1; SELECT * FROM t_xyz ; id | deleted +- 2 | f 1 | t DELETE FROM t_xyz WHERE id = 1; SELECT * FROM t_xyz ; Everything is alright now! Am I wrong? Is the WHERE-clause "WHERE (old.deleted = false)" not correct? Any hints? Or it is really a bug? Best regards, Jens Hartwig PS: You will find the scripts in the attachment. - T-Systems Projektleiter debis Systemhaus GEI GmbH Hausanschrift: Eichhornstraße 3, 10785 Berlin Postanschrift: 10785 Berlin Telefon: (004930) 25 54-32 82 Telefax: (004930) 25 54-31 87 Mobiltelefon: (0170) 167 26 48 E-Mail: [EMAIL PROTECTED] Internet: http://www.t-systems.de rule_error_1.sql rule_error_2.sql rule_error_3.sql ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Comparing dates
Hello, I've a SELECT statement on many joined Tabled and one of them has a date column called 'date_date'. When I fetch a date e.g. '02-03-2001', I get, say, 60 results back. When I now perform the same query with another date, lets take '03-03-2001', I get back about 70 results. When I now modify my query to get both results in one I write SELECT FROM .. AND date_date >= '2001-03-02' AND date_date <= '2001-03-03' AND I think I should get back the rows for both days, 60 + 70 makes 130 to me. But what I get back is even smaller then 60. I allready tried TO_DATE conversion, an OR construct but always the same result. Is there something special to know when comparing/working with date-datetypes ? kind regards, Markus -- Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ EMail: [EMAIL PROTECTED] PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Optimizing Query
Wow. I can't believe the difference. It didn't take too long. I'll set up a script in my etc/cron.weekly to run it... would there be any benefit to doing a vacuum analyze nightly? Justin Long At 11:10 PM 3/5/2001 -0500, you wrote: Justin Long <[EMAIL PROTECTED]> writes: > Ok, now I have another question... it doesn't seem to be accessing the index. > explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1 > WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and > w1.wordid=85369)) > NOTICE: QUERY PLAN: > Merge Join (cost=32339.30..35496.97 rows=19262538 width=24) > -> Merge Join (cost=16530.24..16668.77 rows=233274 width=20) > -> Sort (cost=15809.06..15809.06 rows=8257 width=4) > -> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257 > width=4) > -> Sort (cost=721.18..721.18 rows=2825 width=16) > -> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825 > width=16) > -> Sort (cost=15809.06..15809.06 rows=8257 width=4) > -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4) > Note the sequential scans... there is a wordindex where w0.wordid=42743... > why isn't it doing an indexscan? wouldn't that be more efficient? It probably thinks not, because the estimated number of hits (8257) is so high. That estimate is currently driven by the frequency of the most common value in the column (mainly because that's the only stat we have :-(). I am guessing that you have a few very common words, which are skewing the stats for kbwords and causing it not to pick an indexscan. Does your setup have a notion of "stop words" that shouldn't be indexed, like "a", "an", "the", etc? Perhaps you need to add such a feature, or throw in a few more stopwords if you already have 'em. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Justin Long Network for Strategic Missions [EMAIL PROTECTED] 1732 South Park Court http://www.strategicnetwork.org Chesapeake, VA 23320, USA Reality Check e-zine: [EMAIL PROTECTED] Law: Never retreat. Never surrender. Never cut a deal with a dragon. Corollary: No armor? Unclean life? Then do not mess in the affairs of dragons, for you are crunchy and taste good with ketchup.
Re: [SQL] Comparing dates
Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: [EMAIL PROTECTED] > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ANDing restrictions makes them narrower. If you want to broaden your selection, try ORing the conditions HTH Patrick ---(end of broadcast)--- TIP 3: 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: [SQL] Comparing dates
On 3/6/01, 4:38:41 PM, <[EMAIL PROTECTED]> wrote regarding Re: [SQL] Comparing dates: > Markus Fischer wrote: > > I've a SELECT statement on many joined Tabled and one of them has > > a date column called 'date_date'. When I fetch a date e.g. > > '02-03-2001', I get, say, 60 results back. When I now perform the > > same query with another date, lets take '03-03-2001', I get back > > about 70 results. > > > > When I now modify my query to get both results in one I write > > > > SELECT > > > > FROM > > .. > > AND > > date_date >= '2001-03-02' > > AND > > date_date <= '2001-03-03' > > AND > > > > > > I think I should get back the rows for both days, 60 + 70 makes > > 130 to me. But what I get back is even smaller then 60. I > > allready tried TO_DATE conversion, an OR construct but always > > the same result. > ANDing restrictions makes them narrower. > If you want to broaden your selection, try ORing the conditions > HTH > Patrick True enough - but in this case he should still get two days' worth. There's something odd here - try AND date_date >= '2001-03-02'::date AND date_date <= '2001-03-03'::date Or even AND (date_date = '2001-03-02'::date OR date_date = '2001-03-03'::date) And see if that helps. The only thing I can think of is that either date_date or the comparisons are being used as a timestamp and there is an invisible time part in there skipping some of the entries. Actually, try: AND date_date >= '2001-03-02' AND date_date < '2001-03-04' (note the < on the second part) If that does it, there are times in there somewhere - Richard Huxton ---(end of broadcast)--- TIP 3: 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: [SQL] Comparing dates
I am just wildly guessing here, but you initially stated that you queried on '02-03-2001' (Which I read as February 3, 2001 -- and I belive postgres does as well) which returned 60 results, and on '03-03-2001' (March 3, 2001), which returned 70 results. However, that is *not* the query your wrote out, you wrote date_date >= '2001-03-02' (which I would read as March 2, 2001) and date_date <= '2001-03-03' (March 3, 2001) -- which is two entirely different date ranges, and, hence, why you see the seemingly incorrect results. Try this: SELECT count(*) FROM table WHERE date_date = '03-02-2001'::date; SELECT count(*) FROM table WHERE date_date = '03-03-2001'::date; The sum of the above two, should match the count for each of the next two SELECT count(*) FROM table WHERE date_date >= '03-02-2001'::date AND date_date <= '03-03-2001'::date; SELECT count(*) FROM table WHERE date_date >= '03-02-2001'::date AND date_date < '03-04-2001'::date; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Mar 2001, Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: [EMAIL PROTECTED] > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] platform independend db access?
Hi, I would like my C source code to compile under unix and windows. I am currently using the pg libs to access my database. Under Windows I'd like to use odbc. What's the most platform (and db) independend way to access the database? Since odbc comes from MS, I am still looking for something "better". Would there be libs for unix? What to do? Thank you, Markus ---(end of broadcast)--- TIP 3: 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: [SQL] Temp Tables & Connection Pooling
Gerald Gutierrez wrote: > At 12:48 PM 3/2/2001 -0800, David Olbersen wrote: > >On Fri, 2 Mar 2001, Gerald Gutierrez wrote: > > > >->Recently I wanted to implement Dijkstra's algorithm as a stored procedure, > >->and finding that PL/PGSQL cannot return record sets, I thought about using > >->a temporary table for the results. If tempoary tables are session-specific, > >->however, then wouldn't connection pooling make it unusable since the table > >->might "disappear" from one query to the next? What are alternative > >->approaches to implementing Dijkstra's algorithm inside the database? > > > > > >Wouldn't a VIEW do what you want? > > > > No it wouldn't. Executing Dijkstra would involve executing iterative logic > on multiple tables and storing intermediate results in a form that can be > returned to the user but does not affect the actual persistent table schema > (e.g. a record set, or a temporary table). A view is used to provide a > simplified or alternative way of looking at a set of data, and cannot > cannot generally multi-step operation that data prior to returning to the user. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] This looks like a case for a persistent table where the function would write the data, along with some kind of session identifier, which would be returned from the function. Then your could go back to that table with that sessionid and find what you need. It is kludgey because it has the potential to leave stale data lying around, you will have to write all kinds of housekeeping code around it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Help creating rules/triggers/functions
Hello Blaise, I included a script I used to build/maintain a database. It also includes triggers and history logging. I hope you can use it. In the DO NOT use this part are test things which might not be correct. Best regards, Roelof <> > -Original Message- > From: Blaise Carrupt [SMTP:[EMAIL PROTECTED]] > Sent: 02 March 2001 16:29 > To: [EMAIL PROTECTED] > Subject: RE: [SQL] Help creating rules/triggers/functions > > Hi Roelof ! > > I thank you for your answer. It allowed me to find a way to resolve my > problems. > What is missing to your answer is the trigger. In the documentation, I > found a > way doing it with a C procedure. But I was sure Postgres could do it in a > simpler way. > > If it interests you, what I got now is : > > DROP FUNCTION a_del(); > > CREATE FUNCTION a_del() RETURNS OPAQUE AS > 'DECLARE > id INT4; > BEGIN > SELECT a_id INTO id /* I'm not sure INTO is > mandatory... */ >FROM a >WHERE a_id = OLD.addr_id; > > IF FOUND THEN > RAISE EXCEPTION ''not allowed !''; > END IF; > > RETURN OLD; > END;' > LANGUAGE 'plpgsql'; > > > > DROP TRIGGER a_del_trg ON a; > > CREATE TRIGGER a_del_trg > BEFORE DELETE ON a > FOR EACH ROW > EXECUTE PROCEDURE a_del(); > > > It's much more complicate than Ingres, but it works as well. I don't know > how > a_del knows OLD... > > I thank you again for your help. > > > ___ > B. Carrupt > > > DBcreate.scania.02.sql ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Optimizing Query
Justin Long <[EMAIL PROTECTED]> writes: > Wow. I can't believe the difference. It didn't take too long. I'll set up a > script in my etc/cron.weekly to run it... would there be any benefit to > doing a vacuum analyze nightly? Depends. A nightly vacuum is probably good practice, but you could skip the analyze part if your data statistics (such as column min and max values) don't change much. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems with RULE
On 3/6/01, 7:11:48 AM, Jens Hartwig <[EMAIL PROTECTED]> wrote regarding [SQL] Problems with RULE: > Hello all, > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. > I implemented the following rule: > CREATE RULE r_del_xyz > AS ON DELETE TO t_xyz WHERE (old.deleted = false) > DO INSTEAD > UPDATE t_xyz > SET deleted = true > WHERE id = old.id; > Now I tested the new rule: > INSERT INTO t_xyz VALUES (1, false); > INSERT INTO t_xyz VALUES (2, false); > DELETE FROM t_xyz WHERE id = 1; > SELECT * FROM t_xyz ; >id | deleted > +- > 2 | f > What has happened? The rule seems to be ignored and the record was deleted! No help I'm afraid, but I encountered something similar the other day on 7.1b3 CREATE RULE ... AS ON UPDATE TO ... WHERE ... DO INSTEAD UPDATE ... Didn't run, but removing the WHERE did. I had thought someone had raised this recently, but looking through the list I can't see it now, so maybe it's a real bug. Is there any mention of this is the CHANGES file in beta4? I'll try and check this end whether it applies to all rule-types if you add a WHERE. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] platform independend db access?
Markus Wagner wrote: > > Hi, > > I would like my C source code to compile under unix and windows. I am > currently using the pg libs to access my database. Under Windows I'd like > to use odbc. > > What's the most platform (and db) independend way to access the database? > Since odbc comes from MS, I am still looking for something "better". Would > there be libs for unix? What to do? > > Thank you, > > Markus > > ---(end of broadcast)--- > TIP 3: 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 id say use perl and or jdbc ---(end of broadcast)--- TIP 3: 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: [SQL] No Documentation for to_char(INTERVAL, mask)
Karel, > The 'interval' version of to_char() isn't implemented -- may be in 7.2 > (it's high in my TODO list:-) Grazie. (One of the things I love about PostgreSQL is being able to get definitive answers on functionality -- try asking Microsoft an "is this implemented?" question!) Given the lack of to_char(interval), I'd like to write a PLPGSQL function to fill the gap in the meantime. If you can answer a few questions about how interval values work, it would be immensely helpful: 1. Hours, minutes, and seconds are displayed as "00:00:00". Days are displayed as "0 00:00:00". How are weeks, months, and years displayed? 2. If months have their own placeholder in the Interval data type, how many days make up a month? Is it a fixed value, or does it depend on the calendar? Thanks. I'll post the PLPGSQL function to the list after I write it. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: 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
[SQL] Quick question MySQL --> PgSQL
Folks, Just a quick question ... I need to do a regular transfer (daily + on demand) of data from a MySQL database to a PostgreSQL database and back again. Can anybody steer me towards a good script for this, or do I have to write my own in PHP? Sorry to bother everyone with something that isn't strictly a SQL question, but I'm not sure where else to ask. -Josh Berkus P.S. If somebody wants consulting $$$ for the above, it may be available. -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Quick question MySQL --> PgSQL
On Tue, 6 Mar 2001, Josh Berkus wrote: > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? Don't think there is an actual migration script (I could be wrong, though), but a program using Perl DBI or JDBC would make the data access a bit easier -- if you wrote it generically enough, you could make the data transfer go both ways with just a command-line switch. -- Brett http://www.chapelperilous.net/~bmccoy/ Give all orders verbally. Never write anything down that might go into a "Pearl Harbor File". ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems with RULE
On 3/6/01, 7:11:48 AM, Jens Hartwig <[EMAIL PROTECTED]> wrote regarding [SQL] Problems with RULE: > Hello all, > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. Jens - more info Definitely a bug if my testing is correct (see below) - I'll file a report on it and include your example too (hope that's OK) - Richard Huxton -- OK define a table foo with data and a view voo showing -- even-numbered entries -- richardh=> create table foo (a int, b text); CREATE richardh=> insert into foo values (1,'aaa'); INSERT 1287580 1 richardh=> insert into foo values (2,'bbb'); INSERT 1287581 1 richardh=> insert into foo values (3,'ccc'); INSERT 1287582 1 richardh=> insert into foo values (4,'ddd'); INSERT 1287583 1 richardh=> create view voo as select * from foo where (a % 2)=0; CREATE richardh=> select * from voo; a | b ---+- 2 | bbb 4 | ddd (2 rows) -- Now define an insert rule with a where on voo -- richardh=> CREATE RULE voo_ins_rule AS ON INSERT TO voo WHERE (NEW.a % 2)=0 DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); ERROR: Cannot insert into a view without an appropriate rule richardh=> insert into voo values (98,'yyy'); ERROR: Cannot insert into a view without an appropriate rule richardh=> select * from foo; a | b ---+- 1 | aaa 2 | bbb 3 | ccc 4 | ddd (4 rows) richardh=> select * from voo; a | b ---+- 2 | bbb 4 | ddd (2 rows) -- OK: rule wasn't accepted, so lets add another rule to voo without a where -- richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); INSERT 1287602 1 richardh=> insert into voo values (98,'yyy'); INSERT 1287604 1 richardh=> select * from foo; a | b +- 1 | aaa 2 | bbb 3 | ccc 4 | ddd 99 | zzz 98 | yyy 98 | yyy (7 rows) richardh=> select * from voo; a | b +- 2 | bbb 4 | ddd 98 | yyy 98 | yyy (4 rows) -- So: looks like either rule2 executes twice or both fire. -- Is it because we have a second rule? -- richardh=> drop rule voo_ins_rule2; DROP richardh=> CREATE RULE voo_ins_rule3 AS ON INSERT TO voo WHERE (NEW.a % 2)=1 DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); ERROR: Cannot insert into a view without an appropriate rule richardh=> insert into voo values (98,'yyy'); ERROR: Cannot insert into a view without an appropriate rule richardh=> select * from foo; a | b +- 1 | aaa 2 | bbb 3 | ccc 4 | ddd 99 | zzz 98 | yyy 98 | yyy (7 rows) richardh=> select * from voo; a | b +- 2 | bbb 4 | ddd 98 | yyy 98 | yyy (4 rows) -- No: it must be the lack of where on rule2 -- Let's put rule2 back in and see what executes now -- richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); INSERT 1287608 1 richardh=> insert into voo values (98,'yyy'); INSERT 1287610 1 richardh=> select * from foo; a | b +- 1 | aaa 2 | bbb 3 | ccc 4 | ddd 99 | zzz 98 | yyy 98 | yyy 99 | zzz 99 | zzz 98 | yyy 98 | yyy (11 rows) richardh=> select * from voo; a | b +- 2 | bbb 4 | ddd 98 | yyy 98 | yyy 98 | yyy 98 | yyy (6 rows) -- OK: so it looks like rules with "WHERE" don't execute until -- there is a rule that fires unconditionally, when -- the "WHERE" is recognised and applies accordingly. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Quick question MySQL --> PgSQL
v7.1's contrib directory has the mysql->pgsql script that was used for the
SourceForge migration ... its also downloadable at
http://www.pgsql.com->Downloads
On Tue, 6 Mar 2001, Brett W. McCoy wrote:
> On Tue, 6 Mar 2001, Josh Berkus wrote:
>
> > Just a quick question ... I need to do a regular transfer (daily + on
> > demand) of data from a MySQL database to a PostgreSQL database and back
> > again. Can anybody steer me towards a good script for this, or do I
> > have to write my own in PHP?
>
> Don't think there is an actual migration script (I could be wrong,
> though), but a program using Perl DBI or JDBC would make the data access a
> bit easier -- if you wrote it generically enough, you could make the data
> transfer go both ways with just a command-line switch.
>
> -- Brett
> http://www.chapelperilous.net/~bmccoy/
>
> Give all orders verbally. Never write anything down that might go into a
> "Pearl Harbor File".
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Quick question MySQL --> PgSQL
"Brett W. McCoy" wrote: > > On Tue, 6 Mar 2001, Josh Berkus wrote: > > > Just a quick question ... I need to do a regular transfer (daily + on > > demand) of data from a MySQL database to a PostgreSQL database and back > > again. Can anybody steer me towards a good script for this, or do I > > have to write my own in PHP? > > Don't think there is an actual migration script (I could be wrong, > though), but a program using Perl DBI or JDBC would make the data access a > bit easier -- if you wrote it generically enough, you could make the data > transfer go both ways with just a command-line switch. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > > Give all orders verbally. Never write anything down that might go into a > "Pearl Harbor File". > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) there is one in contrib as well there is some through freshmeat.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Quick question MySQL --> PgSQL
On 3/6/01, 5:26:18 PM, Josh Berkus <[EMAIL PROTECTED]> wrote regarding [SQL] Quick question MySQL --> PgSQL: > Folks, > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? Don't think you'll find much off the shelf - this sort of thing tends to be specific to each person and each project. It's just a matter of SELECTing from MySQL and INSERTing into PostgreSQL in a loop. Be careful with things like times and dates and if you are doing it in PHP make sure you've thought about error recovery. I'd recommend copying into an import table then when it's confirmed that's worked do an internal copy in PG. Remember if it's run from a browser the user can just break the connection. Consider running it from a cron-job with lynx or wget (or write the thing in perl). > Sorry to bother everyone with something that isn't strictly a SQL > question, but I'm not sure where else to ask. > -Josh Berkus > P.S. If somebody wants consulting $$$ for the above, it may be > available. Sadly I charge in £££ and it doesn't sound like a large enough job to make it worth mucking around with conversions. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Quick question MySQL --> PgSQL
> > > there is one in contrib > > as well there is some through freshmeat.net There are two in /contrib. One from pgsql.com, and another from freshmeat.net: http://ziet.zhitomir.ua/~fonin/code/ I would like to see these merged someday. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Comparing dates
I think if you cast it then works. e.g. '02-03-2001'::date '02-03-2001'::timestamp Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: [EMAIL PROTECTED] > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How do I use text script containing SQL?
psql \i filename.txt -Andy Perrin "Jeff S." wrote: > > I want to build my tables by placing all the sql > statements in a file. What is the correct way to use > this file with psql? > > Example: My text file has this in it: > > CREATE TABLE table1 ( >table1_id serial, >field1 char(5), >PRIMARY KEY (table1_id) > ); > > I want to be able to use the file to create my table. > I've tried psql -d databasename -e < filename.txt > but that doesn't work. > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems with RULE
"Jens Hartwig" <[EMAIL PROTECTED]> writes: > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. > I implemented the following rule: > CREATE RULE r_del_xyz > AS ON DELETE TO t_xyz WHERE (old.deleted = false) > DO INSTEAD > UPDATE t_xyz > SET deleted = true > WHERE id = old.id; > Now I tested the new rule: > INSERT INTO t_xyz VALUES (1, false); > INSERT INTO t_xyz VALUES (2, false); > DELETE FROM t_xyz WHERE id = 1; > SELECT * FROM t_xyz ; >id | deleted > +- > 2 | f > What has happened? The rule seems to be ignored and the record was deleted! You'd probably have better luck doing this with a trigger. With this rule, the DELETE query expands into two operations, which can be written as: UPDATE t_xyz SET deleted = true WHERE id IN (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND old.deleted = false); DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false); The problem is that the second query can see the results of the first. Unfortunately, while that's bad for this example, it's necessary for other more-useful examples. So I do not think this is a bug. In my experience, anything you want to do that can be expressed as an operation or condition on an individual target tuple of an INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both performance and understandability. Rules are good for things that involve conditions on multiple tuples. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [BUGS] Mis-firing of rules with a WHERE condition
Richard Huxton ([EMAIL PROTECTED]) writes: > Jens Hartwig posted a question to pgsql-sql today (2001-03-06) > regarding rules with where conditions. It seems to be a bug and > applies to all rule-types. AFAICT this is not a bug but is operating as designed. The message you are getting: > richardh=> insert into voo values (99,'zzz'); > ERROR: Cannot insert into a view without an appropriate rule is a runtime check that insists that the view have at least one unconditional DO INSTEAD rule. It's OK to have conditional rules too (INSTEAD or not doesn't matter) --- but there must be an unconditional one, else there is no certainty that the undefined operation of inserting into the view won't occur. If you want the default to be that nothing happens, fine: add CREATE RULE voo_ins_default AS ON INSERT TO voo DO INSTEAD NOTHING and then do the useful work in conditional rules. But you gotta have the unconditional rule as a backstop. This runtime check is new in 7.1. In 7.0, the undefined operation of inserting into the view will actually occur if you are careless enough to let it. The effective result is that the inserted tuples disappear (I'll let you consult the archives to learn where they really go); that's mystified many people, including me when I first got burnt by it. I haven't had time to look closely at Jens' complaint, but I suspect that he is using 7.0 and is getting burnt by the undefined case. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Mike Mascari
[GENERAL] Date question
Hi, Im a little bit stuck here. Does anyone know how to get date in format '-MM-DD' of a date one year from now. So for example today is '2001-03-06' I need to get date 12 months from now which will be '2002-03-06' in todays case... In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that doesnt work in PG. Regards, Boulat Khakimov -- Nothing Like the Sun ---(end of broadcast)--- TIP 3: 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: [GENERAL] Date question
How about: SELECT '2001-03-06'::timestamp + '1 Year'; Hope that helps, Mike Mascari -Original Message- From: Boulat Khakimov [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, March 06, 2001 2:20 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:[GENERAL] Date question Hi, Im a little bit stuck here. Does anyone know how to get date in format '-MM-DD' of a date one year from now. So for example today is '2001-03-06' I need to get date 12 months from now which will be '2002-03-06' in todays case... In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that doesnt work in PG. Regards, Boulat Khakimov -- Nothing Like the Sun ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 3: 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
[GENERAL] Re: [SQL] Date question
This will do it: mfork=# SELECT to_char(now() + '1 Year'::interval, '-MM-DD'); to_char 2002-03-06 (1 row) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Mar 2001, Boulat Khakimov wrote: > Hi, > > Im a little bit stuck here. > > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. > > > Regards, > Boulat Khakimov > > > -- > Nothing Like the Sun > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RE: [SQL] Date question
Hi Boulat, stasis=# select (now() + '1 year')::date; ?column? 2002-03-06 (1 row) Hope this helps Francis > Hi, > > Im a little bit stuck here. > > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. > > > Regards, > Boulat Khakimov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: [GENERAL] Re: MySQLs Describe emulator!
* Boulat Khakimov <[EMAIL PROTECTED]> [010306 07:24] wrote: > > Karel Zak wrote: > > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > > Tom Lane wrote: > > > > > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > > > Here is a nifty query I came up with > > > > > that provides a detailed information on any row of any table. > > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > > but not into PG. > > > > > > > > Er, what's wrong with psql's "\d table" ? > > > > > > 2) as a programmer I need to be able to find out as much info as > > > possible about any given field > > >which is what "describe" for in mySQL. > > > > As a programmer you can see psql source and directly found how SQL > > query execute this tool. The PostgreSQL needn't non-standard statements > > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. FreeBSD has had some great successes because we're able to emulate Linux, perhaps something in contrib or even the base system could offer a MySQL compatibility module to help people ease into Postgresql from Mysql? -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] ---(end of broadcast)--- TIP 3: 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: [SQL] Permissons on database
On 3/6/01, 5:00:47 PM, Boulat Khakimov <[EMAIL PROTECTED]> wrote regarding [SQL] Permissons on database: > Hi, > How do I grant permissions on everything in the selected databes? > GRANT doesnt take as on object database name nor does it accept wild > chars By hand at the moment I'm afraid. There is no GRANT ALL ON ALL... Having said that, if you have plpgsql enabled on that database you could write a simple loop to do so for you. If you'd find one useful let me know and I'll knock one up. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] MySQLs Describe emulator!
Tom Lane wrote: > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > Here is a nifty query I came up with > > that provides a detailed information on any row of any table. > > Something that is build into mySQL (DESC tablename fieldname) > > but not into PG. > > Er, what's wrong with psql's "\d table" ? Hi, 1) "\d table" can only be used in psql, you cant run a query like that using libpq for example 2) as a programmer I need to be able to find out as much info as possible about any given field which is what "describe" for in mySQL. Regards, Boulat Khakimov -- Nothing Like the Sun ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: MySQLs Describe emulator!
On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > Tom Lane wrote: > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > Here is a nifty query I came up with > > > that provides a detailed information on any row of any table. > > > Something that is build into mySQL (DESC tablename fieldname) > > > but not into PG. > > > > Er, what's wrong with psql's "\d table" ? > > 2) as a programmer I need to be able to find out as much info as > possible about any given field >which is what "describe" for in mySQL. As a programmer you can see psql source and directly found how SQL query execute this tool. The PostgreSQL needn't non-standard statements like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [GENERAL] Date question
Boulat Khakimov writes: > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. How about CURRENT_DATE + INTERVAL '12 months'? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: 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: [GENERAL] Re: MySQLs Describe emulator!
Karel Zak wrote: > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > Tom Lane wrote: > > > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > > Here is a nifty query I came up with > > > > that provides a detailed information on any row of any table. > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > but not into PG. > > > > > > Er, what's wrong with psql's "\d table" ? > > > > 2) as a programmer I need to be able to find out as much info as > > possible about any given field > >which is what "describe" for in mySQL. > > As a programmer you can see psql source and directly found how SQL > query execute this tool. The PostgreSQL needn't non-standard statements > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. > > Karel Agreed! Why make someones life easier?? Let's complicate things as much as possible that way it's more fun,right? ;o) Dont understand how this works? No problem -- just read the source code. Dont understand how to get that to work? Not a problem -- read the source code! The only problem tho, the source codes tend to be thousands of lines when it comes to DBs and time is ... Regards, Boulat Khakimov -- Nothing Like the Sun ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Re: MySQLs Describe emulator!
On Tuesday 06 March 2001 10:19 am, Boulat Khakimov wrote: > Karel Zak wrote: > > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > > > > > Tom Lane wrote: > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > > > Here is a nifty query I came up with > > > > > that provides a detailed information on any row of any table. > > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > > but not into PG. > > > > > > > > Er, what's wrong with psql's "\d table" ? > > > > > > 2) as a programmer I need to be able to find out as much info as > > > possible about any given field > > >which is what "describe" for in mySQL. > > > > As a programmer you can see psql source and directly found how SQL > > query execute this tool. The PostgreSQL needn't non-standard statements > > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. > > > > Karel > > Agreed! Why make someones life easier?? > Let's complicate things as much as possible that way it's more > fun,right? ;o) > > Dont understand how this works? No problem -- just read the source > code. > Dont understand how to get that to work? Not a problem -- read the > source code! > > The only problem tho, the source codes tend to be thousands of lines > when it comes > to DBs and time is ... And, further, some of us are web programmers, and the source code doesn't help one whit in getting DATA that one needs to do stuff on the front end, like data entry validation. Thanks much Boulat - you made the coding for my robust validator a lot easier to write and read... and it works well, too! Michelle Michelle Murrain, Ph.D. President Norwottuck Technology Resources [EMAIL PROTECTED] http://www.norwottuck.com ---(end of broadcast)--- TIP 3: 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: [SQL] Date question
you can say: (now() + '1year'::timespan)::date Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Boulat Khakimov wrote: > Hi, > > Im a little bit stuck here. > > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. > > > Regards, > Boulat Khakimov > > > -- > Nothing Like the Sun > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Date question
Francis Solomon wrote: > > Hi Boulat, > > stasis=# select (now() + '1 year')::date; > ?column? > > 2002-03-06 > (1 row) > > Hope this helps > > Francis > > > Hi, > > > > Im a little bit stuck here. > > > > Does anyone know how to get date in format '-MM-DD' of a date one > > year from now. > > So for example today is '2001-03-06' I need to get date 12 months from > > now > > which will be '2002-03-06' in todays case... > > > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > > doesnt work in PG. > > > > > > Regards, > > Boulat Khakimov > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] i dunno about you but i like this syntax better than the old :: ones select date(now()+ '1 year'); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: [GENERAL] MySQLs Describe emulator!
try starting psql with the -E option -- this displays all queries used internally to the screen, i.e.: bash-2.04$ psql -E * QUERY * SELECT usesuper FROM pg_user WHERE usename = 'mfork' * Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit mfork=# \d test * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='test' * * QUERY * SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'test' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum * Table "test" Attribute | Type | Modifier ---+--+-- t | text | d | date | So to get the info displayed with \d, execute the query: SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '<>' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Mar 2001, Boulat Khakimov wrote: > Hi, > > 1) "\d table" can only be used in psql, you cant run a query like that > using libpq for example > > 2) as a programmer I need to be able to find out as much info as > possible about any given field >which is what "describe" for in mySQL. > > Regards, > Boulat Khakimov > > > -- > Nothing Like the Sun > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 3: 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
[SQL] Permissons on database
Hi, How do I grant permissions on everything in the selected databes? GRANT doesnt take as on object database name nor does it accept wild chars -- Nothing Like the Sun ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: [SQL] Permissons on database
Boulat Khakimov wrote: >Hi, > >How do I grant permissions on everything in the selected databes? > >GRANT doesnt take as on object database name nor does it accept wild >chars However you can give it a list of tables (and other objects). -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD is my light and my salvation; whom shall I fear? the LORD is the strength of my life; of whom shall I be afraid?" Psalms 27:1 ---(end of broadcast)--- TIP 3: 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: [SQL] Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng allegedly wrote: > - Original Message - > From: The Hermit Hacker <[EMAIL PROTECTED]> > To: Jaruwan Laongmal <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, March 02, 2001 8:04 PM > Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in >DB? > > > On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: > > > > > I had deleted a very large number of records out of my SQL table in order to > > > decrease the harddisk space. But after I use command 'ls -l > > > /usr/local/pgsql/data/base/', it is found that the size of concerning files > > > do not reduce due to the effect of 'delete' SQL command. What should I do > > > if I would like to decrease the harddisk space? > > > > VACUUM > > could anyone remove this nasty bug in 7.2? this is already a big pain and is the >reason > why am I still using MySQL in my product server. another nasty thing is it does not > allow me to reference table in another database. sigh. Why would this be a bug? Sure, maybe it's not what you expected, but I hardly think it qualifies as a bug. For instance, Oracle doesn't release storage (datafiles specifically) after it has allocated space for them. In fact, I wish I could force pgsql to allocate storage it might need in the future. It would be great if I could force pgsql to allocated four datafiles spread across four harddisks, so I would enjoy a) better database performance and b) rest assured I have the diskspace when I need it in the future. Call it a poor mans RAID; I think MySQL can perform this trick. If pgsql can do this, please let me know But back to your problem. One way to get the amount of space allocated to shrink is by recreating the database. Dump it using pg_dump and recreate it using the backup you just made. This is a fairly simple and quick process. Give it a try on a small test database first; you don't want to risk loosing your data. Cheers, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Undefined symbol
Hi,
Im writing a function in C (encode) for PG that uses blowfish encryption
here is how I compile it.
gcc -I/usr/src/postgresql-7.0.3/src/include
-I/usr/src/postgresql-7.0.3/src/backend -O2 -Wall -Wmissing-prototypes
-Wmissing-declarations -lcrypt
-I/usr/src/postgresql-7.0.3/src/interfaces/libpq
-I/usr/src/postgresql-7.0.3/src/include -fpic
-I/home/boulat/Funio.com/database/libblowfish.a -c -o encode.o
encode.c
it compiles nicely with 0 error or warnings,
then i do
gcc -shared -o encode.so encode.o
rm encode.o
so now im left we a ready to go encode.so , So now I add that function
to DB
testdb=# CREATE FUNCTION encode(text,text)
testdb-# RETURNS text
testdb-# AS '/home/boulat/Funio.com/database/encode.so'
testdb-# LANGUAGE 'C';
CREATE
no problems there either, BUT ...
testdb=# select encode('bob','bob');
ERROR: Load of file /home/boulat/Funio.com/database/encode.so failed:
/home/boulat/Funio.com/database/encode.so: undefined symbol:
BF_cfb64_encrypt
thats the function that I call from inside my c code...
but Why??? It compiled with no errors or warning, and I have all the
right includes in my source code.
Im confused!
Any help would be appreciated.
Regards,
Boulat Khakimov
--
Nothing Like the Sun
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: [GENERAL] Re: MySQLs Describe emulator!
On Tue, Mar 06, 2001 at 04:37:32PM +0100, Karel Zak allegedly wrote: > On Tue, Mar 06, 2001 at 10:19:13AM -0500, Boulat Khakimov wrote: > > > > Karel Zak wrote: > > > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > > > Tom Lane wrote: > > > > > > > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > > > > Here is a nifty query I came up with > > > > > > that provides a detailed information on any row of any table. > > > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > > > but not into PG. > > > > > > > > > > Er, what's wrong with psql's "\d table" ? > > > > > > > > 2) as a programmer I need to be able to find out as much info as > > > > possible about any given field > > > >which is what "describe" for in mySQL. > > > > > > As a programmer you can see psql source and directly found how SQL > > > query execute this tool. The PostgreSQL needn't non-standard statements > > > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. > > > > > > Karel > > > > Agreed! Why make someones life easier?? > > Let's complicate things as much as possible that way it's more > > fun,right? ;o) > > > > Dont understand how this works? No problem -- just read the source > > code. > > Dont understand how to get that to work? Not a problem -- read the > > source code! > > > > The only problem tho, the source codes tend to be thousands of lines > > when it comes > > to DBs and time is ... > > Well man, I not write this code, but I need 1 minute for found it > > see src/bin/psql/describe.c: > > SELECTa.attname, format_type(a.atttypid, a.atttypmod), attnotnull, > a.atthasdef, a.attnum, obj_description(a.oid) > FROM pg_class c, pg_attribute a > WHERE c.relname = 'YourTableName' AND >^ > a.attnum > 0 AND > a.attrelid = c.oid > ORDER BY a.attnum; > > If I good remenber anywhere in PG's docs is catalog schema. It isn't > too much difficult write queries like above-mentioned, because catalog > attributes/tables names are intuitive. For start see pg_class and > pg_attribute. Karel, how about this one? It's even easier :) No need to spit through code to find this... serv0:/var/namedsrc$ psql -E -c '\d nodes' iig * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='nodes' * * QUERY * SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'nodes' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum * * QUERY * SELECT c2.relname FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'nodes' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY c2.relname * Table "nodes" Attribute | Type | Modifier ---+--+-- id| integer | title | text | ncount| smallint | ecount| smallint | ref | integer | moddate | integer | publish | char(1) | Indices: idx_nodes_id, idx_nodes_ref, idx_nodes_title Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Undefined symbol
On Tue, Mar 06, 2001 at 06:44:31PM -0500, Boulat Khakimov allegedly wrote:
> testdb=# select encode('bob','bob');
> ERROR: Load of file /home/boulat/Funio.com/database/encode.so failed:
> /home/boulat/Funio.com/database/encode.so: undefined symbol:
> BF_cfb64_encrypt
>
> thats the function that I call from inside my c code...
> but Why??? It compiled with no errors or warning, and I have all the
> right includes in my source code.
>
> Im confused!
Are you linking against a blowfish library? If so, either include the
encryption functions in your shared object, or load that shared object
yourself. Have a look at the dlopen manpage for more information.
I hope this helps a bit,
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re[2]: [SQL] Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
Hello Mathijs, Wednesday, March 07, 2001, 7:46:01 AM, you wrote: MB> On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng allegedly wrote: >> - Original Message - >> From: The Hermit Hacker <[EMAIL PROTECTED]> >> To: Jaruwan Laongmal <[EMAIL PROTECTED]> >> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> >> Sent: Friday, March 02, 2001 8:04 PM >> Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data >in DB? >> >> > On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: >> > >> > > I had deleted a very large number of records out of my SQL table in order to >> > > decrease the harddisk space. But after I use command 'ls -l >> > > /usr/local/pgsql/data/base/', it is found that the size of concerning files >> > > do not reduce due to the effect of 'delete' SQL command. What should I do >> > > if I would like to decrease the harddisk space? >> > >> > VACUUM >> >> could anyone remove this nasty bug in 7.2? this is already a big pain and is the >reason >> why am I still using MySQL in my product server. another nasty thing is it does not >> allow me to reference table in another database. sigh. MB> Why would this be a bug? Sure, maybe it's not what you expected, but I hardly think MB> it qualifies as a bug. For instance, Oracle doesn't release storage (datafiles MB> specifically) after it has allocated space for them. In fact, I wish I could force MB> pgsql to allocate storage it might need in the future. It would be great if I could MB> force pgsql to allocated four datafiles spread across four harddisks, so I would MB> enjoy a) better database performance and b) rest assured I have the diskspace when MB> I need it in the future. Call it a poor mans RAID; I think MySQL can perform this MB> trick. If pgsql can do this, please let me know MB> But back to your problem. One way to get the amount of space allocated to shrink is MB> by recreating the database. Dump it using pg_dump and recreate it using the backup MB> you just made. This is a fairly simple and quick process. Give it a try on a small MB> test database first; you don't want to risk loosing your data. MB> Cheers, MB> Mathijs do you really know the problem of PGSQL storage manager? it DOES NOT reuse deleted record space. it also grows database size when you just update but not insert record. it is a MS ACCESS like storage manager. it is a functional bug. there is logic bug, performance bug... -- Best regards, Xu Yifeng ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re[2]: [SQL] Re: [HACKERS] why the DB file size does not reduce when'delete'the data in DB?
On Wed, 7 Mar 2001, Xu Yifeng wrote: > do you really know the problem of PGSQL storage manager? it DOES NOT > reuse deleted record space. it also grows database size when you just > update but not insert record. it is a MS ACCESS like storage manager. > it is a functional bug. there is logic bug, performance bug... Well, as always, we look forward to seeing patches from you to fix this glaring functional bug :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: [SQL] Re: why the DB file size does not reduce when 'delete'the data in DB?
> do you really know the problem of PGSQL storage manager? it DOES NOT > reuse deleted record space. it also grows database size when you just > update but not insert record. it is a MS ACCESS like storage manager. > it is a functional bug. there is logic bug, performance bug... imho a designed-in feature can not be called a bug, even if you disagree with its intent or implementation. The term "bug" should be reserved for code which does not behave as designed. You are not quite factually correct above, even given your definition of "bug". PostgreSQL does reuse deleted record space, but requires an explicit maintenance step to do this. We have continuing discussions on how to evolve the performance and behavior of PostgreSQL, and you can check the archives on these past discussions. Regards. - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: Re[2]: [SQL] Re: [HACKERS] why the DB file size does notreduce when 'delete'the data in DB?
> do you really know the problem of PGSQL storage manager? it DOES NOT > reuse deleted record space. it also grows database size when you just > update but not insert record. it is a MS ACCESS like storage manager. > it is a functional bug. there is logic bug, performance bug... It's not a bug but a feature invented by Michael Stonebraker. Write to him why do you think that is a bug:-) -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Query Limitations
PostgreSQL 6.4 seems to have limitations in Query Length when I "CREATE VIEW" ... is this limit defined further, when I create a query on a query... it seems to compound the queries and reach the limit sooner!! Is this limit programmable? Is it default higher in 7.0? What is the most stable 7.X release? Keith ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Query Limitations
> PostgreSQL 6.4 seems to have limitations in Query Length when I > > "CREATE VIEW" ... is this limit defined > > further, when I create a query on a query... it seems to compound > the queries and reach the limit sooner!! > > Is this limit programmable? > > Is it default higher in 7.0? Yes. > > What is the most stable 7.X release? 7.0.3 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] On Clusters
A previous posting mentioning clusters prompted me to revist some earlier tests done on clustered and unclustered data. It appears that currently ( 7.1beta5 ) the optimizer is unaware of any clustering on a table - how important is that ? To answer this question I used by "pet" data warehouse tables : Table "fact1" 300 rows ~ 350Mb Attribute | Type | Distribution ---+-+- d0key | integer | 3000 distinct values 0-9000 clustered d1key | integer | val | integer | filler| text| Index: fact1_pk ( d0key,d0key ) cluster "key" Table "fact2" 300 rows ~ 350Mb Attribute | Type | Distribution ---+-+- d0key | integer | 3000 distinct values 0-9000 uniformly spread d1key | integer | val | integer | filler| text| Index: fact2_pk ( d0key,d0key ) The sample queries used to shed some light on the nature of the difference are : firstly the index scan - explain select count(*) from fact1 where d0key between 200 and 279; Aggregate (cost=58664.62..58664.62 rows=1 width=0) -> Index Scan using fact1_pk on fact1 (cost=0.00..58598.72 rows=26360 width=0) and the sequential scan - explain select count(*) from fact1 where d0key between 200 and 280; Aggregate (cost=59020.73..59020.73 rows=1 width=0) -> Seq Scan on fact1 (cost=0.00..58954.00 rows=26693 width=0) and analogous versions for fact2 ( with the same execution plan ) On the unclustered table fact2 the optimizer correctly assess the time to switch between an index scan and an sequential scan - both queries take about 30 s. However on the clustered table fact1, the (same) choice results in a jump from1s for the index scan to 30s for the sequential scan. (this was the guts of the previous research... bear with me those of you who read the last article ) So how long should an index scan be used for ?, some experimentation led me to adjust the "where" clause in my queries to where d0key between 0 and 4500 This produces a query plan of : Aggregate (cost=62692.75..62692.75 rows=1 width=0) -> Seq Scan on fact1 (cost=0.00..58954.00 rows=1495498 width=0) coercing the optimizer with a brutal set of cpu_tuple_cost = 0.4 gives : Aggregate (cost=868673.82..868673.82 rows=1 width=0) -> Index Scan using fact1_pk on fact1 (cost=0.00..864935.08 rows=1495498 width=0) (note that these scan 150 rows, ie. half the data ) Testing these queries on fact1 gives run times af about 35s for both - thus it is worthwhile to keep using index scans of upto 50% of the ( clustered ) table data. I found this result interesting, as I was thinking more like 15-20% of the table data would be the limit. The answer to the original question ( finally ) is "it is pretty important", as knowlege of the clustering drastically changes the optimal access path. So what to do if you know you have clustered data ? ( either via cluster or "it just happens to go in that way" ). Tampering with the various *cost type parameters to encourage index scans seems to be the only solution (other sugestions welcome here), but tends to be too global in effect ( for example trying the previous query on (unclustered ) fact2 with cpu_tuple_cost=0.4 takes more that 300s - I got tired of waiting...) . So be careful out there... Cheers Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
AW: [SQL] Problems with RULE
JH: > > [...] > > I tried to implement the following rule: if someone wants to delete a record > > from a table t_xyz (id integer, deleted boolean) the record should get a > > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > > the next time it should be physically deleted from the database. > > [...] TL: > In my experience, anything you want to do that can be expressed as > an operation or condition on an individual target tuple of an > INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both > performance and understandability. Rules are good for things that > involve conditions on multiple tuples. > [...] I am afraid, that I do not really understand this: if I insert one record in a view there also is only ONE tuple involved, isn't it? By the way, I admit that my example is not really useful :-) It should only demonstrate the use of rules for the book I am writing on. Further I do not understand the following: > You'd probably have better luck doing this with a trigger. With this > rule, the DELETE query expands into two operations, which can > be written > as: > > UPDATE t_xyz SET deleted = true > WHERE id IN > (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND > old.deleted = false); > > DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false); What would have happened, if I executed an unconditional DELETE? => DELETE FROM t_xyz; Which statement would have been generated by PostgreSQL in this case? Best Regards, Jens - T-Systems Projektleiter debis Systemhaus GEI GmbH Hausanschrift: Eichhornstraße 3, 10785 Berlin Postanschrift: 10785 Berlin Telefon: (004930) 25 54-32 82 Telefax: (004930) 25 54-31 87 Mobiltelefon: (0170) 167 26 48 E-Mail: [EMAIL PROTECTED] Internet: http://www.t-systems.de ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: [SQL] Problems with RULE
Hello Richard, this was a very precise analysis - thanks for the effort you made! Nevertheless the Tom's explanation of the behaviour in case of views was sufficient for me. But still I don't understand the behaviour in my case ... Best regards, Jens PS: I use 7.1b4 - T-Systems Projektleiter debis Systemhaus GEI GmbH Hausanschrift: Eichhornstraße 3, 10785 Berlin Postanschrift: 10785 Berlin Telefon: (004930) 25 54-32 82 Telefax: (004930) 25 54-31 87 Mobiltelefon: (0170) 167 26 48 E-Mail: [EMAIL PROTECTED] Internet: http://www.t-systems.de > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Gesendet: Dienstag, 6. März 2001 19:10 > An: Jens Hartwig > Cc: PSQL-Sql (E-Mail) > Betreff: Re: [SQL] Problems with RULE > > > On 3/6/01, 7:11:48 AM, Jens Hartwig <[EMAIL PROTECTED]> wrote > regarding [SQL] Problems with RULE: > > > Hello all, > > > I tried to implement the following rule: if someone wants > to delete a > record > > from a table t_xyz (id integer, deleted boolean) the record > should get a > > delete-flag (deleted = true). When this "pre-deleted" > record is deleted > for > > the next time it should be physically deleted from the database. > > Jens - more info > > Definitely a bug if my testing is correct (see below) - I'll file a > report on it and include your example too (hope that's OK) > > - Richard Huxton > > -- OK define a table foo with data and a view voo showing > -- even-numbered entries > -- > richardh=> create table foo (a int, b text); > CREATE > richardh=> insert into foo values (1,'aaa'); > INSERT 1287580 1 > richardh=> insert into foo values (2,'bbb'); > INSERT 1287581 1 > richardh=> insert into foo values (3,'ccc'); > INSERT 1287582 1 > richardh=> insert into foo values (4,'ddd'); > INSERT 1287583 1 > richardh=> create view voo as select * from foo where (a % 2)=0; > CREATE > richardh=> select * from voo; > a | b > ---+- > 2 | bbb > 4 | ddd > (2 rows) > > -- Now define an insert rule with a where on voo > -- > richardh=> CREATE RULE voo_ins_rule AS ON INSERT TO voo WHERE > (NEW.a % 2)=0 > DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> insert into voo values (98,'yyy'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> select * from foo; > a | b > ---+- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > (4 rows) > > richardh=> select * from voo; > a | b > ---+- > 2 | bbb > 4 | ddd > (2 rows) > > -- OK: rule wasn't accepted, so lets add another rule to voo > without a > where > -- > richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO > INSTEAD INSERT > INTO > foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > INSERT 1287602 1 > richardh=> insert into voo values (98,'yyy'); > INSERT 1287604 1 > richardh=> select * from foo; > a | b > +- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > 99 | zzz > 98 | yyy > 98 | yyy > (7 rows) > > richardh=> select * from voo; > a | b > +- > 2 | bbb > 4 | ddd > 98 | yyy > 98 | yyy > (4 rows) > > -- So: looks like either rule2 executes twice or both fire. > -- Is it because we have a second rule? > -- > richardh=> drop rule voo_ins_rule2; > DROP > richardh=> CREATE RULE voo_ins_rule3 AS ON INSERT TO voo > WHERE (NEW.a % > 2)=1 DO > INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> insert into voo values (98,'yyy'); > ERROR: Cannot insert into a view without an appropriate rule > richardh=> select * from foo; > a | b > +- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > 99 | zzz > 98 | yyy > 98 | yyy > (7 rows) > > richardh=> select * from voo; > a | b > +- > 2 | bbb > 4 | ddd > 98 | yyy > 98 | yyy > (4 rows) > > -- No: it must be the lack of where on rule2 > -- Let's put rule2 back in and see what executes now > -- > richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO > INSTEAD INSERT > INTO > foo VALUES (NEW.a, NEW.b); > CREATE > richardh=> insert into voo values (99,'zzz'); > INSERT 1287608 1 > richardh=> insert into voo values (98,'yyy'); > INSERT 1287610 1 > richardh=> select * from foo; > a | b > +- > 1 | aaa > 2 | bbb > 3 | ccc > 4 | ddd > 99 | zzz > 98 | yyy > 98 | yyy > 99 | zzz > 99 | zzz > 98 | yyy > 98 | yyy > (11 rows) > > richardh=> select * from voo; > a | b > +- > 2 | bbb > 4 | ddd > 98 | yyy > 98 | yyy > 98 | yyy > 98 | yyy > (6 rows) > > -- OK: so it looks like rules with "WHERE" don't execute until > -- there is a rule that fires unconditionally, when > -- the "WHERE" is recognised and applies accordingly. ---(end of broadcast)---
