Re: [SQL] Data Entry and Query forms
> > Before continuing our Plans, I need to know how can I do with > PostgreSQL in order to have Data Entry and Query Forms on clients side > (How can I design Data Entry and Query Forms). > Hy. You could try using BOND framework which works for both linux and win32. It is rather bad documented but is also very simple to use, and there are examples. Mailing list is low traffic, but if you ask you will get your anwser quick. The BOND developers are great guys, really :) The link is http://www.treshna.com/bond/ the is also gnue, which is longer in production, and offers more features at the moment, but requires some python knowledge http://www.gnuenterprise.org/ Hope this helps and welcome to the wonderful world of opensource, Jure ---(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: [SQL] Using In Clause For a Large Text Matching Query
Ah, I do think that sounds about like what I want! Let me play with this one some, thanks so much!! Richard Broersma Jr wrote: Well, there is also: href="http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13377";> /expression/ /operator/ ANY (/array expression/). So, if you have a way to preprocess you input text fields that you want matched you could build a regex for each and feed them in an array to an '~ ANY' expression like so (or, use ~* for case insensitive matching): SELECT col1 FROM table WHERE col1 ~ ANY (ARRAY['regex1', 'regex2', ...]); Good point, But don't forget to include the list in your response. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Views and query planner
Let's say I have a view like this: CREATE VIEW v_small AS SELECT c1, c2 FROM t1 JOIN t2 ON t1.c1 = t2.c2 and then I have another view CREATE VIEW v_big AS SELECT c1 FROM v_small WHERE c2 > 5 Now, when I do this: SELECT * FROM v_big WHERE c1 < 1500 Is postgres going to make one query plan, or each view has it own query plan? Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Views and query planner
On Jun 30, 2006, at 21:35 , Mario Splivalo wrote: Is postgres going to make one query plan, or each view has it own query plan? Have you taken a look at the EXPLAIN ANALYZE output of the queries? You can see exactly which plan PostgreSQL will use. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Views and query planner
On Fri, 2006-06-30 at 21:47 +0900, Michael Glaesemann wrote: > On Jun 30, 2006, at 21:35 , Mario Splivalo wrote: > > > Is postgres going to make one query plan, or each view has it own > > query > > plan? > > Have you taken a look at the EXPLAIN ANALYZE output of the queries? > You can see exactly which plan PostgreSQL will use. > So, the performance will be the same if I use view on view, or I create big select with those view definitions. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Views and query planner
Hi, Mario, Mario Splivalo wrote: >>> Is postgres going to make one query plan, or each view has it own >>> query >>> plan? >> Have you taken a look at the EXPLAIN ANALYZE output of the queries? >> You can see exactly which plan PostgreSQL will use. > > So, the performance will be the same if I use view on view, or I create > big select with those view definitions. Yes, usually[1]. Internally, PostgreSQL generates a big query out of the stack of views, and then lets the optimizer do its work. The query performance will be the same, but the planning performance may vary. (This effect can be mildered by careful use of prepared statements.) HTH, Markus [1] Queries reaching some complexity level may lead to different plans, due to optimizer limits, the gequo kicking in, or other effects. -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Alternative to Select in table check constraint
I am practicing with SQL examples comming from the book:
JOE CELKO'S
SQL
PUZZLES
& ANSWERS
The following codes doesn't work on PostgreSQL 8.1.4 but according to the book
does conform to
SQL-92. Is there any other solutions that would result in the same effect? Or
is this an example
of a contraint that should be avoided at all costs?
CREATE TABLE BADGES
(
BADGENO SERIAL NOT NULL PRIMARY KEY,
EMPNO INTEGER NOT NULL REFERENCES SECEMPLOYEES (EMPNO),
ISSUEDATE DATE NOT NULL,
STATUS CHAR(1) NOT NULL CHECK ( STATUS IN ('A', 'I')),
CHECK ( 1 = ALL ( SELECT COUNT(STATUS)
FROM BADGES
WHERE STATUS = 'A'
GROUP BY EMPNO))
);
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How To Exclude True Values
> Also, I produced a second query using PostgreSQL: > select a.id_i, a.ir_id, a.test, a.stamp > from test a > join > ( > select max(stamp) as mstamp, id_i > from test > group by id_i > ) b > on a.stamp = b.mstamp > where a.test = false > ; > -- result > id_i | ir_id | test |stamp > --+---+--+- > 4 | 8 | f| 2006-06-05 08:00:00 I found this query produced the same result. It is a list slower than the first with my small dataset. but maybe it will improve for larger datasets? select t1.id_i, t1.ir_id, t1.test, t1.stamp, t1.inttest from test as t1 where t1.stamp = ( select max(T2.stamp) from test as t2 where t2.id_i = t1.id_i) and t1.test = 'f'; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
On Jul 1, 2006, at 6:55 , Richard Broersma Jr wrote: CHECK ( 1 = ALL ( SELECT COUNT(STATUS) FROM BADGES WHERE STATUS = 'A' GROUP BY EMPNO)) From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ interactive/sql-createtable.html) Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. This is why the the above won't work. You can probably do something similar by creating UNIQUE index with a WHERE clause. For example (if I'm understanding the intent), CREATE UNIQUE INDEX one_a_badge_per_employee_idx ON badges (empno) WHERE status = 'A'; Here are links to more documentation on indexes: http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Alternative to Select in table check constraint
> > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > interactive/sql-createtable.html) > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > ON badges (empno) > WHERE status = 'A'; > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html Michael, Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of the constraint is to allow only one active badge status at a time. But now that I think about it, using the authors suggestion (if it actually worked), how would would it be possible to change the active status from one badge to another? Oh well, partial index are obvious the superior solution since the entire table doesn't not have to be scanned to determine if the new badge can be set to active. Once again thanks for the insight. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to Select in table check constraint
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > The following codes doesn't work on PostgreSQL 8.1.4 but according to > the book does conform to SQL-92. > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > FROM BADGES > WHERE STATUS = 'A' > GROUP BY EMPNO)) Yeah, the spec does allow sub-SELECTs in CHECK constraints but PG doesn't implement that. The problem with it is that there's no clear way to make it perform reasonably, because the CHECK doesn't simply implicate the row you're currently inserting/updating --- every other row is potentially referenced by the sub-SELECT, and so changing row X might make the CHECK condition fail at row Y. A brute-force implementation would be that every update of any sort to BADGES causes us to re-evaluate the CHECK constraint (hence, re-scan BADGES entirely to compute the sub-SELECT) for every row of BADGES (hence, O(N^2) work if there are N rows in the table). That is certainly unworkable :-(. A bright person can think of ways to optimize particular cases but it's not easy to see how the machine might figure it out for arbitrary SELECTs. The unique-index hack that Michael suggested amounts to hand-optimizing the sub-SELECT constraint into something that's efficiently checkable. regards, tom lane ---(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: [SQL] Alternative to Select in table check constraint
> > The following codes doesn't work on PostgreSQL 8.1.4 but according to > > the book does conform to SQL-92. > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > Yeah, the spec does allow sub-SELECTs in CHECK constraints but PG > doesn't implement that. The problem with it is that there's no clear > way to make it perform reasonably, because the CHECK doesn't simply > implicate the row you're currently inserting/updating --- every other > row is potentially referenced by the sub-SELECT, and so changing row > X might make the CHECK condition fail at row Y. A brute-force > implementation would be that every update of any sort to BADGES causes > us to re-evaluate the CHECK constraint (hence, re-scan BADGES entirely > to compute the sub-SELECT) for every row of BADGES (hence, O(N^2) work > if there are N rows in the table). That is certainly unworkable :-(. > A bright person can think of ways to optimize particular cases but > it's not easy to see how the machine might figure it out for arbitrary > SELECTs. > > The unique-index hack that Michael suggested amounts to hand-optimizing > the sub-SELECT constraint into something that's efficiently checkable. > > regards, tom lane Ah. Thanks for the clarification. Regards, Richard Broersma Jr. ---(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
