Re: [SQL]
On Thu, 04 Oct 2001 01:55, postgresql wrote: > Please can someone help > > I tried to subccribe to pgsl-admin but I have been unable. > > I just upgraded to Mac osx 10.1. When I try to configure --with-tcl I get > an error that 'wish' can not be found. Where do I get this? (this is not so > important but I would like to use pgaccess) You need to install Tcl and Tk The source is available from many places, but their homes are:- http://downloads.activestate.com/ActiveTcl/src/8.3.3/tcl8.3.3.tar.gz http://downloads.activestate.com/ActiveTcl/src/8.3.3/tk8.3.3.tar.gz You need both of them. > 2. below is the error I am getting from 'make' (included the output from > make -v) any ideas (I know that I can go back to osx version 10.0.4 but > 10.1 is much faster writing to the screen. I'm sorry I can't help with the compile error. The mesage is very obtuse. btw, don't do work as the root user, it is so easy to have a total stuff-up. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] temporary views
> Hi > I have simple question: How to create view on a temporary table? > I need this, because pl/pgsql function returns data via temporary table. > > View created on a temporary table is useful only to the end of session. > Next time i create the same temp table i get > "Table xxx with oid xxx doesn't exist" Just name your temporary table the same name in every session. Why bother with a view. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] ORDER BY case insensitive?
How do I get the rows sorted in a case insensitive way? I have some queries that basically fit the form: SELECT * FROM MyTable ORDER BY Name; When I view the results, all of the Name's that start with an upper case letter precede all of the Name's that start with a lower case letter. I want them all in alphabetic order, regardless of case. I've looked in PostgreSQL Introduction and Concepts by Bruce Momjian and in the FAQ at http://postgresql.overtone.org/docs/faq-english.html Thanks for your help. -- Bob Swerdlow Chief Operating Officer Transpose, LLC [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] select 5/2???
dear all I run select 5/2 = 2 who to get "2.5" thanks -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] challenging query
Consider the following table: A B C D select? --- 1 FOO A1 100 n 1 BAR Z2 100 n 2 FOO A1 101 y 2 BAR Z2 101 y 3 FOO A1 102 y 4 BAR Z2 99 y 5 FOO A1 99 n 6 BAR Z2 98 n 7 FOO AB 103 y 7 BAR ZY 103 y This table has the idea of "groups", that is, a group is defined as all of the words from B that have the same number A. The values in column C also matter- we want to select both groups A=7 and A=1 since they contain different values C. Note that the groups defined by A=1 and A=3 are distinct- they do not contain the same number of words from B, so we want to select them both. Also note that D is datetime, and all the rows with the same number A will have the same D (this is actually ensured by a single row in another table.) I want to select all of the numbers A which define distinct groups and have the highest datetime D. Is this possible in a SQL query? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] select 5/2???
> > I run select 5/2 = 2 > who to get "2.5" > integer/integer = integer float/integer = float integer/float = float integer/integer::float = float test=# SELECT 5.0/2; ?column? -- 2.5 (1 row) test=# SELECT 5/2::float; ?column? -- 2.5 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [PHP] Need Help!!
Hi Gurudutt-- Concerning #1, I had a similar problem when porting data from mysql to psql. I finally ended up just using mysql's COPY command to get the data into delimited text form, then imported that into psql using its COPY command. This seems to me to be the easiest way to port over data if your table structures are exactly the same. If they aren't, then I'd export the mysql data to delimited text anyway, and write a quick script to import it into a structurally distinct psql table. I don't have any good advice for the other two difficulties you're having---hopefully others on the list can help. Good luck. Heather - Original Message - From: "Gurudutt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, May 21, 2001 10:09 AM Subject: [PHP] Need Help!! > Hello pgsql-sql, > > I am the new member for the postgres mailing list. Actually I have > been working with mysql, php and perl for a very long time now, and > offlate shifted to pgsql. I have many technical difficulties > > 1. I need to port mysql data to pgsql. I tried both mysql2pg.pl and > my2pg.sql. Both have some problem. I think it is got something to do > with the auto increment feature that i used in mysql. Can that issue > be addressed while porting. > > 2. Some of the joins that were successfully working in mysql are not > working, most importantly LEFT JOIN. > > eg. > > SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM > ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON > (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE > ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and > ACT_NetworkTab.NetCode=3 and > ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and > (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and > ACT_InvoiceTab.InvGenDate >= '2001-08-01') > ORDER BY ACT_InvoiceTab.InvGenDate DESC > > This query works fine in mysql, but suffers in pgsql. > > 3. I was using PEAR for data abstraction layer ( to make code > independent of the database), I find that PEAR which worked fine with > mysql doesn't work so well with pgsql > > > Any help on all these issues will be greatly appreciated. I am in the > midst of a porject porting exercise. > > > -- > Best regards, > Gurudutt mailto:[EMAIL PROTECTED] > > Life is not fair - get used to it. > Bill Gates > > > ---(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 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Alias Join Table
If I have the following 'Hierachy' table... Child | Parent -- 1 | 0 2 | 1 3 | 1 4 | 3 5 | 4 6 | 3 7 | 4 How do I return a list 5,4,3,1 ? SELECT a.Child FROM Hierachy AS a, Hierachy AS b WHERE a.Parent = b.Child AND a.Child = 5; ... is obviously a bit simplistic in my approach. -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Beginner's List
"Josh Berkus" <[EMAIL PROTECTED]> writes: > A new PostgreSQL user pointed out to me that there is nobody currently > on the pgsql-beginner list with any depth of experience. You mean pgsql-novice, no? There are a number of hackers answering questions pretty regularly on that list... > To help remedy this, I would like to take 1 beginner question per day. > But I can't figure out how to do this without subscribing to > pgsql-beginner and suffering a deluge of 100's of e-mails. If you read pghackers, pggeneral, etc, then the extra traffic for pgsql-novice is pretty tiny. I see 12 postings so far this month. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ORDER BY case insensitive?
SELECT * FROM MyTable ORDER BY lower(Name); Should do the trick. Jason Earl --- Bob Swerdlow <[EMAIL PROTECTED]> wrote: > How do I get the rows sorted in a case insensitive > way? > > I have some queries that basically fit the form: > SELECT * FROM MyTable ORDER BY Name; > When I view the results, all of the Name's that > start with an upper case > letter precede all of the Name's that start with a > lower case letter. I > want them all in alphabetic order, regardless of > case. > > I've looked in PostgreSQL Introduction and Concepts > by Bruce Momjian and in > the FAQ at > http://postgresql.overtone.org/docs/faq-english.html > > Thanks for your help. > > -- > Bob Swerdlow > Chief Operating Officer > Transpose, LLC > [EMAIL PROTECTED] > > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __ Do You Yahoo!? NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 ---(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] to_date/to timestamp going to BC
Thanks Karel,
Good call, you guessed it. I was just using my Oracle knowledge of
the to_date and applying it to the results I was expecting in pgsql. Guess I
should not make those assumptions
-Original Message-
From: Karel Zak [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 04, 2001 2:42 AM
To: Tom Lane
Cc: Servetar, Jason; [EMAIL PROTECTED]
Subject: Re: [SQL] to_date/to timestamp going to BC
On Wed, Oct 03, 2001 at 05:14:02PM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > Can someone tell me if this is a bug with the date functions or am I
using
> > them incorrectly?
>
> I get the right thing when I use the right format:
>
> regression=# select dt, to_timestamp(dt, 'FMMonth dd, ') from
test_date;
>dt | to_timestamp
> +
> March 11, 1997 | 1997-03-11 00:00:00-05
> (1 row)
>
> However, I'd agree that this shows a lack of robustness in to_timestamp;
> it's not objecting to data that doesn't match the format.
The manual is transparent about this. I can add feauture that will
check everythig, but users who knows read manual and use already
debugged queries will spend CPU on non-wanted code.
Hmm.. I look at Oracle, and it allows parse queries like:
SVRMGR> select to_date('March 11, 1997', 'Month dd, ') from dual;
TO_DATE('
-
11-MAR-97
1 row selected.
.. well, I add it to my TODO for 7.3 (I plan rewrite several things
in to_* functions).
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how can i return multiple values from a function
Try using temporary tables. Functions can't return tables and, it would seem, SETOFs.
srinivas wrote:
> i have tried retrieving multiple values using setof function but i
> couldnt solve it.when i am trying using setof iam getting this as
> output.
>
>
> 1 CREATE FUNCTION hobbies (varchar) RETURNS SETOF bank
> 2 AS 'SELECT * FROM bank
> 3 '
> 4 LANGUAGE 'sql';
>
> ~
> output:
> select hobbies('srinivas') as col;
> col
> ---
> 137462096
> 137462096
> (2 rows)
>
> please let me know what is the alternative and if possible with a sample
> piece of code.
>
> cheers
> chowdary.
>
> ---(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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function return rows?
Pat M wrote: > Can I use a function to execute a query and return a row or set of rows? No. Consider using temporary tables if you must do this. > If > so, can you point me to some examples or perhaps give me an example of a > function that would do roughly the same thing as: > > select * from mytable where mytable.name ~* 'aname'; > > I can't seem to find any examples that return rows, just single ints and > bools and stuff. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ORDER BY case insensitive?
My guess is that compared to the task of sorting
millions of names the fact that you have to lowercase
them first is not going to be a particular burden. No
matter what you do you are going to get a table scan
(unless you qualify your select with a where clause).
You can, however, create an index like:
create index MyTable_lower_idx on MyTable
(lower(name));
It won't help with your particular query, but it
certainly would help for queries like:
SELECT * FROM MyTable WHERE lower(name) = 'jason';
It is also possible to create a trigger that would
automatically lowercase information when it is
inserted or updated. Or even less high tech you can
make sure that all of your INSERTS and UPDATES use the
lower command like this:
INSERT into MyTable (name) VALUES (lower('VALUE'));
Jason
--- Bob Swerdlow <[EMAIL PROTECTED]> wrote:
> Thanks for the suggestion, Jason.
>
> A co-worker of mine, however, had this response:
> Yes, but my guess is that that will first
> convert all million (or
> whatever) records to upper case, and then physically
> sort them. It won't be
> able to make use of the index.
>
> To make this efficient, do we need to uppercase all
> of the data before
> adding to the table? (yuk)
>
> - Bob
>
>
> - Original Message -
> From: "Jason Earl" <[EMAIL PROTECTED]>
> To: "Bob Swerdlow" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Thursday, October 04, 2001 3:47 PM
> Subject: Re: [SQL] ORDER BY case insensitive?
>
>
> > SELECT * FROM MyTable ORDER BY lower(Name);
> >
> > Should do the trick.
> >
> > Jason Earl
> >
> > --- Bob Swerdlow <[EMAIL PROTECTED]> wrote:
> > > How do I get the rows sorted in a case
> insensitive
> > > way?
> > >
> > > I have some queries that basically fit the form:
> > > SELECT * FROM MyTable ORDER BY Name;
> > > When I view the results, all of the Name's that
> > > start with an upper case
> > > letter precede all of the Name's that start with
> a
> > > lower case letter. I
> > > want them all in alphabetic order, regardless of
> > > case.
> > >
> > > I've looked in PostgreSQL Introduction and
> Concepts
> > > by Bruce Momjian and in
> > > the FAQ at
> > >
> http://postgresql.overtone.org/docs/faq-english.html
> > >
> > > Thanks for your help.
> > >
> > > --
> > > Bob Swerdlow
> > > Chief Operating Officer
> > > Transpose, LLC
> > > [EMAIL PROTECTED]
> > >
> > >
> > >
> > > ---(end of
> > > broadcast)---
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> >
> > __
> > Do You Yahoo!?
> > NEW from Yahoo! GeoCities - quick and easy web
> site hosting, just
> $8.95/month.
> > http://geocities.yahoo.com/ps/info1
> >
>
__
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] to_date/to timestamp going to BC
On Wed, Oct 03, 2001 at 05:14:02PM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > Can someone tell me if this is a bug with the date functions or am I using
> > them incorrectly?
>
> I get the right thing when I use the right format:
>
> regression=# select dt, to_timestamp(dt, 'FMMonth dd, ') from test_date;
>dt | to_timestamp
> +
> March 11, 1997 | 1997-03-11 00:00:00-05
> (1 row)
>
> However, I'd agree that this shows a lack of robustness in to_timestamp;
> it's not objecting to data that doesn't match the format.
The manual is transparent about this. I can add feauture that will
check everythig, but users who knows read manual and use already
debugged queries will spend CPU on non-wanted code.
Hmm.. I look at Oracle, and it allows parse queries like:
SVRMGR> select to_date('March 11, 1997', 'Month dd, ') from dual;
TO_DATE('
-
11-MAR-97
1 row selected.
.. well, I add it to my TODO for 7.3 (I plan rewrite several things
in to_* functions).
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Beginner's List
On Thu, 4 Oct 2001, Josh Berkus wrote: > A new PostgreSQL user pointed out to me that there is nobody currently > on the pgsql-beginner list with any depth of experience. As a result, > most posts there go answered with an "Uh ... I dunno either." or even > misinformation. > > To help remedy this, I would like to take 1 beginner question per day. > But I can't figure out how to do this without subscribing to > pgsql-beginner and suffering a deluge of 100's of e-mails. Can anyone > suggest something? If you don't mind the delay you could try the mailing list archives on the website I guess (they're a little annoying to use for responding, but...) ---(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] Need Help!!
Hello pgsql-sql, I am the new member for the postgres mailing list. Actually I have been working with mysql, php and perl for a very long time now, and offlate shifted to pgsql. I have many technical difficulties 1. I need to port mysql data to pgsql. I tried both mysql2pg.pl and my2pg.sql. Both have some problem. I think it is got something to do with the auto increment feature that i used in mysql. Can that issue be addressed while porting. 2. Some of the joins that were successfully working in mysql are not working, most importantly LEFT JOIN. eg. SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and ACT_NetworkTab.NetCode=3 and ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and ACT_InvoiceTab.InvGenDate >= '2001-08-01') ORDER BY ACT_InvoiceTab.InvGenDate DESC This query works fine in mysql, but suffers in pgsql. 3. I was using PEAR for data abstraction layer ( to make code independent of the database), I find that PEAR which worked fine with mysql doesn't work so well with pgsql Any help on all these issues will be greatly appreciated. I am in the midst of a porject porting exercise. -- Best regards, Gurudutt mailto:[EMAIL PROTECTED] Life is not fair - get used to it. Bill Gates ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL Syntax / Logic question
I've been working on this SQL problem for about 12 days now and have asked for help from friends/colleagues, but haven't found a solution. I send it to this list as a last resort. Let's say I have a table called "friends" and in this table, I have the following data: FriendA FriendB --- --- Mike Christopher Jim Mike Joe Sara Jim Sara Let's also say I have another table called "schools" and in this table, I have the following data: Person School -- -- Christopher Akron MikeAkron Jim OSU Joe Kent SaraOSU I want to be able to return all (FriendA, FriendB) pairs in which both friends went to the same school. The above example would return only these pairs: Mike, Christopher Jim, Sara My initial thinking was that I need a query like this: select frienda,friendb from friends where "frienda's school" = "friendb's school"; Translating the pseudo-code into a real query, we have: select frienda, friendb from friends where (select schools.school from friends,schools where friends.frienda = schools.person) = (select schools.school from friends,schools where friends.friendb = schools.person); Of course, this doesn't work in real life. I get the usual error: ERROR: More than one tuple returned by a subselect used as an expression. Is there a way to do this or am I asking for the impossible? Many thanks for any help you can provide. Mike Harlan [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] to_date/to timestamp going to BC
Karel, > .. well, I add it to my TODO for 7.3 (I plan rewrite several things > in to_* functions). How about a to_char function for INTERVAL? Please, oh please? -Josh __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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]maximum parameters limit to function & manipulating array in plpgsql
Advid, > Well I read one of ur solutions to sending more tham 16 parameters to > a function in plpgsql. I have changed the value of FUNC_MAX_ARGS > (a/k/a INDEX_MAX_KEYS) in \usr\local\plpgsql\include\config.h after > stoping the postmaster service. > On restarting the service after having changed config.h I am atill > experiencing the same problem. What should I do now? Did you re-init PostgreSQL, and then re-build your database from a pgdump file? I believe that these steps are necessary as well. > If I declare n as integer[3] it gives a compilation error. So I tried > it as above. Can I do any array manipulations at all in plpgsql like > assigning,copying etc values in to other arrays in plpgsql. If so how > ?? No, you can't. Array support in PL/pgSQL is quirky and not all the way there. Jan Wieck has instead been focusing on building CURSOR support into PL/pgSQL so arrays are likely to be unevenly supported for some time unless somebody else takes on the task. Mind you, there have been some proponents of dropping the array data type entirely as not relational (it isn't) so I don't know where this feature is going. Basically, you can declare an array as a parameter of a function, but that's it. You cannnot have array variables or retuurn types. -Josh __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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] to_date/to timestamp going to BC
On Thu, Oct 04, 2001 at 07:44:14AM -0700, Josh Berkus wrote:
> Karel,
>
> > .. well, I add it to my TODO for 7.3 (I plan rewrite several things
> > in to_* functions).
>
> How about a to_char function for INTERVAL? Please, oh please?
oh, needn't please.. already in right now breeding 7.2 :-)
test=# SELECT to_char('5months 3sec 4h 1min'::interval, 'HH:MI:SS Mon');
to_char
--
04:01:03 May
(1 row)
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(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] SQL Syntax / Logic question
Mike, > select frienda, friendb from friends where (select > schools.school from friends,schools where friends.frienda = > schools.person) = (select schools.school from friends,schools where > friends.friendb = schools.person); Too complicated. You need to learn how to use JOINS and table aliases (or find yourself some friends who know SQL!): SELECT friends.frienda, friends.friendb FROM friends JOIN schools schoola ON friends.frienda = schoola.person JOIN schools schoolb ON friends.friendb = schoolb.person WHERE schoola.school = schoolb.school and, if it's possible that any particular person went to more than one school, add: GROUP BY frienda, friendb Simple, neh? -Josh __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
[SQL] temporary views
Hi I have simple question: How to create view on a temporary table? I need this, because pl/pgsql function returns data via temporary table. View created on a temporary table is useful only to the end of session. Next time i create the same temp table i get "Table xxx with oid xxx doesn't exist" Tomek ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Beginner's List
Folks, A new PostgreSQL user pointed out to me that there is nobody currently on the pgsql-beginner list with any depth of experience. As a result, most posts there go answered with an "Uh ... I dunno either." or even misinformation. To help remedy this, I would like to take 1 beginner question per day. But I can't figure out how to do this without subscribing to pgsql-beginner and suffering a deluge of 100's of e-mails. Can anyone suggest something? -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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ORDER BY case insensitive?
On Thu, 4 Oct 2001, Jason Earl wrote: > My guess is that compared to the task of sorting > millions of names the fact that you have to lowercase > them first is not going to be a particular burden. No > matter what you do you are going to get a table scan > (unless you qualify your select with a where clause). > > You can, however, create an index like: > > create index MyTable_lower_idx on MyTable > (lower(name)); > > It won't help with your particular query, but it > certainly would help for queries like: Current sources do (and I think older sources may) consider such an index to replace the sort step. On a test table with 10 strings, explain shows an index scan for the lower() ordering and a seq scan/sort for ordering. At this point there's actually a performance gain for the lower() index scan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Need Help!!
On Mon, May 21, 2001 at 07:39:06PM +0530, Gurudutt wrote: > Hello pgsql-sql, > > I am the new member for the postgres mailing list. Actually I have > been working with mysql, php and perl for a very long time now, and > offlate shifted to pgsql. I have many technical difficulties > > 2. Some of the joins that were successfully working in mysql are not > working, most importantly LEFT JOIN. > > eg. > > SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM > ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON > (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE > ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and > ACT_NetworkTab.NetCode=3 and > ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and > (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and > ACT_InvoiceTab.InvGenDate >= '2001-08-01') > ORDER BY ACT_InvoiceTab.InvGenDate DESC > > This query works fine in mysql, but suffers in pgsql. suffers? What's suffers? It's slower? It doesn't work at all? What? Looking at it, I'd guess that you get something about lack of GROUPing when using an aggregate, right? So, you'll need to use correct SQL to express the summation your trying to achieve. I don't have your schema, nor the time to reverse engineer it from your example query, but if what your expecting back from that is 31 rows in order, each one representing the total invoices due on that day, you need to add: GROUP BY BY ACT_InvoiceTab.InvGenDate just before the ORDER BY line Or, if you want the summation of all of them, and only expect one number back, why are you ORDERing it? > > 3. I was using PEAR for data abstraction layer ( to make code > independent of the database), I find that PEAR which worked fine with > mysql doesn't work so well with pgsql Again, vague. What "doesn't work so well" ? What is PEAR? Hmm, seems to be some PHP specific thing. I guess I'll let PHP PostgreSQL people answer this one. > > > Any help on all these issues will be greatly appreciated. I am in the > midst of a porject porting exercise. > Hope I helped. Ross ---(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] Beginner's List
Josh Berkus wrote: > > To help remedy this, ... > Can anyone suggest something? > Could we set-up a forum with a product like Request Tracker where a group of experienced users could take questions from a web-based queue? -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[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
Re: [SQL] ORDER BY case insensitive?
Jason Earl wrote: > > You can, however, create an index like: > > create index MyTable_lower_idx on MyTable > (lower(name)); > > It won't help with your particular query, but it > certainly would help for queries like: > > SELECT * FROM MyTable WHERE lower(name) = 'jason'; > How would PostgreSQL know to use the index MyTable_lower_idx when I do a ... SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%'; -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ORDER BY case insensitive?
Keith Gray <[EMAIL PROTECTED]> writes: > How would PostgreSQL know to use the index > MyTable_lower_idx when I do a ... > SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%'; The same way it knows to use any other index: it matches up the things mentioned in the WHERE clause with the available indexes, and then sees whether the clauses they are mentioned in are comparisons that the index can help with. In this case the "thing" mentioned is "function(column)" rather than just "column", but otherwise it's just like a simple index. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Indexing behavior
On Tue, Oct 02, 2001 at 09:47:09AM -0700, Josh Berkus wrote: > Folks, > > Q: Are brand-new rows included in PostgreSQL indexed immediately? > A: Yes, and that's why you don't add indices willy-nilly to all possible column combos on any given table: INSERTs get real slow. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Alias Join Table
Keith, There are a number of posts and papers on tree structures , both in the SQL list archives, and on Roberto Mello's resources at techdocs.postgresql.org. -Josh __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] strange query execution times
On Tue, 2001-10-02 at 17:49, Tom Lane wrote: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > r_kunden_anbieter describes the relationship between customers and > > suppliers. there are five status, 0 to 4 in attribute beziehung. both > > queries return the same results. they select all customers which have a > > certain relationship (beziehung = 3) to a given supplier. > > personen has 484 rows, r_kunden_anbieter 327. > > How many rows actually satisfy "rka.a_id = 620 and rka.beziehung = 3"? > > The issue appears to be that the planner estimates one matching row > in the one case and two matching rows in the second. Given the estimate > of one row, it decides to go for the low-overhead nested loop plan. > I am guessing that there are really considerably more than two matching > rows, and so the nested loop plan loses badly compared to the mergejoin, > which takes longer to set up but is better able to cope with many rows. > > FWIW, 7.2 has better statistics and should be better able to pick the > right plan in this context ... 13 rows do. Is there a way to force 7.0.3 (or 7.1.3) to use the mergejoin with the straightforward condition? Markus Bertheau Cenes Data GmbH ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
