[SQL]
Hello everyone, Good day! Could anyone help me translate this query in Microsoft Access to Postgresql. I'm having a difficulty. Pls... Query1: SELECT items.description, Sum(supplieditems.qty) AS SumOfqty FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno GROUP BY items.description; Query2: SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER JOIN customer ON [OtherItem].[Itemno]=[customer].[itemcode] GROUP BY [items].[description]; Query3: SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain FROM Query1, Query2; I have translated Query1 and Query2 in POSTGRESQL but I don't know how to implement Query3. Are you a student of MSU-IIT? Log on http://my.msuiit.edu.ph and sign up for your My.MSUIIT E-mail Plus account now! Are you an MSU-IIT alumnus? Log on http://www.alumni.msuiit.edu.ph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] query problem
Hello everyone, Good day! Could anyone help me translate this query in Microsoft Access to Postgresql. I'm having a difficulty. Pls... Query1:SELECT items.description, Sum(supplieditems.qty) AS SumOfqtyFROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemnoGROUP BY items.description; Query2:SELECT [items].[description], Sum([customer].[qty]) AS SumOfqtyFROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER JOIN customer ON [OtherItem].[Itemno]=[customer].[itemcode]GROUP BY [items].[description]; Query3:SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remainFROM Query1, Query2; I have translated Query1 and Query2 in POSTGRESQL but I don't know how to implement Query3.Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger
Re: [SQL] search facilities
Check contrib/tsearch2 and http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2 for documentation Oleg On Fri, 7 Nov 2003, Martin Kuria wrote: > Hi, > I have a content management system, for my database driven website developed > using php and postgresql but I don't know how to develop a search facility > for it. > > How do I go about it?. > > I have seen database driven website developed using php and postgresql and > they have their built search facilities where can I learn to develop my > customized search facility please do advice. > > Kind Regards > > +-+ > | Martin W. Kuria (Mr.) [EMAIL PROTECTED] > ++ > > _ > Tired of spam? Get advanced junk mail protection with MSN 8. > http://join.msn.com/?page=features/junkmail > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > 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 8: explain analyze is your friend
Re: [SQL] search facilities
Download the latest version of PostgreSQL and look in the contrib/tsearch or contrib/tsearch2 directories. For documentation, see: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ http://sourceforge.net/mailarchive/forum.php?forum_id=7671 George Essig ___ Original Message: Hi, I have a content management system, for my database driven website developed using php and postgresql but I don't know how to develop a search facility for it. How do I go about it?. I have seen database driven website developed using php and postgresql and they have their built search facilities where can I learn to develop my customized search facility please do advice. Kind Regards +-+ | Martin W. Kuria (Mr.) [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] off subject - pg web hosting
can anybody recomend web hosting that provides postgresql? I have found a couple, but their pricing is several times the going rate using mySql. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(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
[SQL] SQL to PLC to diverter gate, can this be done?
First, I know nothing about PLC's, but I was wondering is there a way to extract data from a SQL database file, feed that to a PLC is some manner, and then have the PLC trigger a diverter gate based on the value in the table exceeding a certain value, to divert parts to another location. Am I off base, or is there something out there that will allow or help me to do this. Any help would be greatly appreciated. ---(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] No output while using PEAR DB & formatted time-string
I have a problem in using the to_char function in a SQL-Statement
and addressing it with DB_FETCHMODE_OBJECT under PEAR DB.
The follwing code is working fine unless I'm adding the to_char function:
--
require_once 'DB.php';
$dsn = $$$;
$db = DB::connect($dsn);
$db->setFetchMode(DB_FETCHMODE_OBJECT);
$query .= "SELECT news_id,to_char(news_time,'DD.MM. HH24:MI'), ";
$query .= "FROM News ";
$query .= "WHERE news_id=$var";
$sth = $db->query($query);
while($newsid_row=$sth->fetchRow()) {
print <news_time
blabla
HTML;
}
$db->disconnect();
--
Has anybody an idea to get this working or similar experiences?
Thanks for your patience; could be the wrong newsgroup!?
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Problems with NEW.* in triggers
On Tue, 04 Nov 2003, Tom Lane wrote: > Jamie Lawrence <[EMAIL PROTECTED]> writes: > > I don't understand why moddate isn't getting set to now() in the above. > > Josh fingered the problem there --- you need a BEFORE trigger if you > want to affect the data that will be stored. I had missed that little > mistake :-( Yep. Many thanks to both of you. -j -- Jamie Lawrence[EMAIL PROTECTED] If built in great numbers, motels will be used for nothing but illegal purposes. - J. Edgar Hoover ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] 7.3 how remove password valid until
How remove a password validity ? CREATE USSER x WITH VALID UNTIL 'uu' ALTER USSER x WITH VALID UNTIL 'uu' but how remove password valid until ? Thank Alban ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] No output while using PEAR DB & formatted time-string
Am 01.11.2003 12:00 meinte Marcus Krause: > $query .= "SELECT news_id,to_char(news_time,'DD.MM. HH24:MI'), "; The real code is without the comma at the end of the line above, so it's not a simple SQL-Statement problem! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] 7.3 : how add user when last user have lost 'createuser' option
i have execute ALTER USER postgres WITH NOCREATEUSER; it's work very well but now, i cant add, drop, alter user, alter group... ALTER USER postgres WITH CREATEUSER; --> ERROR : ALTER USER : permission denied there is a solution ? Thank ALban ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] How to know column constraints via system catalog tables
Hi, I need to query each column's constraint and name of a table in postgreSQL v7.3.4 with a single SQL query but don't know how. Would appreciate any pointers! Thank you. Regards, Damon ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] 7.3 how remove password valid until
alban wrote: > How remove a password validity ? > > > CREATE USSER x WITH VALID UNTIL 'uu' > ALTER USSER x WITH VALID UNTIL 'uu' > but how remove password valid until ? I think you have to specify the data as 'infinity'. test=> CREATE USER x WITH VALID UNTIL '2005-01-01'; CREATE USER test=> ALTER USER x WITH VALID UNTIL 'infinity'; ALTER USER -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 7.3 : how add user when last user have lost 'createuser' option
alban wrote: > i have execute > ALTER USER postgres WITH NOCREATEUSER; > it's work very well but now, i cant add, drop, alter user, alter group... > > ALTER USER postgres WITH CREATEUSER; > --> ERROR : ALTER USER : permission denied > > there is a solution ? Uh, you removed createuser permission from the postgres super-user. I think that's why you are getting the failure. I think you have to manually update pg_shadow to fix this, if it will allow that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 7.3 : how add user when last user have lost 'createuser' option
Bruce Momjian <[EMAIL PROTECTED]> writes: > alban wrote: >> i have execute >> ALTER USER postgres WITH NOCREATEUSER; > Uh, you removed createuser permission from the postgres super-user. That was a really bad move :-( If you have no superusers left, I think your only option is to shut down the postmaster, start a standalone backend, and ALTER USER postgres back to superuser status in the standalone backend. Then you can restart the postmaster. IIRC this will work in 7.3 and probably 7.2. If you're running something older, you are well and truly hosed, because you can't reclaim superuser status even with a standalone backend. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] off subject - pg web hosting
http://www.hub.org http://www.pghoster.com http://www.commandprompt.com On Thu, 6 Nov 2003, chester c young wrote: > can anybody recomend web hosting that provides postgresql? I have > found a couple, but their pricing is several times the going rate using mySql. > > __ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > > ---(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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] off subject - pg web hosting
http://www.iniquinet.com When grilled further on (Thu, 6 Nov 2003 16:39:14 -0800 (PST)), chester c young <[EMAIL PROTECTED]> confessed: > can anybody recomend web hosting that provides postgresql? I have > found a couple, but their pricing is several times the going rate using mySql. > -- 15:58:42 up 99 days, 9:18, 4 users, load average: 2.00, 2.00, 2.00 pgp0.pgp Description: PGP signature
Re: [SQL] plpgsql question
Rich, > I don't want to abuse you - so if this is the wrong forum for this, feel > free to toss it back with direction :-) No, but I do think that you should CC a mailing list becuase: 1) Someone else may have direct experience with your problem and answer more quickly than me, and: 2) Other people will want to read your issue and share in the solution. Therefore I've cc'd the SQL list. Hope that's OK. I've omitted the full text of your procedure in case it contains proprietary info. > I think I might be running up against an error with plpgsql in temrs of seg > faults with null values in stored procs. Here is my stored proc, the > error, and the datatype definintion: > 1. I am using 7.3 btw > 2. WARNING: Error occurred while executing PL/pgSQL function > getcaseaccounttransactionlist > WARNING: line 44 at return next > ERROR: Wrong record type supplied in RETURN NEXT Um, that's a regular error. How is it a Seg Fault? >From the error, the frist thing I'd suggest you do is to check carefully into each of the column types and order in your query and the return type. I'll bet that one column is the wrong type or is transposed. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] plpgsql question
Rich, > That was my thought - I was fairly sure I fixed it though. Do, really > need to be specific in terms of type in terms of int, or can I put numeric, INT and numeric are fairly different, and I believe that SRF return types are very fussy about data types; I wouldn't be surprised if you got an error for using an INT4 in place of an INT8. > and for varchar(30) I can just put varchar in my type definition. That I don't think will be a problem; varchar limits are indifferently supported anyway. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] pg 7.4.rc1, Range query performance
Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06. All configuration settings are default. Hi, Trying to find a way to improve range query performance. The table Test has about 30 million records. -- DLong, Dtimestamp, Dint, etc are domains of the respective types. create table Test ( id DLong not null, a Dtimestamp null, b Dintnot null, c Dintnot null, d Dstring null, constraint PK_id primary key (id), constraint AK_abc unique (a, b, c) ); The following query retrieves a single record, it runs against AK index and is extremely fast (30-150 ms) for the table of this size: -- returns result in 30-150 ms select * from Test where a = '2002-09-01' and b = 5 and c = 255 OTOH, the following range query that returns 30 records performs much slower, about 33000 ms. The query is using AK index, as it should, but why does it take so much longer to scan the index for the range of just 30 records? I see that PG is hitting the disk very intensively for this query. Can the query be rewritten, etc to improve performance? Thanks select * from Test where a >= '2002-09-01' and a <= '2002-09-30' and b = 5 and c = 255 QUERY PLAN Index Scan using ak_abc on test (cost=0.00..106.27 rows=30 width=53) (actual time=33.536..33200.998 rows=30 loops=1) Index Cond: (((a)::timestamp without time zone >= '2002-09-01 00:00:00'::timestamp without time zone) AND ((a)::timestamp without time zone <= '2002-09-30 00:00:00'::timestamp without time zone) AND ((b)::integer = 5) AND ((c) (..) Total runtime: 33201.219 ms __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg 7.4.rc1, Range query performance
Try CLUSTER --- that usually helps with index scans on ranges. --- ow wrote: > Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06. > All configuration settings are default. > > > Hi, > > Trying to find a way to improve range query performance. > > The table Test has about 30 million records. > > -- DLong, Dtimestamp, Dint, etc are domains of the respective types. > create table Test ( > id DLong not null, > a Dtimestamp null, > b Dintnot null, > c Dintnot null, > d Dstring null, > constraint PK_id primary key (id), > constraint AK_abc unique (a, b, c) > ); > > The following query retrieves a single record, it runs against AK index and is > extremely fast (30-150 ms) for the table of this size: > > -- returns result in 30-150 ms > select * from Test > where a = '2002-09-01' > and b = 5 > and c = 255 > > OTOH, the following range query that returns 30 records performs much slower, > about 33000 ms. The query is using AK index, as it should, but why does it take > so much longer to scan the index for the range of just 30 records? I see that > PG is hitting the disk very intensively for this query. Can the query be > rewritten, etc to improve performance? Thanks > > select * from Test > where a >= '2002-09-01' > and a <= '2002-09-30' > and b = 5 > and c = 255 > > QUERY PLAN > Index Scan using ak_abc on test (cost=0.00..106.27 rows=30 width=53) (actual > time=33.536..33200.998 rows=30 loops=1) > Index Cond: (((a)::timestamp without time zone >= '2002-09-01 > 00:00:00'::timestamp without time zone) AND ((a)::timestamp without time zone > <= '2002-09-30 00:00:00'::timestamp without time zone) AND ((b)::integer > = 5) AND ((c) (..) > Total runtime: 33201.219 ms > > > > > > > > > __ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] pg 7.4.rc1, Range query performance
On Sat, 8 Nov 2003, ow wrote: > Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06. > All configuration settings are default. > > > Hi, > > Trying to find a way to improve range query performance. > > The table Test has about 30 million records. > > -- DLong, Dtimestamp, Dint, etc are domains of the respective types. > create table Test ( > id DLong not null, > a Dtimestamp null, > b Dintnot null, > c Dintnot null, > d Dstring null, > constraint PK_id primary key (id), > constraint AK_abc unique (a, b, c) > ); I'm not sure that AK_abc is the best index for check a range on a and single values on b and c. I'd think that something like an index on (b,c,a) would probably be better for this purpose (without doing any testing ;) ). ---(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
