Re: [SQL] Postgres - Delphi Application
Dnia 2003-06-19 19:50, Użytkownik murali napisał: > Hi > > I' would like to connect my postgres database on a linux server with > Delphi 5 Applications,which are located on Win95/98 Clients, very > helpful would be an example on how to realize a connection of these things > > Thanks > > Murali http://sourceforge.net/projects/zeoslib Nice solution - your executable needs only small libpq.dll file without any annoying BDE/ODBC installation. There is one more native driver for C++ Builder/Delphi - pgexpress, but it's not free. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Change the behaviour of the SERIAL "Type"
Randall Lucas wrote: Wow, I had never actually faced this problem (yet) but I spied it as a possible stumbling block for porting MySQL apps, for which the standard practice is inserting a NULL. As I have made a fairly thorough reading of the docs (but may have not cross-correlated every piece of data yet, obviously), I was surprised to find I hadn't figured this out myself. It /seems/ obvious in retrospect, but it really baked my noodle when I first looked at some ugly MySQL queries. Respectfully, then, I move that a sentence outlining this functionality be added to User Manual section 5.1.4, "The Serial Types." Furthermore, anyone who has written or is writing a MySQL porting guide should include this, if he hasn't. Yea, fine, but I propose a different (deeper) approach. Why does SERIAL only enforce a DEFAULT? This is not an exact imitation of an autoincrement, as a DEFAULT can be overwritten. In my oppinion, SERIAL should implicitly create a Trigger on the table, which then handles this transparently. Would that be difficult? (I am already writing a Procedure which gets all the info needed out of the Catalog, but my problem is that I need some dynamic statements in there...) Cheers, Dani ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Postgres - Delphi Application
Dnia 2003-06-27 12:25, Użytkownik [EMAIL PROTECTED] napisał: I think I looked at zeos at some point and found that it did not support md5 authentication, which, at the time, I thought was important. ~Berend Tober Zeos has nothing to authentication. It's a matter of libpq library, which currently supports md5 authentication. If you need more security - you can even compile libpq with ssl. Tomasz ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Getting all rows even if not a member of any groups
Hi, I have a product table identified by its id field. There is a productgroups table with productisd, productgroupid fields. And I have a prod_in_pgr (productid, productgroupid) table which describes the membership of productgroups. Each product can be a member of zero or more productgroups, but one productgroup can contain a product only once. I would like to list the following information: productgroupid | productid | ... some other prouduct info | ... I need all the products even if it is not a member in any productgroups. I need these information ordered by productgroup and then productid. An example: select t_productgroups.name as pgroup, t_products.id as productid from t_products join t_prod_in_pgr on (t_products.id=productid) join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid) order by pgroup, productid; > pgroup | productid --+--- Alumínium profilok| 6047 Alumínium profilok| 6048 Alumínium profilok| 6049 Alumínium profilok| 6050 Alumínium profilok| 6051 Alumínium profilok| 6052 Alumínium profilok| 6053 Alumínium profilok| 6054 Alumínium profilok| 6055 Alumínium profilok| 6056 Alumínium profilok| 6057 Alumínium profilok| 6058 Alumínium profilok| 6059 Alumínium profilok| 6060 Alumínium profilok| 6061 Alumínium profilok| 6062 Gumik | 6063 Hohíd mentes profilok | 6060 Hohíd mentes profilok | 6061 Hohíd mentes profilok | 6062 Hohidas profilok | 6050 Hohidas profilok | 6051 Hohidas profilok | 6052 Hohidas profilok | 6053 Hohidas profilok | 6054 Hohidas profilok | 6055 Hohidas profilok | 6056 Hohidas profilok | 6057 Hohidas profilok | 6058 Hohidas profilok | 6059 Nyílászárók | 6064 I hope it is understandable. This query is a result of a 'join'-ed query (see above), but it can contain only those products which are in one or more groups. But I also need the ungroupd items. Pleas tell me how to create such an sql query. Thank you, -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Getting all rows even if not a member of any groups
On Fri, Jun 27, 2003 at 13:13:07 +0200, Együd Csaba <[EMAIL PROTECTED]> wrote: > Hi, Please don't reply to messages to start a new thread. > I have a product table identified by its id field. There is a productgroups > table with productisd, productgroupid fields. And I have a prod_in_pgr > (productid, productgroupid) table which describes the membership of > productgroups. Each product can be a member of zero or more productgroups, > but one productgroup can contain a product only once. > > I would like to list the following information: > productgroupid | productid | ... some other prouduct info | ... > > I need all the products even if it is not a member in any productgroups. I > need these information ordered by productgroup and then productid. > > An example: > > select t_productgroups.name as pgroup, > t_products.id as productid > from t_products > join t_prod_in_pgr on (t_products.id=productid) > join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid) > order by pgroup, productid; I think you want something like: select t_productgroups.name as pgroup, t_products.id as productid from t_products left join (t_prod_in_pgr join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)) on (t_products.id=productid) order by pgroup, productid; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Postgres - Delphi Application
Tomasz, it seems to be interesting for me as well, but actually I can't download a file from the given url. I just click on the binary zip link, but nothing happen. How should I click? :) Thanks, -- Csaba - Original Message - From: "Tomasz Myrta" <[EMAIL PROTECTED]> To: "murali" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, June 27, 2003 9:06 AM Subject: Re: [SQL] Postgres - Delphi Application > Dnia 2003-06-19 19:50, Użytkownik murali napisał: > > > Hi > > > > I' would like to connect my postgres database on a linux server with > > Delphi 5 Applications,which are located on Win95/98 Clients, very > > helpful would be an example on how to realize a connection of these things > > > > Thanks > > > > Murali > > http://sourceforge.net/projects/zeoslib > > Nice solution - your executable needs only small libpq.dll file without any > annoying BDE/ODBC installation. > There is one more native driver for C++ Builder/Delphi - pgexpress, but it's > not free. > > Regards, > Tomasz Myrta > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Getting all rows even if not a member of any groups
Bruno, > Please don't reply to messages to start a new thread. sorry, I will never do such things in the future. > select t_productgroups.name as pgroup, > t_products.id as productid > from t_products > left join (t_prod_in_pgr > join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid)) > on (t_products.id=productid) > order by pgroup, productid; This is absolutelly what I want, but I can't understand how it is working. Where can I find a descriptive (tale-like, for kids ... :) ) documentation about using joins? Thank you wery mauch. -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18. ---(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] Change the behaviour of the SERIAL "Type"
On Fri, Jun 27, 2003 at 10:45:07 +0200, Dani Oderbolz <[EMAIL PROTECTED]> wrote: > > Yea, fine, but I propose a different (deeper) approach. > Why does SERIAL only enforce a DEFAULT? Because it is faster. > This is not an exact imitation of an autoincrement, as a DEFAULT can be > overwritten. There are probably other differences as well, since serial only provides a way to get unique values. If you want more meaning than that you have to be careful. > In my oppinion, SERIAL should implicitly create a Trigger on the table, > which then > handles this transparently. > Would that be difficult? It shouldn't be too difficult to write some triggers that make something closer to autoincrement. It probably won't work very well if there are lots of concurrent updates though. You can either lock the table with the column exclusively and then find the largest value and then use that value plus one. Don't use max for this. Make an index on the autoincrement column and use order by and limit 1 to get the largest value. The other option is to keep the sequence value in other table. You can use select for update to update it. You will want to vacuum this table often enough that it will stay on one page. > (I am already writing a Procedure which gets all the info needed out of > the Catalog, > but my problem is that I need some dynamic statements in there...) > > Cheers, Dani > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Change the behaviour of the SERIAL "Type"
Bruno Wolff III wrote: ... It shouldn't be too difficult to write some triggers that make something closer to autoincrement. It probably won't work very well if there are lots of concurrent updates though. You can either lock the table with the column exclusively and then find the largest value and then use that value plus one. Don't use max for this. Make an index on the autoincrement column and use order by and limit 1 to get the largest value. The other option is to keep the sequence value in other table. You can use select for update to update it. You will want to vacuum this table often enough that it will stay on one page. Well, why not just use the Sequence? Is there really such a performance hit when calling a trigger? In Oracle, one usually does such a thing, as there is no such nice workaround as SERIAL. Hmm, I am still thinking about a special kinf of SERIAL, maybe called TRIGGERED_SERIAL which creates a trigger instead of a DEFAULT. Cheers, Dani ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Change the behaviour of the SERIAL "Type"
> Well, why not just use the Sequence? > Is there really such a performance hit when calling a trigger? > In Oracle, one usually does such a thing, as there is no such nice > workaround > as SERIAL. > Hmm, I am still thinking about a special kinf of SERIAL, maybe called > TRIGGERED_SERIAL which creates a trigger instead of a DEFAULT. DB2, Firebird, MSSQL? and some others have what they call GENERATOR support (IDENTITIES fall into this)-- which also happens to be in the SQL 200N proposals. Main Features (per proposed spec): - Not strictly integers (any expression on any datatype) - Optionally overridable or not -- which is what you're looking for - Attribute of the column. Not a datatype. This is an alternative for DEFAULT. I'm hoping to add IDENTITIES / GENERATOR support along these lines in 7.5, but I've not looked at those other databases to see how close their implementation matches spec -- whether it will make us compatible with them or not. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Getting all rows even if not a member of any groups
On Fri, Jun 27, 2003 at 16:18:10 +0200, Együd Csaba <[EMAIL PROTECTED]> wrote: > > This is absolutelly what I want, but I can't understand how it is working. > Where can I find a descriptive (tale-like, for kids ... :) ) documentation > about using joins? If you look at the documentation for the select command and page down a bit there is a description of join syntax. Note that in 7.4 using the explicit join syntax won't force join order. (This really only affects cross joins and inner joins; left and right joins normally can't be reordered.) You need a left join to pick up products that aren't in any group. The parenthesis changed the join order so that group names were attached to group ids before group ids were joined to products. This can have performance implications. I think that this is probably the faster way, but the other option would to have been to make the second join a left join as well. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Change the behaviour of the SERIAL "Type"
On Fri, Jun 27, 2003 at 16:35:36 +0200, Dani Oderbolz <[EMAIL PROTECTED]> wrote: > Well, why not just use the Sequence? > Is there really such a performance hit when calling a trigger? I think the big issue is concurrency. Sequences are designed so that conncurrent uses of the sequence don't block each other. In the trigger based methods you have to lock the value against concurrent update and this lock will be held to the end of the transaction. This has the potential to really hurt your performance. I may have been misunderstanding what you are trying to do though. If your problem was that people could update or insert values into the serial column that might cause other transactions to unexpected fail (when they try to use a duplicate value - assuming you use a unique index on the column) then you could use a trigger to prevent updates on that column and force inserts to always use nextval. If you need to reload the database at some point, you will need to do something to keep the triggers from interfering with the reload. This should be fairly efficient. I had thought you were concerned about possible gaps in the sequence. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Postgres - Delphi Application
> Dnia 2003-06-27 12:25, U¿ytkownik [EMAIL PROTECTED] napisa³: > >> I think I looked at zeos at some point and found that it did not >> support md5 authentication, which, at the time, I thought was >> important. >> ~Berend Tober > Zeos has nothing to authentication. It's a matter of libpq library, > which currently supports md5 authentication. If you need more security > - you can even compile libpq with ssl. > Tomasz Not sure I was clear. Using zeos, you get a set of Delphi components that connect the database back end with user-interface controls on your Delphi TForm objects, like TDBEdit, TDBMemo, etc. You use those zeos components instead of the standard "messy" BDE database components TDatabase, TQuery, TTable, etc. While the zeos components would successfully connect to the database back end, they would not allow or require database-enforced md5 authentication at the user-interface end. I didn't want to have to invent my own authentication scheme when the database is capable of doing a good job. ~Berend Tober ---(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] Postgres - Delphi Application
> Dnia 2003-06-19 19:50, U¿ytkownik murali napisa³: > > I' would like to connect my postgres database on a linux server with > Delphi 5 Applications,which are located on Win95/98 Clients, very > helpful would be an example on how to realize a connection of these > things > > http://sourceforge.net/projects/zeoslib > > Nice solution - your executable needs only small libpq.dll file without > any annoying BDE/ODBC installation. I think I looked at zeos at some point and found that it did not support md5 authentication, which, at the time, I thought was important. ~Berend Tober ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Inheritance and standards
Hi, in how far are the Table Inheritance features of PostgreSQL SQL92 or SQL99? What other databases support table inheritance? Do they use the same syntax? Thanks -- Markus Bertheau Cenes Data GmbH ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] need some help with a delete statement
Hi, I have a bunch of records that I need to delete from our database.
These records represent shopping carts for visitors to our website.
The shopping carts I'd like to delete are the ones without anything in
them. Here is the schema:
create sequence carts_sequence;
create table carts(
cart_id integer default nextval('carts_sequence') primary key,
cart_cookie varchar(24));
create sequence cart_contents_sequence;
create table cart_contents(
cart_contents_id integer default nextval('cart_contents_sequence')
primary key,
cart_id integer not null,
content_id integer not null,
expire_time timestamp);
I'm trying to use this query to delete the carts that are not
referenced from the cart_contents table.
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents));
My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
v_carts and only 3746 entries in v_cart_contents. Clearly there are a
very large number of empty carts. Running the delete statement above
runs for over 15 minutes on this machine. I just cancelled it because
I want to find a faster query to use in case I ever need to do this
again. While the query is running the disk does not thrash at all. It
is definitely CPU bound.
Limiting the statement to 1 item takes about 12 seconds to run:
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1);
Time: 12062.16 ms
Would someone mind showing me a query that would perform this task a
little faster? Any help would be greatly appreciated.
-M@
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need some help with a delete statement
On Fri, Jun 27, 2003 at 16:09:31 -0700, Matthew Hixson <[EMAIL PROTECTED]> wrote: > Hi, I have a bunch of records that I need to delete from our database. > These records represent shopping carts for visitors to our website. > The shopping carts I'd like to delete are the ones without anything in > them. Here is the schema: IN is slow in 7.3.3 and below. It will be substantially faster in 7.4. In the meantime rewriting your query to use not exists will probably speed things up for you. Delete also allows for joins with other tables which doesn't help in thsi particular case (at least not any way I can think of), but is help for deleting items there are in (as opposed to are not in) another table. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Question on OUTER JOINS.
Hi: 1) Is the ON clause of an OUTER JOIN always evaluated first before the WHERE clause? 2) Given the ff SQL statement : SELECT employee_id, a.status as status FROM permissions a LEFT JOIN (select * from employee where employee_id = 3) as b on (a.status=b.status) WHERE a.status='test'; Is there a way to rewrite the query as a view such that one can do: select * from test_view where employee_id=3 and status='test'; Thank you very much, ludwig lim __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Question on OUTER JOINS.
Ludwig Lim <[EMAIL PROTECTED]> writes: > 1) Is the ON clause of an OUTER JOIN always > evaluated first before the WHERE clause? No; the planner will do whatever it thinks is the most efficient way (assuming it can prove that the reordering it wants to do won't change the query result). > Is there a way to rewrite the query as a view such > that one can do: I'm really not clear on what you want here. Better example please? regards, tom lane ---(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] Question on OUTER JOINS.
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Ludwig Lim <[EMAIL PROTECTED]> writes: > > 1) Is the ON clause of an OUTER JOIN always > > evaluated first before the WHERE clause? > > No; the planner will do whatever it thinks is the > most efficient way > (assuming it can prove that the reordering it wants > to do won't change > the query result). If re-ordering does change the result, is the ON clause evaluated first and the WHERE filters out the result of the OUTER JOIN? > > Is there a way to rewrite the query as a view > such > > that one can do: > > I'm really not clear on what you want here. Better > example please? > Sorry for not making it that clear. Is there way of rewritting : SELECT a.status, employee_id FROM permission a LEFT JOIN ( SELECT * FROM employee WHERE employee_id =5) as b ON (a.status = b.status) WHERE status='test' into a query that has no subselect in the FROM clause. I mean can the query above be rewritten into something like: SELECT a.status, b.employee_id FROM permission a LEFT JOIN employee b ON (a.status = b.status) WHERE a.status = 'test' and b.employee_id = 5; Thank you very much, ludwig __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
