Re: [SQL] How to make a IN without a table... ?
> > I avoid as hell to use temporary tables. This is part of a complex > > database, with more than 250 different tables. So i don't even want to > > think about adding temporary tables, brrr ! > > Just FYI, temporary tables in pgsql are invisible to other connections > even if they have the same name, and are auto-dropped when the connection > is dropped. Well, i didn't know this. I will check how it works. Thanks a lot, Scott. -- [EMAIL PROTECTED] 01.46.47.21.33 ---(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] Let join syntax
Hi, I've a problem with the following query. I want to left join table sec (x5) with x0, and x4. I try the following query : select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv ,x0.coh_d_inv , x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local ,x0.coh_basis_local ,x0.coh_cost_local ,x0.coh_profit_local ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust ,x0.coh_com_held ,x0.coh_com_cancel ,x0.coh_com_topay ,x0.coh_d_paid ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb ,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr ,x1.cpy_cr_tg ,x1.cpy_cr_tb ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name from coh x0 , cpy x1 ,bra x2 , cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6 where ((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id = x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id = x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id = x0.dpr_id ) Unfortunatelly, postgres returns me the following error : Error: ERROR: Relation "x0" does not exist (State:S1000, Native Code: 7) I tried to modify the FROM sentence by: FROM (coh x0 , cpy x1 ,bra x2 , cur x3 ,tad x4 ) LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, but I get a syntax error , may be due to parentheses. Does anyone knows how to write this query correctly ? Regards, Nicolas. --- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: [EMAIL PROTECTED] Web : www.regie-france.com --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Let join syntax
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" <[EMAIL PROTECTED]> wrote: >from >coh x0 , cpy x1 ,bra x2 , >cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND >x5.usr_id=x0.usr_id AND [...] > >Unfortunatelly, postgres returns me the following error : > Error: ERROR: Relation "x0" does not exist Yes, because the LEFT OUTER JOIN only sees x4 and x5. I have not analysed all your join conditions, but FROM coh x0 INNER JOIN cpy x1 ON [...] INNER JOIN bra x2 ON [...] INNER JOIN cur x3 ON [...] INNER JOIN tad x4 ON [...] LEFT JOIN sec x5 ON [...] might work. If there is nothing to join x1, x2, x3, x4 on, you could try to put x5 to the front and use RIGHT JOINs ... Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] casting interval to time
Hi
psql -V
psql (PostgreSQL) 7.3
SELECT cast(cast('1 day 12 hours' as interval) as time);
time
--
12:00:00
(1 row)
psql -V
psql (PostgreSQL) 7.3.2
SELECT cast(cast('1 day 12 hours' as interval) as time);
time
--
00:00:00
(1 row)
Did I miss something? I looked into 'history' file, but I couldn't find
anything interesting about changes in casting interval into time.
I found replacement for this problem, but I wan't just to know what
happened:
('1970-1-1'::date+some_interval)::time
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Let join syntax
Manfred Koizar <[EMAIL PROTECTED]> writes: > On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" > <[EMAIL PROTECTED]> wrote: >> from >> coh x0 , cpy x1 ,bra x2 , >> cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND >> x5.usr_id=x0.usr_id AND [...] >> >> Unfortunatelly, postgres returns me the following error : >> Error: ERROR: Relation "x0" does not exist > Yes, because the LEFT OUTER JOIN only sees x4 and x5. The way that query is constructed, you've put all of the join conditions into the LEFT JOIN's constraint, which will not do what you want even if there weren't a syntactic failure. As an example, consider the difference between (a cross join b) left join c on (a.a1 = b.b1 and a.a2 = c.c2) (a join b on a.a1 = b.b1) left join c on (a.a2 = c.c2) The former is almost surely wrong: it will produce a row for *every* combination in the cross product of a and b. Rows where a1 != b1 will still be emitted --- but the c columns will be nulled out, even if a2 = c2 is true, because the left join condition is false at such rows. The second one is probably what was meant, instead. In short, the reason why the SQL spec syntax for JOIN is the way it is is that there's a big difference between conditions you put in an outer join's ON clause and those you put elsewhere. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Let join syntax
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" <[EMAIL PROTECTED]> wrote: >from >coh x0 , cpy x1 ,bra x2 , >cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND >x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, >dpr x6 where ((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id >= x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id >= x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id >= x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id >= x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id >= x0.dpr_id ) Nicolas, sometimes reformatting a query helps a lot: FROM coh x0 , cpy x1 ,bra x2 , cur x3 , tad x4 LEFT OUTER JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6 WHERE x0.cpy_id = x1.cpy_id AND x0.bra_id = x2.bra_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.tad_id = x4.tad_id AND x2.bra_id = x6.bra_id AND x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id First note that the last four lines duplicate the ON conditions thus effectively turning the OUTER JOIN into an INNER JOIN. As I suspect that that was not your intention, simply omit those four conditions from the WHERE clause. Now inserting INNER JOIN where the syntax forces us to do so leads to (completely untested): FROM coh x0 INNER JOIN bra x2 ON x0.bra_id = x2.bra_id INNER JOIN tad x4 ON x2.tad_id = x4.tad_id LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, cpy x1, cur x3, dpr x6 WHERE x0.cpy_id = x1.cpy_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.bra_id = x6.bra_id You might feel like replacing the remaining commas in the FROM clause and the corresponding WHERE conditions with semantically equivalent INNER JOINs. But this limits the freedom of the planner which may be a good or a bad thing... Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] casting interval to time
Tomasz Myrta <[EMAIL PROTECTED]> writes:
> psql (PostgreSQL) 7.3.2
> SELECT cast(cast('1 day 12 hours' as interval) as time);
>time
> --
> 00:00:00
> (1 row)
I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] casting interval to time
Dnia 2003-06-16 16:53, Użytkownik Tom Lane napisał:
Tomasz Myrta <[EMAIL PROTECTED]> writes:
psql (PostgreSQL) 7.3.2
SELECT cast(cast('1 day 12 hours' as interval) as time);
time
--
00:00:00
(1 row)
I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
Default Debian package. I got the same result even on a 7.3.3 Debian
package.
postgresql.conf contains:
DATESTYLE = 'iso,european'
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] [pgmail] - I have problems with pgmail
I install language and all codes ... but I have problems with text in french with accents "ééé èèè" The datas are correct in database but tcl does not recognize the format and trnasform accents when I send mail ! ... What could I do ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] casting interval to time
Tomasz Myrta <[EMAIL PROTECTED]> writes:
> Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
>> Tomasz Myrta <[EMAIL PROTECTED]> writes:
>>
>>> psql (PostgreSQL) 7.3.2
>>> SELECT cast(cast('1 day 12 hours' as interval) as time);
>>> time
>>> --
>>> 00:00:00
>>> (1 row)
>>
>>
>> I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
> Default Debian package. I got the same result even on a 7.3.3 Debian
> package.
What do you get from "pg_config --configure"? What's the hardware
platform --- i386, or something else? Can anyone else reproduce this,
on any platform?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [pgmail] - I have problems with pgmail
BenLaKnet <[EMAIL PROTECTED]> writes: > The datas are correct in database but tcl does not recognize the format > and trnasform accents when I send mail ! ... Try making sure that client_encoding is set to unicode. (libpgtcl probably ought to enforce this, but it doesn't at the moment.) 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] casting interval to time
On Mon, 16 Jun 2003, Tom Lane wrote:
> Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
> > > Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > >
> > > > psql (PostgreSQL) 7.3.2
> > > > SELECT cast(cast('1 day 12 hours' as interval) as time);
> > > > time
> > > > --
> > > > 00:00:00
> > > > (1 row)
> > >
> > >
> > > I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
>
> > Default Debian package. I got the same result even on a 7.3.3 Debian
> > package.
>
> What do you get from "pg_config --configure"? What's the hardware
> platform --- i386, or something else? Can anyone else reproduce this,
> on any platform?
template1=# select cast('25 hours'::interval as time);
time
--
00:00:00
(1 row)
template1=# select version();
version
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
It's also Debian (3.0).
% pg_config --configure '--host=i386-linux' '--build=i386-linux'
'--prefix=/usr' '--mandir=/usr/share/man' '--docdir=/usr/share/doc'
'--bindir=/usr/lib/postgresql/bin' '--libdir=/usr/lib'
'--includedir=/usr/include/postgresql' '--enable-recode' '--enable-nls'
'--enable-integer-datetimes' (could be this?)
'--enable-debug'
'--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam'
'--with-openssl' '--with-gnu-ld' '--with-krb5'
'--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4'
'--with-includes=/usr/include/tcl8.4' '--with-maxbackends=64'
'--with-pgport=5432'
'DOCBOOKSTYLE=/usr/share/sgml/docbook/stylesheet/dsssl/modular'
'build_alias=i386-linux' 'host_alias=i386-linux'
---
Not affected: GentooLinux 1.4 rc2 x86 w/ pg 7.3, gcc 3.2.1, glibc 2.3.1 w/
following configure options:
'--prefix=/usr' '--mandir=/usr/share/man' '--host=i586-pc-linux-gnu'
'--docdir=/usr/share/doc/postgresql-7.3' '--libdir=/usr/lib'
'--enable-syslog' '--enable-depend' '--with-gnu-ld' '--with-pam'
'--with-maxbackends=1024' '--with-python' '--with-perl' '--with-java'
'--with-openssl' '--enable-locale' '--enable-nls' '--enable-multibyte'
'--with-CXX' 'CC=gcc' 'CFLAGS=-march=pentium-mmx -O3 -pipe'
'host_alias=i586-pc-linux-gnu'
--
Antti Haapala
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] casting interval to time
Dnia 2003-06-16 17:17, Użytkownik Tom Lane napisał: What do you get from "pg_config --configure"? What's the hardware platform --- i386, or something else? Can anyone else reproduce this, on any platform? The platform is i386. There was no "pg_config" file in binary package. After copying this file from source tgz, I get only one line result: @configure@ Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Let join syntax
Hi, select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv ,x0.coh_d_inv , x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local ,x0.coh_basis_local ,x0.coh_cost_local ,x0.coh_profit_local ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust ,x0.coh_com_held ,x0.coh_com_cancel ,x0.coh_com_topay ,x0.coh_d_paid ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb ,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr ,x1.cpy_cr_tg ,x1.cpy_cr_tb ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name from coh x0 INNER JOIN bra x2 ON x0.bra_id = x2.bra_id INNER JOIN tad x4 ON x2.tad_id = x4.tad_id LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, cpy x1 , cur x3 ,dpr x6 where x0.cpy_id = x1.cpy_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.bra_id = x6.bra_id worked correctly Thanks. > -Message d'origine- > De : Manfred Koizar [mailto:[EMAIL PROTECTED] > Envoye : lundi 16 juin 2003 16:40 > A : Nicolas JOUANIN > Cc : [EMAIL PROTECTED] > Objet : Re: [SQL] Let join syntax > > > On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" > <[EMAIL PROTECTED]> wrote: > >from > >coh x0 , cpy x1 ,bra x2 , > >cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND > >x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, > >dpr x6 where ((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id > >= x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id > >= x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id > >= x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id > >= x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id > >= x0.dpr_id ) > > Nicolas, sometimes reformatting a query helps a lot: > > FROM > coh x0 , cpy x1 ,bra x2 , cur x3 , > tad x4 LEFT OUTER JOIN sec x5 ON > x5.thr_id = x4.thr_id AND > x5.usr_id = x0.usr_id AND > x5.bra_id = x0.bra_id AND > x5.dpr_id = x0.dpr_id, > dpr x6 > WHERE > x0.cpy_id = x1.cpy_id AND > x0.bra_id = x2.bra_id AND > x0.cur_id = x3.cur_id AND > x0.dpr_id = x6.dpr_id AND > x2.tad_id = x4.tad_id AND > x2.bra_id = x6.bra_id AND > x5.thr_id = x4.thr_id AND > x5.usr_id = x0.usr_id AND > x5.bra_id = x0.bra_id AND > x5.dpr_id = x0.dpr_id > > First note that the last four lines duplicate the ON conditions thus > effectively turning the OUTER JOIN into an INNER JOIN. As I suspect > that that was not your intention, simply omit those four conditions > from the WHERE clause. > > Now inserting INNER JOIN where the syntax forces us to do so leads to > (completely untested): > > FROM > coh x0 > INNER JOIN bra x2 ON x0.bra_id = x2.bra_id > INNER JOIN tad x4 ON x2.tad_id = x4.tad_id > LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND > x5.usr_id = x0.usr_id AND > x5.bra_id = x0.bra_id AND > x5.dpr_id = x0.dpr_id, > cpy x1, cur x3, dpr x6 > WHERE > x0.cpy_id = x1.cpy_id AND > x0.cur_id = x3.cur_id AND > x0.dpr_id = x6.dpr_id AND > x2.bra_id = x6.bra_id > > You might feel like replacing the remaining commas in the FROM clause > and the corresponding WHERE conditions with semantically equivalent > INNER JOINs. But this limits the freedom of the planner which may be > a good or a bad thing... > > Servus > Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] date question
Hi; I've got a table in the way: year | month | day | est7237 | est7238 | est7239 | est7250 --+---+-+-+-+-+- 2002 | 9 | 1 | | | | 2002 | 9 | 2 | | | | 2002 | 9 | 3 | | | | ... This table is: murciadb=# \d series_lluvia_completas Table "series_lluvia_completas" Column | Type | Modifiers -+-+--- year| integer | month | integer | day | integer | est7237 | real| est7238 | real| est7239 | real| est7250 | real| And I've got another related tables with a date field. I need to compare the dates field in the other tables with "year", "month", "day" in this table. Is there a way to create a table from this table, directly in Postgres in which a date field is created based in the values of "year", "month", "day" in this table? Thanks and regards Javier ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] question on rules
Dnia 2003-06-13 03:26, Uz.ytkownik [EMAIL PROTECTED] napisa?: hi folks, i'd like to write an rule which fills out some empty attrs on insert (w/ data from other given attrs). the table structure is: CREATE TABLE foo (start date, duration integer, endtime date); and the rule is: CREATE RULE foo1 AS ON INSERT TO foo WHERE new.endtime = NULL DO INSERT INTO foo SELECT new.start, new.duration, new.start + new.duration; when i try to insert some data (INSERT INTO foo SELECT '2003-01-01', '12';) i get the error: query rewritten 10 times, may contain cycles. how can i avoid this loop ? For this case creating trigger would be much better than rule. I think, you can't use rules this way. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] "No such attribute or function 'oid'"
Hi; I'm using ODBC driver for windows, because I use Excel (Microsoft) to work with data. When I try to get data from a Postgres table there is no problem; but when I try to acces a view I get the mesage: "No such attribute or function 'oid'" When I try to access views from ACCESS2000 there is no problem What is the cause of this? Can I resolve it? Thanks and regards Javier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] UNION & LIMIT & ORDER BY
Hi Why this query doesn't work ? << SELECT '1' AS id FROM users ORDER BY id LIMIT 1 UNION SELECT '2' AS id FROM users >> And this one work ? << SELECT '1' AS id FROM users UNION SELECT '2' AS id FROM users ORDER BY id LIMIT 1 >> -- - | Dans le cours d'un long gouvernement, on va au mal par une pente | | insensible, et ne remonte au bien que par un effort. | | Montesquieu, "de l'esprit des lois" livre V/VII | - Emmanuel Engelhart alias Kelson | ICQ UIN : 53388731 | TEL 01.58.46.04.79 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Ordering in an aggregate -- points to paths
From: "Tom Lane" <[EMAIL PROTECTED]> > Yeah, this is a fairly obvious thing to want to do with a user-written > aggregate. It does not work in released versions, because the planner > does not notice that the inner SELECT's output ordering matches what > the GROUP BY needs, and so it inserts an additional Sort plan step > above the sub-select (you can see this if you look at EXPLAIN output). > Unfortunately, on most platforms qsort() isn't stable and will not > preserve the ordering of its input for equal keys. So you lose the > minor ordering by seq_no in the re-sort. Most grateful for the rapid response Tom. Knowing that, I can work around by iterating through the firs at the application level. Regards Julian Scarfe PS: you shouldn't be working on a Sunday, it's bad for you ;-) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] date question
On Fri, Jun 13, 2003 at 13:44:09 +0200, javier garcia - CEBAS <[EMAIL PROTECTED]> wrote: > > And I've got another related tables with a date field. I need to compare the > dates field in the other tables with "year", "month", "day" in this table. > > Is there a way to create a table from this table, directly in Postgres in > which a date field is created based in the values of "year", "month", "day" > in this table? Yes. You could concatenate the values for year, month and day and cast the resulting string to date. In the long run you might be better storing the dates as dates in the table and extracting the year, month and day when reporting (or perhaps using a view to add them as additional columns). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "No such attribute or function 'oid'"
Dnia 2003-06-13 13:59, Uz.ytkownik javier garcia - CEBAS napisa?: Hi; I'm using ODBC driver for windows, because I use Excel (Microsoft) to work with data. When I try to get data from a Postgres table there is no problem; but when I try to acces a view I get the mesage: "No such attribute or function 'oid'" When I try to access views from ACCESS2000 there is no problem What is the cause of this? Can I resolve it? Thanks and regards Javier "oid" is a table field. If you create view and you don't expose oid from any table - you loose access to this field. Regards, Tomasz Myrta ---(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] UNION & LIMIT & ORDER BY
Hi, Le Samedi 14 Juin 2003 17:01, Emmanuel Engelhart a écrit : > [...] Taken from the postgresql manual (http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-select.html#SQL-UNION): ORDER BY and LIMIT can be attached to a sub-expression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression. Regards. -- Guillaume . ---(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] casting interval to time
Antti Haapala <[EMAIL PROTECTED]> writes: > '--enable-integer-datetimes' (could be this?) Bingo. I can reproduce it with that configure choice. Should have the answer soon ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] casting interval to time
Antti Haapala <[EMAIL PROTECTED]> writes:
> It's also Debian (3.0).
On investigation the interval_time() function was completely broken for
the --enable-integer-datetimes case --- it was reducing the interval
value modulo one second, rather than modulo one day as intended.
I also noticed that neither the integer nor float case behaved
rationally for negative intervals. I've applied the attached patch
to 7.3 and HEAD ...
regards, tom lane
*** src/backend/utils/adt/date.c.orig Thu Feb 13 12:04:24 2003
--- src/backend/utils/adt/date.cMon Jun 16 14:56:53 2003
***
*** 999,1004
--- 999,1009
/* interval_time()
* Convert interval to time data type.
+ *
+ * This is defined as producing the fractional-day portion of the interval.
+ * Therefore, we can just ignore the months field. It is not real clear
+ * what to do with negative intervals, but we choose to subtract the floor,
+ * so that, say, '-2 hours' becomes '22:00:00'.
*/
Datum
interval_time(PG_FUNCTION_ARGS)
***
*** 1007,1021
TimeADT result;
#ifdef HAVE_INT64_TIMESTAMP
result = span->time;
! if ((result >= INT64CONST(864))
! || (result <= INT64CONST(-864)))
! result -= (result / INT64CONST(100) * INT64CONST(100));
#else
- Intervalspan1;
-
result = span->time;
! TMODULO(result, span1.time, 86400e0);
#endif
PG_RETURN_TIMEADT(result);
--- 1012,1034
TimeADT result;
#ifdef HAVE_INT64_TIMESTAMP
+ int64 days;
+
result = span->time;
! if (result >= INT64CONST(864))
! {
! days = result / INT64CONST(864);
! result -= days * INT64CONST(864);
! }
! else if (result < 0)
! {
! days = (-result + INT64CONST(864-1)) / INT64CONST(864);
! result += days * INT64CONST(864);
! }
#else
result = span->time;
! if (result >= 86400e0 || result < 0)
! result -= floor(result / 86400e0) * 86400e0;
#endif
PG_RETURN_TIMEADT(result);
---(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] Our FLOAT(p) precision does not conform to spec
Fernando Nasser of Red Hat pointed out to me that we are not quite spec-compliant on the FLOAT(p) datatype notation. We interpret P as the number of decimal digits of precision, and hence translate P = 1..6 => float4 (a/k/a REAL) P = 7..15 => float8 (a/k/a DOUBLE PRECISION) otherwise error However, the spec is perfectly clear that P is to be interpreted as the number of *binary* digits of precision, not decimal digits. SQL92 section 4.4.1 says: An approximate numeric value consists of a mantissa and an expo- nent. The mantissa is a signed numeric value, and the exponent is a signed integer that specifies the magnitude of the mantissa. An approximate numeric value has a precision. The precision is a posi- tive integer that specifies the number of significant binary digits ^^ in the mantissa. The value of an approximate numeric value is the mantissa multiplied by 10^exponent. So it's fairly clear that P is not the number of decimal digits. (The reference to multiplying by 10^exponent seems bogus, since on machines where the mantissa is in fact binary, one would expect a base-2 or possibly base-16 exponent to be used. But this does not affect the precision of the mantissa AFAICS.) On the assumption that most platforms have IEEE float math, it would be appropriate to interpret P like this: P = 1..24 => float4 P = 25..53 => float8 otherwise error This is a straightforward change and would not break pg_dump files, since fortunately pg_dump always references the underlying types and never refers to anything as FLOAT(p). But I wonder whether it is likely to break many existing applications. There is a hazard of some existing app asking for (what it thinks is) float8 and getting float4 instead. Is it worth trying to provide some sort of backwards-compatibility mode? We could imagine adding a GUC variable to select binary or decimal precision, but I really don't want to. It would increase the amount of work needed by more than an order of magnitude, and this problem doesn't seem worth it. I'd rather just list this under Incompatibilities in the 7.4 release notes. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] unique keys on inherited classes
hi folks, in many of my applications i'm deriving all classes from the base _inode, which stores general information needed for mirroring. the _inode class defines an inode_id (cluster-wide object id) and an timestamp field for the modification time. I've defined an unique index on the inode_id field of _inode. This doesn't seem to be inherited to the derived classes. The goal is to prevent double inode_id values in all _all_ derived classes. How could i achieve this ? cu -- - Enrico Weigelt== metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 smsgate: [EMAIL PROTECTED] - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ ---(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] Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references Oracle in its examples, though I can usually get PostgreSQL to work almost as well. Well, I'm almost to the end of the book and I'm trying to port some of the book's PL/SQL examples to PL/pgSQL, with mixed success. In this case, I translated BEGIN DECLARE UnknownPayType EXCEPTION; CURSOR pay_cursor IS SELECT name, pay_type, pay_rate, eff_date, sysdate, rowid FROM pay_table; IndRec pay_cursor%ROWTYPE; . . . END; / to this: CREATE OR REPLACE FUNCTION update_pay() RETURNS VOID AS ' DECLARE pay_cursor CURSOR IS SELECT name, pay_type, pay_rate, eff_date, current_date, oid FROM pay_table; IndRec pay_cursor%ROWTYPE; cOldDate DATE; . . . END; ' LANGUAGE 'plpgsql'; The problem is, when I call the function, I get: sql-practice=# select update_pay(); ERROR: pay_cursor: no such class WARNING: plpgsql: ERROR during compile of update_pay near line 2 WARNING: plpgsql: ERROR during compile of update_pay near line 2 ERROR: pay_cursor: no such class I tried several ways of defining pay_cursor, with the same result. What am I to do? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
On Mon, 16 Jun 2003, Tony G. Harris wrote: > Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The > text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references > Oracle in its examples, though I can usually get PostgreSQL to work > almost as well. > > Well, I'm almost to the end of the book and I'm trying to port some of > the book's PL/SQL examples to PL/pgSQL, with mixed success. In this > case, I translated > > BEGIN > DECLARE > UnknownPayType EXCEPTION; > CURSOR pay_cursor IS >SELECT name, pay_type, pay_rate, eff_date, sysdate, > rowid >FROM pay_table; > IndRec pay_cursor%ROWTYPE; > . . . > END; > / > > to this: > > CREATE OR REPLACE FUNCTION update_pay() RETURNS VOID AS ' > DECLARE > pay_cursor CURSOR IS >SELECT name, pay_type, pay_rate, eff_date, current_date, oid >FROM pay_table; > IndRec pay_cursor%ROWTYPE; > cOldDate DATE; > . . . > END; > ' > LANGUAGE 'plpgsql'; > > The problem is, when I call the function, I get: > > sql-practice=# select update_pay(); > ERROR: pay_cursor: no such class > WARNING: plpgsql: ERROR during compile of update_pay near line 2 > WARNING: plpgsql: ERROR during compile of update_pay near line 2 > ERROR: pay_cursor: no such class > > I tried several ways of defining pay_cursor, with the same result. > What am I to do? I think it's complaining because you can't use pay_cursor%ROWTYPE. Maybe declaring IndRec as being of type record may work (don't know for certain). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] select date range?
Hi, When I use sql statement to select the date range from $_POST value,It doesn't work. --- "select * from mydatabase where mydate between '$_POST[start_date]' and '$_POST[end_date]' " There is no error,but the result is not I want. ps: (1)mydate attribute is timestamp (2)$_POST[start_date] is something like '2003/05/12'
Re: [SQL] [HACKERS] Our FLOAT(p) precision does not conform to spec
Tom Lane wrote: > Is it worth trying to provide some sort of backwards-compatibility mode? > We could imagine adding a GUC variable to select binary or decimal > precision, but I really don't want to. It would increase the amount of > work needed by more than an order of magnitude, and this problem doesn't > seem worth it. I'd rather just list this under Incompatibilities in the > 7.4 release notes. Let's just change it and list it in the release notes as an incompatibility --- anything else is too confusing. -- 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 4: Don't 'kill -9' the postmaster
Re: [SQL] Our FLOAT(p) precision does not conform to spec
Fix the problem and inform the users about code that may break. Rick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select date range?
Ko, > (1)mydate attribute is timestamp > (2)$_POST[start_date] is something like '2003/05/12' You probably need to re-format the POST date before passing it to the back-end. For example, my compilation of PostgreSQL would not necessarily correctly recognize that date format. Try re-formatting the date to '2003-05-12' -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] select date range?
hmm it should work fine. Remember, the statement where mydate between '2003/05/12' and '2003/06/17' would ONLY return records from '2003/05/12 to 2003/06/16 since your type is a timestamp. 2003/06/17 actually means 2003/06/17 00:00:00.00+00. so a timestamp of 2003/06/17 03:01:10.102022+08 would not be included in the query. got it? ramil On Tue, 2003-06-17 at 10:56, ko wrote: > Hi, > > When I use sql statement to select the date range from $_POST value,It > doesn't work. > > --- > "select * from mydatabase where mydate between '$_POST[start_date]' > and '$_POST[end_date]' " > > There is no error,but the result is not I want. > > ps: > (1)mydate attribute is timestamp > (2)$_POST[start_date] is something like '2003/05/12' > ---(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] Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Mon, 16 Jun 2003, Tony G. Harris wrote: >> The problem is, when I call the function, I get: >> sql-practice=# select update_pay(); >> ERROR: pay_cursor: no such class >> WARNING: plpgsql: ERROR during compile of update_pay near line 2 >> ERROR: pay_cursor: no such class > I think it's complaining because you can't use pay_cursor%ROWTYPE. Yeah, you're right. I didn't believe that theory at first because the line number reference didn't point at the line with %ROWTYPE ... but upon digging into it I find that plpgsql's code for determining the line number to report is flat wrong for this case. (I've committed a quick fix into CVS tip, but I wonder whether the whole mechanism shouldn't be rethought. Calling plpgsql_scanner_lineno() all over the place doesn't seem real clean.) > Maybe declaring IndRec as being of type record may work (don't know > for certain). That's what I'd try. We don't consider that declaring a cursor creates a named rowtype ... I'm surprised that Oracle seems to think it does. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
