[SQL] Client Applications come with PostGreSQL V7.1
Is there any Windows version of all Client Applications come with PostGreSQL V7.1? Jack ---(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] Order by email address by domain ?
here's an idea: if you would have all emails stored in the reverse order: moc.niamod@resu then all you would need is simple 'ORDER BY email'. Also, in this way, emails like [EMAIL PROTECTED] would be perfectly ordered. Is that a crazy thought? If not yet then: why not to add an additional field to the table with the reverse domain only? (after an @) ordering by it. INDEX would also be simple. now is crazy? Then, is there any such function in PostgreSQL? With C it would be so easy and fast. Cheers! Maxim Maletsky -Original Message- From: Hervé Piedvache [mailto:[EMAIL PROTECTED]] Sent: Friday, May 11, 2001 3:38 AM To: [EMAIL PROTECTED] Subject: [SQL] Order by email address by domain ? Hi, I just want to order by a listing of email address by domain like : [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Is it possible and how ? Thanks ! -- Hervé Piedvache Elma Ingenierie Informatique 6, rue du Faubourg Saint-Honoré F-75008 - Paris - France http://www.elma.fr Tel: +33-1-44949901 Fax: +33-1-44949902 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Client Applications come with PostGreSQL V7.1
Is there any Windows version of all Client Applications come with PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Informix-PostgreSQL database convertion
I think this isn't so difficult. Use unload to ... on Informix side ( from dbaccess ) and copy ... from ... om Postgres side. Sylte [EMAIL PROTECTED] ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: 9d8r7v$1to$[EMAIL PROTECTED]">news:9d8r7v$1to$[EMAIL PROTECTED]... Howto? Are there tools or is it just a lot of hard work with a lot of PHP? Some examples or site-links will be appreciated :O) Thanks - Sylte ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Oracle ROWNUM = PSQL ???
Oracle has a ROWNUM pseudo column that works like this ... TESTselect callid, rownum from cs_calls where rownum 5; CALLID ROWNUM -- -- 7806 1 7807 2 7809 3 6443 4 4 rows selected. ... which can be quite handy. Is there something like this in PSQL? By the way, having used Oracle since 1987 it's a pleasure to use PSQL. Someone actually thinks about the implemented features. For example, Oracle's implementation of ROWNUM gives them in the order the rows were BEFORE the ORDER BY, which is not of much use as adding an ORDER BY jumbles them up. Duh! Thanks in advance for any responses. ---(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] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
NOTE: I did a moderate search through the PgSQL mail list archives, but couldn't find an example of a question where both ORDER BY and aggregation were used. In all examples it was possible to use either one or the other with a possible subselect. [Keep reading...I discovered how to do this with subselects, but I'm wondering if (1) what I'm doing with ORDER BY and aggregation will ever be allowed and (2) whether the subselect solution is much more inefficient than being able to order a pre-aggregate set.] I need to query a table and return aggregated results. The aggregation occurs many ways (I'm joining 7 tables and returning 19 columns currently): o Columns in a GROUP BY clause o SUM() functions o COUNT() functions However, I wrote a new FUNCTION and a new AGGREGATE to return the *last* value of a field during the aggregation process (see below). This means that I would like to use an ORDER BY clause with the SELECT ... GROUP BY statement before the results are aggregated. DROP FUNCTION lastint4 (int4, int4); CREATE FUNCTION lastint4 (int4, int4) RETURNS int4 AS 'BEGIN RETURN $2; END;' LANGUAGE 'PLPGSQL'; DROP AGGREGATE lastitem int4; CREATE AGGREGATE lastitem ( SFUNC1 = lastint4, BASETYPE = int4, STYPE1 = int4); A simple (made-up) example: SELECT r.personid AS personid ,SUM(r.laps) AS laps ,COUNT(DISTINCT r.id) AS nightsraced ,lastitem(r.carid)AS carid FROM race r WHERE r.personid = 14 GROUP BY r.personid ORDER BY r.date DESC; Does PostgreSQL (or ANSI SQL) allow this? In the simple tests I ran, I would get errors similar to the following: ERROR: Attribute r.carid must be GROUPed or used in an aggregate function This seems a bit...obvious because r.carid is already being used in an aggregate function. I'm guessing that I'm running into the internals of how PgSQL processes the query. Hmm...well I just figured out how I could do this as a subselect, but it seems hugely inefficient and would require PostgreSQL 7.1 or later (ORDER BY, LIMIT used in a subselect): SELECT r.personid AS personid ,SUM(r.laps) AS laps ,COUNT(DISTINCT r.id) AS nightsraced ,(SELECT r.carid FROM race r WHERE r.personid = 14 ORDER BY r.date DESC LIMIT 1) AS carid FROM race r WHERE r.personid = 14 GROUP BY r.personid ORDER BY r.date; Is a subselect like this really that inefficient (assuming appropriate indexes on r.date and r.personid)? I would think doing this during aggregation would be much more efficient. I'm using PostgreSQL 7.0.3 on Debian GNU/Linux 2.2r3 (postgresql-7.0.3-4) on a Linux 2.2.1x kernel. Thanks for any insight you can provide! Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Informix-PostgreSQL database convertion
It is convertion of the data, not the application... ---(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] Informix-PostgreSQL database convertion
Howto? Are there tools or is it just a lot of hard work with a lot of PHP? Some examples or site-links will be appreciated :O) Thanks - Sylte ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem in Porting from Oracle to Postgres SQl
Hi! I am facing two problems in porting from oracle to Postgres SQL. 1> There is a code in Oracle like Type Tstate is table of number(9) index by binary_integer; . To define a runtime table, basically it works like a array, How can it be possible in Postgres SQL, I have tried create temp table But it not works.. Is there any way to use arrays. 2> There is one function in Oracle Executesql '...' to execute and what i got in Postgres is Execute immediate '.' But it is giving error at Execute. I will be very thankful if any one help me. Amit ( India )
[SQL] execute client application from PL/pgSql
Is that possible to execute a client application from server site by PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS, or is there any Windows version of all Client Applications come from PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Orac le ROWNUM = PSQL ???
* Robert Beer [EMAIL PROTECTED] menulis: Oracle has a ROWNUM pseudo column that works like this ... TESTselect callid, rownum from cs_calls where rownum 5; CALLID ROWNUM -- -- 7806 1 7807 2 7809 3 6443 4 4 rows selected. ... which can be quite handy. Is there something like this in PSQL? You can use the LIMIT clause: SELECT callid FROM cs_calls LIMIT 4; See http://postgresql.readysetnet.com/users-lounge/docs/7.1/postgres/sql-select.html for more info. By the way, having used Oracle since 1987 it's a pleasure to use PSQL. Someone actually thinks about the implemented features. For example, Oracle's implementation of ROWNUM gives them in the order the rows were BEFORE the ORDER BY, which is not of much use as adding an ORDER BY jumbles them up. Duh! LIMIT in PostgreSQL applies *after* ORDER BY, so you won't have this problem :) -- Cliff Crawford http://www.sowrong.org/ birthday party cheesecake jellybean BOOM ---(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: How to store gif or jpeg? Thanks!
src/interfaces/jdbc/example/ImageViewer.java Somewhere on the internet Please be more specific ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
Okay, I found a workaround for PostgreSQL 7.0.3 for the specific query I was working on below, but I'd still like to know if an ORDER BY clause will ever be allowed with an aggregated SELECT statement, and whether it would generally be more efficient to use an ORDER BY in the situation described below instead of a subselect query. Also, I realized after I sent the original message below that I could work around the no ORDER BY, LIMIT in subselect limitation in PostgreSQL 7.0.x using a subselect within a subselect, but then I'd just be abusing the database engine, not finding a real-world solution. :^) Thanks! Dave On Mon, May 07, 2001 at 12:27:59PM -0500, David D. Kilzer wrote: NOTE: I did a moderate search through the PgSQL mail list archives, but couldn't find an example of a question where both ORDER BY and aggregation were used. In all examples it was possible to use either one or the other with a possible subselect. [Keep reading...I discovered how to do this with subselects, but I'm wondering if (1) what I'm doing with ORDER BY and aggregation will ever be allowed and (2) whether the subselect solution is much more inefficient than being able to order a pre-aggregate set.] I need to query a table and return aggregated results. The aggregation occurs many ways (I'm joining 7 tables and returning 19 columns currently): o Columns in a GROUP BY clause o SUM() functions o COUNT() functions However, I wrote a new FUNCTION and a new AGGREGATE to return the *last* value of a field during the aggregation process (see below). This means that I would like to use an ORDER BY clause with the SELECT ... GROUP BY statement before the results are aggregated. DROP FUNCTION lastint4 (int4, int4); CREATE FUNCTION lastint4 (int4, int4) RETURNS int4 AS 'BEGIN RETURN $2; END;' LANGUAGE 'PLPGSQL'; DROP AGGREGATE lastitem int4; CREATE AGGREGATE lastitem ( SFUNC1 = lastint4, BASETYPE = int4, STYPE1 = int4); A simple (made-up) example: SELECT r.personid AS personid ,SUM(r.laps) AS laps ,COUNT(DISTINCT r.id) AS nightsraced ,lastitem(r.carid)AS carid FROM race r WHERE r.personid = 14 GROUP BY r.personid ORDER BY r.date DESC; Does PostgreSQL (or ANSI SQL) allow this? In the simple tests I ran, I would get errors similar to the following: ERROR: Attribute r.carid must be GROUPed or used in an aggregate function This seems a bit...obvious because r.carid is already being used in an aggregate function. I'm guessing that I'm running into the internals of how PgSQL processes the query. Hmm...well I just figured out how I could do this as a subselect, but it seems hugely inefficient and would require PostgreSQL 7.1 or later (ORDER BY, LIMIT used in a subselect): SELECT r.personid AS personid ,SUM(r.laps) AS laps ,COUNT(DISTINCT r.id) AS nightsraced ,(SELECT r.carid FROM race r WHERE r.personid = 14 ORDER BY r.date DESC LIMIT 1) AS carid FROM race r WHERE r.personid = 14 GROUP BY r.personid ORDER BY r.date; Is a subselect like this really that inefficient (assuming appropriate indexes on r.date and r.personid)? I would think doing this during aggregation would be much more efficient. I'm using PostgreSQL 7.0.3 on Debian GNU/Linux 2.2r3 (postgresql-7.0.3-4) on a Linux 2.2.1x kernel. Thanks for any insight you can provide! Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Is this possible?
I have a table that has a serial for primary key. Is it possible to get the new available primary key right after I insert a row of new entry? Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Orac le ROWNUM = PSQL ???
Robert Beer [EMAIL PROTECTED] wrote: Oracle has a ROWNUM pseudo column that works like this ... TESTselect callid, rownum from cs_calls where rownum 5; Is there something like this in PSQL? SELECT callid FROM cs_calls LIMIT 4; HTH, Ray -- Give a man a fire, he's warm for a day. Set a man on fire, he's warm for the rest of his life. ---(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: Order by email address by domain ?
???
I don't think anyone suggested adding a new builtin function.
Yes, your suggestion is good for an occasional use.
Where this kind of functionality is likely to be needed on a continuing
basis, my experience is that wrapping the code up in a custom function is
easier and cleaner than writing it out every time. Saves a lot of typing,
not to mention the risk of typos (which could give spurious results without
being obvious about it). Also, a function allows for indexing on that
value, which can be a great aid to performance.
I have found that sometimes it is better to just add a column or two to the
table to contain the needed key, because with large amounts of data that can
be much quicker. Doing this too much though can lead to a cluttered
database, and a loss of clarity about just what all of those extra fields
are for... It's a judgement call.
Just my $0.01 (That's $0.02 Australian :-))
-Original Message-
From: Frank Bax [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, May 10, 2001 8:53 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Order by email address by domain ?
Why is everyone else suggesting new functions? This works (in 6.5.3):
ORDER BY lower(substring(email from position('@' in email)+1 )),
lower(email)
remove the lower() functions if you don't need them (I had mixed case
addresses).
I am guessing/assuming that it's cheaper to just use entire email address
in second key rather than extract before the '@' character.
Frank
At 08:37 PM 5/10/01 +0200, you wrote:
Hi,
I just want to order by a listing of email address by domain like :
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Is it possible and how ?
Thanks !
--
Hervé Piedvache
Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: [HACKERS] Problems in porting from Oracle to Postgres
This is more appropriate for the pgsql-sql list, so im forwarding it that way. The hackers list is for other purposes. On Fri, May 11, 2001 at 12:24:25PM +0530, Amit wrote: 1 There is a code in Oracle like Type Tstate is table of number(9) index by binary_integer; To define a runtime table, basically it works like a array, How can it be possible in Postgres SQL, I have tried create temp table But it not works.. Is there any way to use arrays. It'd be much easier to help you if you posted the function/procedure you're trying to port. Just one line is harder. 2 There is one function in Oracle Executesql '...' to execute and what i got in Postgres is Execute immediate '.' But it is giving error at Execute. Again, you're giving way too little detail. What error? What are you trying? Without this, it's very hard to help. -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 Cannot open CATFOOD.CAN - Eat logitech mouse instead (Y/n)? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] create type and domains
I'm trying to simulate domains in PostgreSQL using the create type function, but I cannot find how the input and output functions for standard types are called. Is there anyone who can help me ? Thanks! --Valerio Santinelli
Re: [SQL] execute client application from PL/pgSql
you could hack the pg_dump bit out of phpPgAdmin i think the license permits it. just my 2 cents. jeff On Wed, 9 May 2001, Jack wrote: Date: Wed, 9 May 2001 09:45:46 +1000 From: Jack [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [SQL] execute client application from PL/pgSql Is that possible to execute a client application from server site by PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS, or is there any Windows version of all Client Applications come from PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] execute client application from PL/pgSql
Jack wrote: Is that possible to execute a client application from server site by PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS, or is there any Windows version of all Client Applications come from PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster afaik if you have tcl/tk on windows should you not be able to run the pgaccess? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
David D. Kilzer [EMAIL PROTECTED] writes: [ wants to write an aggregate function that returns its last input ] The SQL model of query processing has a very definite view of the stages of processing: first group by, then aggregate, and last order by. Tuple ordering is irrelevant according to the basic semantics of the language. Probably the SQL authors would have left out ORDER BY entirely if they could have got away with it, but instead they made it a vestigial appendage that is only allowed at the very last instant before query outputs are forwarded to a client application. Thus, it is very bad form to write an aggregate that depends on the order it sees its inputs in. This won't be changed, because it's part of the nature of the language. In PG 7.1 it's possible to hack around this by ordering the result of a subselect-in-FROM: SELECT orderedagg(ss.x) FROM (select x from tab order by y) ss; which is a gross violation of the letter and spirit of the spec, and should not be expected to be portable to other DBMSes; but it gets the job done if you are intent on writing an ordering-dependent aggregate. However, I don't see any good way to combine this with grouping, since if you apply GROUP BY to the output of the subselect you'll lose the ordering again. SELECT r.personid AS personid ,SUM(r.laps) AS laps ,COUNT(DISTINCT r.id) AS nightsraced ,(SELECT r.carid FROM race r WHERE r.personid = 14 ORDER BY r.date DESC LIMIT 1) AS carid FROM race r WHERE r.personid = 14 GROUP BY r.personid ORDER BY r.date; This is likely to be reasonably efficient, actually, since the subselect will be evaluated only once per output group --- in fact, as you've written it it'll only be evaluated once, period, since it has no dependencies on the outer query. More usually you'd probably do ,(SELECT r2.carid FROM race r2 WHERE r2.personid = r.personid ORDER BY r2.date DESC LIMIT 1) AS carid so that the result tracks the outer query, and in this form it'd be redone once per output row. 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] Postgres function library
On Fri, May 11, 2001 at 08:48:01AM +0200, Lajtos Mate wrote: Hi all, I read the post from Josh Berkus where he (at the end of the message) mentions a 'function library'. Is that library available for any developer or it's a proprietary one? If it's public, can I post functions there? It's a cookbook of functions that we are trying to gather. Each function submitted has its own license specified by its author. Most of them are licensed under the GPL or BSD. Many are public domain. And yes, PLEASE post functions there. The more we have, the better. I am going to improve the site in a few days. http://www.brasileiro.net/postgres -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 As easy as pi=3.14159265358979323846264338327950288419716939937511 ---(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] Is this possible?
On Tue, May 08, 2001 at 09:16:56PM -0400, Wei Weng wrote: I have a table that has a serial for primary key. Is it possible to get the new available primary key right after I insert a row of new entry? Yeah. Se the documentation on triggers. -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 Data! I thought you were dead! No, Sir. I rebooted. ---(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] number of days in a month
does it exist a date function to determine the number of days in a Select, knowing a specifique date ? January, 2001: select '2001-2-1'::datetime - '2001-1-1'::datetime; -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Web: www.suse.dk 2000 FrederiksbergLørdag 11.00-17.00 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: How to store gif or jpeg? Thanks!
On Tue, May 08, 2001 at 03:16:53PM +0200, Sylte allegedly wrote: src/interfaces/jdbc/example/ImageViewer.java Somewhere on the internet Please be more specific It's part of the PostgreSQL source. You can download the PostgreSQL source from www.postgresql.org... Regards, Mathijs -- And the beast shall be made legion. Its numbers shall be increased a thousand thousand fold. The din of a million keyboards like unto a great storm shall cover the earth, and the followers of Mammon shall tremble. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] execute client application from PL/pgSql
Thank You Jeff, What is phpPgAdmin and where can get it? Jack - Original Message - From: Jeff MacDonald [EMAIL PROTECTED] To: Jack [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, May 12, 2001 2:28 AM Subject: Re: [SQL] execute client application from PL/pgSql you could hack the pg_dump bit out of phpPgAdmin i think the license permits it. just my 2 cents. jeff On Wed, 9 May 2001, Jack wrote: ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
