Re: [SQL] Concatenate fields
I've tried it and it works well, thanks a lot Oliver. Amanda Oliver Elphick wrote: > Amanda Riera wrote: > >I would like concatenate some fields to have all information in just > >one field. I'm doing this below: > > > >CREATE TABLE bill_2col AS > >SELECT bill.bill_id, > > (trim(text(bill.bill_number)) || ' | ' || > > trim(text(provider.company)) || ' | ' || > > trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || > > trim(to_char(bill.amount,'999.99')) || ' pts') AS billdesc > >FROM bill, provider > >WHERE bill.provider_id = provider.provider_id > >ORDER BY bill.bill_id; > > > >When it finds some empty field, it makes all the new field empty, no > >matters > >if the other are empty or not. > > In this case, empty means NULL. Any concatenation involving NULL returns > NULL; this is according to the standard. > > Use COALESCE(field,'') to return an empty string if field is NULL, so > that no NULLs go into the concatenation. > > -- > 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 > > "Is any one of you in trouble? He should pray. Is > anyone happy? Let him sing songs of praise. Is any one > of you sick? He should call the elders of the church > to pray over him...The prayer of a righteous man is > powerful and effective." James 5:13,14,16 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] maybe Offtopic : PostgreSQL & PHP ?
Hi all, sorry for the maybe offtopic questions, but I did not get any answer
from the PHP ML.
I would like to query a postgresql db with the php language.
Today I wrote a script (connectandquery.php) performing the following :
- connect to the DB : $conn = pg_Connect("dbname = foo");
- execute the query : $res = pg_Exec($conn,"SELECT * from BAR");
But I would like to write this in two scripts :
- connect.php : $conn = pg_Connect("dbname = foo");
- query.php : $res = pg_Exec($conn,"SELECT * from BAR");
but I don't know how to get the $conn variable (defined in connect.php) in
the script query.php.
Any idea is welcome !
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Subqueries in select clause
Hi, I am attempting to use subqueries in the select clause of a query and am encountering difficulties. The Problem: I would like to use a subquery that returns one column, but more than one tuple. The result of this subquery is then used in an aggregate function. For example, suppose I have a table c, with columns a and b of numbers. Then I would like to be able to write something of the style: select max((select count(b) from c group by a)); However, when I try it, I get an error message: ERROR: More than one tuple returned by a subselect used as an expression. I actually need to use this type of construct in many cases, some of which have subqueries using values that appear in the outer query (i.e., correlated subqueries). Thus, it would be difficult for me to simply create a temporary table with the value of the inner query and then use it to solve the query I need. (Since I need to translate automatically from queries with the above type of constructs to something that will run on Postgresql.) Using Oracle, I could get by this problem with: select max(d) from ((select count(b) as d from c group by a)); However, my version of postgres doesn't support subqueries in the from clause. My Version: --- sarina=> select version(); version - PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96 (1 row) Perhaps this has been solved in a later version? Any suggestions on what to do will be greatly appreciated! Sara Department of Computer Science The Hebrew University Jerusalem, Israel ---(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] maybe Offtopic : PostgreSQL & PHP ?
Not quite sure how your code is organised...
but you could access the variable $conn by including "connect.php" into the
"query.php" script.
Otherwise, you will need to use persistent connections... which can be
achieved via pg_pconnect...
a persistent connection will instead of creating a new database connection
each time.. it will try to use
an existing connection that is no longer being used (persistent connections
do tend to have a lot of quirks tho)
Keith
At 11:33 AM 18/04/2001 +0200, Picard, Cyril wrote:
>Hi all, sorry for the maybe offtopic questions, but I did not get any answer
>from the PHP ML.
>
>I would like to query a postgresql db with the php language.
>Today I wrote a script (connectandquery.php) performing the following :
>- connect to the DB : $conn = pg_Connect("dbname = foo");
>- execute the query : $res = pg_Exec($conn,"SELECT * from BAR");
>
>
>But I would like to write this in two scripts :
>- connect.php : $conn = pg_Connect("dbname = foo");
>- query.php : $res = pg_Exec($conn,"SELECT * from BAR");
>
>but I don't know how to get the $conn variable (defined in connect.php) in
>the script query.php.
>
>Any idea is welcome !
>
>---(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] maybe Offtopic : PostgreSQL & PHP ?
On Wed, Apr 18, 2001 at 10:35:09PM +1000, Keith Wong wrote: > an existing connection that is no longer being used (persistent connections > do tend to have a lot of quirks tho) What quirks? I am developing a PHP now and using persistant connections. Coming from the AOLserver/OpenNSD world -- where ALL connections are persistant, pooled, and dstributed among threads -- I'd be interested in knowing the quirks of persistant connections in PHP. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I may be fat, but you are ugly, and I can diet... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Subqueries in select clause
Sara Cohen <[EMAIL PROTECTED]> writes: > Using Oracle, I could get by this problem with: > select max(d) from ((select count(b) as d from c group by a)); > However, my version of postgres doesn't support subqueries in the from > clause. Time to update to 7.1... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Subqueries in select clause
On Wed, 18 Apr 2001, Sara Cohen wrote: > The Problem: > > > I would like to use a subquery that returns one column, but more than one > tuple. The result of this subquery is then used in an aggregate function. > > For example, suppose I have a table c, with columns a and b of > numbers. Then I would like to be able to write something of the style: > > select max((select count(b) from c group by a)); > > However, when I try it, I get an error message: > > ERROR: More than one tuple returned by a subselect used as an expression. Yeah, technically I think the spec (at least my sql92 draft) guards against this by saying that the set functions can't take set functions or subqueries. > I actually need to use this type of construct in many cases, some of which > have subqueries using values that appear in the outer query (i.e., > correlated subqueries). Thus, it would be difficult for me to simply > create a temporary table with the value of the inner query and then use it > to solve the query I need. (Since I need to translate automatically from > queries with the above type of constructs to something that will run on > Postgresql.) > > Using Oracle, I could get by this problem with: > > select max(d) from ((select count(b) as d from c group by a)); Upgrade to 7.1 :) Actually, technically for postgres it'll be: select max(d) from (select count(b) as d from c group by a) e; It enforces the requirement of naming the subqueries. However in 7.0, you *might* be able to do something like: select count(b) as d from c group by a order by 1 desc limit 1; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Subqueries in select clause
Sara Cohen <[EMAIL PROTECTED]> said: > Hi, > > I am attempting to use subqueries in the select clause of a query > and am encountering difficulties. > > The Problem: > > > I would like to use a subquery that returns one column, but more than one > tuple. The result of this subquery is then used in an aggregate function. > Using Oracle, I could get by this problem with: > > select max(d) from ((select count(b) as d from c group by a)); > > However, my version of postgres doesn't support subqueries in the from > clause. In 7.1 at least you can do it if you alias the sub-query: select max(d) from (select count(b) as d from c group by a) as calias; Sorry, I don't know if 7.0.2 handles this (but you probably want to upgrade anyway - I seem to remember some problems with 7.0.2) HTH - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
On Wed, Apr 18, 2001 at 04:44:36PM +0200, Mathijs Brands wrote: > I've written several applications with Apache/PHP. At first I was using > persistant connections, but I was soon forced to revert to normal db > connects. The problem is the number of connections opened. If you have > 50 Apache processes and 5 different databases, it is highly likely that > you will end up (if you leave your application running long enough) with > 250 persistant db links; not something pgsql really likes, unless you > configure it to correctly handle large numbers of db links. Apache/PHP > doesn't provide you with some way to pool connections :( Ah, I see the problem. When you mentioned there were problems I thought you were talking about the connections themselves. I am using PHP 4 under AOLserver/OpenNSD, which has been fully threaded from scratch (1994) so I hope this won't be much of an issue (but then PHP 4 still has threading problems AFAIK). Not using persistant connections is just too slow for me. It's instantaneous for AOLserver to generate a page, when the same page with the connection overhead in PHP takes A LOT longer. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Tetris tagline: @@ o@o @oo oo@ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
Folks, Not to be a list-nazi or anything, but can we move this discussion to the PHP list? These issues are already part of the PHP list archives. If anyone is interested in Postgres-PHP topics, the PHP list is still quite low-traffic. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Subqueries in select clause
Sara, Hey! Great to see that Postgres has made it to Israel. What's the most popular Linux distribution there? I think you have your answer ... an upgrade. RPMs for most major distributions of Linux should be available within the week. Until you can upgrade, though, try this approach: CREATE VIEW c_aggregate AS SELECT sum(a) as sum_a, b FROM c GROUP BY b; SELECT max(sum_a) FROM c_aggregate; Not as fast or dynamic as a subselect, but should solve your immediate problem. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 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
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
On Wed, Apr 18, 2001 at 08:54:24AM -0600, Roberto Mello allegedly wrote: > Ah, I see the problem. When you mentioned there were problems I > thought you were talking about the connections themselves. > I am using PHP 4 under AOLserver/OpenNSD, which has been fully > threaded from scratch (1994) so I hope this won't be much of an issue (but > then PHP 4 still has threading problems AFAIK). > Not using persistant connections is just too slow for me. It's > instantaneous for AOLserver to generate a page, when the same page with the > connection overhead in PHP takes A LOT longer. So how does AOLserver/PHP handle connections then? Is AOLserver managing (pooling) connections for PHP or something like that (like websphere and nas do for Java programs)? A big problem (in my opinion) with Apache is that you cannot limit the number of Apache processes that acquire database links. For instance, it would be great if I could instruct Apache to direct PHP request to a maximum of, say, 20 dedicated processes. As it stands now, your best bet is probably a two step approach; have a reverse proxy (such as a very lean apache installation) or some other proxy (such as a loadbalancer) redirect requests to multiple webservers, some of which have the pgsql PHP module. Another approach we've used for our own development environment (DBL) is using a SQL proxy. We've placed a proxy between the webservers and the databases. It provides multiplexing of connections and caching of queries. Fail-over or load balancing is not implemented, but not that difficult in our case, since our webservers do not directly update the databases themselves. There's an Open Source product that provides similar functionality (I think it's called SQL Relay or something similar). There maybe be some commercial offerings for Open Source databases such as pgsql and mysql, but I haven't looked into that. Of course, having lots of db connections may not be necessary. We're running one of the bigger news sites in Holland. It's implemented using Java servlets, run without big-gun application servers such as WAS or NAS (just the M5 webserver) and it uses PostgreSQL 7.0 for it's data storage (both images and xml). It runs on a fairly small server and is able to sustain more than 600,000 hits daily without breaking out in a sweat. Of course, caching is a big thing, since actually generating a page may take a second or more, but due to the way we maintain and prime the cache, the users normally don't notice. However, restarting the (web) server is a bitch though, since it usually takes 10-15 minutes to build up a cache big enough to be able to serve most request directly from the cache. It may be a fairly common setup, but I quite like the way this system (built by two collegues) turned out. It was our second attempt at using pgsql for a bigger production site and while we ran into a lot of problems, in the end it worked out very well :) Regards, 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 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] Subqueries in select clause
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Until you can upgrade, though, try this approach: > CREATE VIEW c_aggregate AS > SELECT sum(a) as sum_a, b FROM c GROUP BY b; > SELECT max(sum_a) FROM c_aggregate; Unfortunately that won't work in 7.0 --- grouped views have a lot of problems in that version, and one of the problems is that you can't do another level of aggregating on their results. Basically a view and a subselect are the same thing, so you can't get around the restrictions of one by using the other... 7.1 is what Sara needs. 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] breakage in schema with foreign keys between 7.0.3 and 7.1
Hello everyone me again (apologies in advance :). I have been running a database under 7.0.3 for some months now, and it was all fine. The tables all loaded and it was working flawlessly. Then 7.1 came out and I noticed it had outer joins (which are a big win in one of the main views i use). So, i started loading in the schema into 7.1, but it seems to break. Now, i have included the 3 tables below, but first i would like to tell some of the design criteria behind this. 1) I need to have order_id as a primary key across the system (system key ?) so that i can pull out based on an order_id. The same goes for history_id in the client. 2) I also need to have the client_id as a secondary key across the system, as another application frontend references on client_id. its icky but it works. 3) i have taken out some of the non-important fields, so please dont tell me that i have over-normalised my data ;p for some reason though, under 7.1 when trying to get the tables i get this error -> UNIQUE constraint matching given keys for referenced table "client" not found. I know what it is saying, but i dont quite understand what has changed between 7.0.3 and 7.1 CREATE TABLE action ( ORDER_IDintegerPRIMARY KEY, ORDERTYPE integerNOT NULL, client_idchar(16)NOT NULL, priority integerDEFAULT 5 NOT NULL, creation_idnamedefault user, creation_datedatetime default now(), close_id nameNULL, close_datedatetime NULL, lock_id nameNULL, lock_date datetime NULL ) \g CREATE TABLE client ( ORDER_IDinteger REFERENCES action (ORDER_ID) ON UPDATE CASCADE INITIALLY DEFERRED, history_id SERIAL, active boolean, client_id char(16)NOT NULL, change_id nameDEFAULT USER, change_date datetimeDEFAULT NOW(), PRIMARY KEY (ORDER_ID,history_id) ) \g CREATE TABLE client_dates ( ORDER_IDinteger REFERENCES action (ORDER_ID) ON UPDATE CASCADE INITIALLY DEFERRED, LOCATION_ID integer NOT NULL, history_id integer REFERENCES client (history_id) ON UPDATE CASCADE INITIALLY DEFERRED, active boolean, client_id char(16)REFERENCES client (client_id) ON UPDATE CASCADE INITIALLY DEFERRED, dte_action integer NULL, change_id nameDEFAULT USER, change_date datetimeDEFAULT NOW(), PRIMARY KEY (ORDER_ID,LOCATION_ID,history_id) ) \g thank you, i know its something almost smackingly obvious but i cant seem to understand why it was working and now isnt. i even went through the changelog! regards Stef ---(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] RTREE on points
Jeff, I checked the archive and it works with 7.1 release we've implemented only several functions for box, so you may use them as example for remaining types. Regards, Oleg On Wed, 18 Apr 2001, Oleg Bartunov wrote: > On Tue, 17 Apr 2001, Jeff Hoffmann wrote: > > > Oleg Bartunov wrote: > > > > > > GiST is great ! > > > > > > You may look at http://www.sai.msu.su/~megera/postgres/gist/ > > > for GiST implementation of RTree - it could be not compiled with 7.1 > > > release due to some api changes, but it's not difficult to do. > > > > it looks like i just wasted a good couple of hours trying to convert the > > gist_box_ops. it did help find the pointer problem i was having because > > i'm still not up to speed on the new function calling conventions, > > though... > > > > :-) > > > > If somebody want it I could contribute it to contrib area. > > > > i'm definitely interested. i'm going to play with it & if oleg's claim > > holds about index insertion time holds, i can definitely see myself > > moving to it over the built in rtree. anything that can cut down the > > hours of index creation time would be great. also, it seems that it'd > > be a good choice for inclusion in the standard distribution because it'd > > be easy to test -- you already have to run rtree tests anyway, you can > > just duplicate them with gist & gist_box_ops. > > > > nice you noticed that ! We'll update contrib-rtree_box_gist for 7.1 release > in a few days. > > > > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] RTREE on pointsy
I believe Marc install the GIST code into the tree long ago. > Jeff Hoffmann <[EMAIL PROTECTED]> writes: > > I know there are a couple of GiST examples in contrib (seg, cube & > > intarray), but i thought there used to be at least a gist_box_ops. > > I don't recall any such thing having been removed, but it does seem > peculiar that there are no GIST opclasses in the standard distribution. > How the heck did the GIST index code get developed/tested without some > opclasses? > > Anyone remember the history? AFAICT from the CVS logs, GIST was added > to the tree in mid 1996, but no opclasses for it were added at the time. > I'd go digging in the maillist archives, but www.postgresql.org is too > friggin' slow at the moment ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] breakage in schema with foreign keys between 7.0.3 and7.1
On Wed, 18 Apr 2001, Stef Telford wrote: > CREATE TABLE action > ( > ORDER_IDintegerPRIMARY KEY, > ORDERTYPE integerNOT NULL, > client_idchar(16)NOT NULL, > priority integerDEFAULT 5 NOT NULL, > creation_idnamedefault user, > creation_datedatetime default now(), > close_id nameNULL, > close_datedatetime NULL, > lock_id nameNULL, > lock_date datetime NULL > ) \g > > CREATE TABLE client > ( > ORDER_IDinteger REFERENCES action > (ORDER_ID) > ON UPDATE CASCADE > INITIALLY DEFERRED, > history_id SERIAL, > active boolean, > client_id char(16)NOT NULL, > change_id nameDEFAULT USER, > change_date datetimeDEFAULT NOW(), > PRIMARY KEY (ORDER_ID,history_id) > ) \g > > CREATE TABLE client_dates > ( > ORDER_IDinteger REFERENCES action > (ORDER_ID) > ON UPDATE CASCADE > INITIALLY DEFERRED, > LOCATION_ID integer NOT NULL, > history_id integer REFERENCES client > (history_id) > ON UPDATE CASCADE > INITIALLY DEFERRED, > active boolean, > client_id char(16)REFERENCES client > (client_id) > ON UPDATE CASCADE > INITIALLY DEFERRED, > dte_action integer NULL, > change_id nameDEFAULT USER, > change_date datetimeDEFAULT NOW(), > PRIMARY KEY (ORDER_ID,LOCATION_ID,history_id) > ) \g > > > thank you, i know its something almost smackingly obvious but > i cant seem to understand why it was working and now isnt. i even went > through the changelog! Hmm, don't know why it's not in changelog, but the spec requires that the target fields of a foreign key constraint are themselves constrained by a unique or primary key constraint. 7.0 didn't actually check this, but 7.1 does. The reason for this is because while 7.0 would let you specify such a constraint, it wouldn't really work entirely properly if the field wasn't actually unique. You'll need a unique constraint on client.client_id. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] breakage in schema with foreign keys between 7.0.3 and 7.1
Stephan Szabo wrote: > Hmm, don't know why it's not in changelog, but the spec requires that > the target fields of a foreign key constraint are themselves constrained > by a unique or primary key constraint. maybe its time for me to go and re-read the changelog with a fine tooth comb (it has been known for me to be blind to the obvious before and if this is the case then i more than apologise :) > 7.0 didn't actually check this, > but 7.1 does. The reason for this is because while 7.0 would let you > specify such a constraint, it wouldn't really work entirely properly > if the field wasn't actually unique. You'll need a unique constraint > on client.client_id. hhrrm. the only problem with -that- is that client_id by itself is not unique, but in conjunction with order_id it is. order_id is wholly unique. maybe i should jst drop the foreign key on client_id then, although i did want to use referential integrity on the client_id on an insert. although now i think about this, the criteria for having the changes on client_id cascading are totally gone and i could (read will) jst use a 'references' column. in short, thank you, i have jst figured out what an idiot i have been (again i hear you all say ;) many thanks and good work on postrgresql 7.1, it seems to be quite a bit quicker (and praise the lord for outer joins =) stefs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] groups in postgres
I building an application that will load data on a database by lots of people. So I thought about putting them in a group and giving priviledges to the group. The problem is the the information on the main table is related with fields of other tables, and depending on how it's related, I want some users to be able to modify the data and I don't want those users to insert new data if the relation is right for them. Am I clear up to know? Now, how can I do this? Is it posible manipulating groups? Or do I have to play with triggers. Saludos... :-) -- El mejor sistema operativo es aquel que te da de comer. Cuida tu dieta. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Re: DB porting questions...
On Wed, 18 Apr 2001, Diehl, Jeffrey wrote: > Querying the entire database is difficult, and very important to me. > I tried to setup a "view of a union" scheme. That isn't supported. > Right now I am using a perl function to rewrite my sql in such a way > that it queries any of the tables I want and coelesces the output. > Aggregate functions even work with this method. To get around this same problem, I created a base table from which all other tables were inherited. Doing this allows for you to query on 1 table, or all data easily. i.e. CREATE TABLE wwwlogs (id INT4, url TEXT); CREATE TABLE wwwlogs_041801 INHERITS (wwlogs); CREATE TABLE wwwlogs_041701 INHERITS (wwlogs); HTH... Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio > Querying the entire database is difficult, and very important to me. I > tried to setup a "view of a union" scheme. That isn't supported. Right now > I am using a perl function to rewrite my sql in such a way that it queries > any of the tables I want and coelesces the output. Aggregate functions even > work with this method. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Timezone conversion
Convert it to timestamp and minus TIMEZONE * 3600... On Wed, 11 Apr 2001, Roberto Mello wrote: > Hi, > > How can I do timezone conversions in PG? I looked on the docs and > couldn't find how. I want to find the current time in another timezone. > > Thanks, > > -Roberto > > P.S: This type of function would be excellent on the cookbook > (www.brasileiro.net). > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] any proper benchmark scripts?
hello on the modperl list a good thread was happening called 'fast db access' find attached scripts used to do this here are my results: [drfrog]$ perl fast_db.pl postgres 16 wallclock secs ( 0.05 usr + 0.00 sys = 0.05 CPU) @ 400.00/s (n=20) mysql 3 wallclock secs ( 0.07 usr + 0.00 sys = 0.07 CPU) @ 285.71/s (n=20) postgres 17 wallclock secs ( 0.06 usr + 0.00 sys = 0.06 CPU) @ 333.33/s (n=20) mysql 3 wallclock secs ( 0.01 usr + 0.01 sys = 0.02 CPU) @ 1000.00/s (n=20) correct me if im wrong but if fast_db.pl is working right first set is insert second set is select comp stats running stock rpms from mandrake 7.2 for both postgresql and mysql 3.23.23-beta of mysql and 7.02 of postgresql [drfrog@nomad desktop]$ uname -a Linux nomad.localdomain 2.2.18 #2 Tue Apr 17 22:55:04 PDT 2001 i686 unknown [drfrog]$ cat /proc/meminfo total:used:free: shared: buffers: cached: Mem: 257511424 170409984 87101440 24219648 96067584 44507136 Swap: 2549432320 254943232 MemTotal:251476 kB MemFree: 85060 kB MemShared:23652 kB Buffers: 93816 kB Cached: 43464 kB SwapTotal: 248968 kB SwapFree:248968 kB [drfrog]$ cat /proc/cpuinfo processor : 0 vendor_id : AuthenticAMD cpu family : 6 model : 3 model name : AMD Duron(tm) Processor stepping: 1 cpu MHz : 697.535 cache size : 64 KB fdiv_bug: no hlt_bug : no sep_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 1 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 psn mmxext mmx fxsr 3dnowext 3dnow bogomips: 1392.64 i will recomp both the newest postgresql and mysql not using any optimizing techs at all i'll post the config scripts i use -- back in the day we didn't have no old school -dr. frog http://www.hyperbomb.com it sells itself fast_db.pl benchmark.sql ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
