[SQL] substr or char_length problem
Dear ALL
I have a problem with function substr or char_length or both. I guery A2 and
it works fine. But sometimes gives 'ERROR: negative substring length not
allowed'. When I test many many times with diffrent values, never gives
error. Sample table and query below.
A1 A2
---
1 1957
2 197
3 19
4
5 NULL
6 1
7 195
Select * from tbl_xxx where
tbl_xxx.A2=substr('196895588454554545454',0,char_length(tbl_xxx.A2)+1);
Thanks
Fatih Cerit
---(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: [despammed] [SQL] substr or char_length problem
am 17.06.2005, um 13:01:15 +0300 mailte Fatih Cerit folgendes:
> Dear ALL
>
> I have a problem with function substr or char_length or both. I guery A2
> and it works fine. But sometimes gives 'ERROR: negative substring length
> not allowed'. When I test many many times with diffrent values, never gives
> error. Sample table and query below.
>
>
>
> A1 A2
> ---
> 1 1957
> 2 197
> 3 19
> 4
> 5 NULL
> 6 1
> 7 195
>
>
> Select * from tbl_xxx where
> tbl_xxx.A2=substr('196895588454554545454',0,char_length(tbl_xxx.A2)+1);
Perhaps because char_length() returns NULL and this is a invalid value
for substr(). Use coalesce():
... substr('196895588454554545454',0,coalesce(char_length(tbl_xxx.A2),0)+1);
Regards, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(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] substr or char_length problem
On Fri, Jun 17, 2005 at 12:25:28PM +0200, Andreas Kretschmer wrote:
> am 17.06.2005, um 13:01:15 +0300 mailte Fatih Cerit folgendes:
> >
> > I have a problem with function substr or char_length or both. I guery A2
> > and it works fine. But sometimes gives 'ERROR: negative substring length
> > not allowed'. When I test many many times with diffrent values, never gives
> > error. Sample table and query below.
> >
> > A1 A2
> > ---
> > 1 1957
> > 2 197
> > 3 19
> > 4
> > 5 NULL
> > 6 1
> > 7 195
> >
> > Select * from tbl_xxx where
> > tbl_xxx.A2=substr('196895588454554545454',0,char_length(tbl_xxx.A2)+1);
>
> Perhaps because char_length() returns NULL and this is a invalid value
> for substr(). Use coalesce():
substr() is marked STRICT, also known as RETURNS NULL ON NULL INPUT,
so it should simply return NULL if the length is NULL:
SELECT oid::regprocedure, proisstrict
FROM pg_proc
WHERE proname = 'substr';
oid | proisstrict
---+-
substr(bytea,integer) | t
substr(text,integer) | t
substr(bytea,integer,integer) | t
substr(text,integer,integer) | t
(4 rows)
SELECT substr('196895588454554545454', 0, NULL + 1) IS NULL;
?column?
--
t
(1 row)
The error "negative substring length not allowed" implies that the
length being passed is negative. Since the query adds 1 to the
return value of char_length(), that implies that char_length() is
returning a value <= -2. I don't know what could cause that short
of a bug in the backend. Or am I missing something?
I couldn't duplicate the error with the given example -- is that the
real data and query or just a contrived example that doesn't actually
fail? What version of PostgreSQL are you using? What encoding?
What OS and version? What are the results of the following query?
SELECT a1, char_length(a2), a2 FROM tbl_xxx WHERE char_length(a2) < 0;
Could you post a self-contained test case, that is, a complete list
of SQL statements that somebody could load into an empty database
to reproduce the problem?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] how can i UPDATE without dead rows
Hi! My program must use UPDATE usualy, and there will be many dead rows in this table what throttle down the db server. I would like to update so not create dead row. How can i do this? sorry for my english :)
Re: [SQL] UPDATEABLE VIEWS ... Examples?
Dmitri Bichko wrote: > warn "WARNING: dmitrisms are on, some assumptions may not make sense" beauty! :) -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] how can i UPDATE without dead rows
[EMAIL PROTECTED] wrote: Hi! My program must use UPDATE usualy, and there will be many dead rows in this table what throttle down the db server. I would like to update so not create dead row. How can i do this? You can't - that's how MVCC works. Make sure your free-space-map settings are large enough in postgresql.conf and that you vacuum enough - that will keep the "dead" space being re-used. -- Richard Huxton Archonet Ltd ---(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] SELECT with sum on groups ORDERING by the subtotals
Hi Greg, Thanks for your reply. Yes, same group of code... Perfect solution, simple and efficient. Thank you very much!!! Cheers, Rodrigo Carvalhaes Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I need an subtotal for all the products with the same group and that the query be ordered by the bigger subtotal. (please proofread: the subtotals in your example output did not add up) By "same group" I presume you mean the same code, as you don't actually use the "group varchar(10)" column you created in your example. A major problem you have is that you have no other way of ordering the rows except by the code. So having a running subtotal is fairly pointless, as the items within each code will appear randomly. Since only the grand total for each code is significant, you could write something like this: SELECT t.*, s.subtotal FROM (SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1) s, test t WHERE s.code = t.code ORDER BY subtotal desc; code | description | quant | price | total | subtotal - ---+-+---+---+---+-- 99120 | PRODUCT C |10 | 0.8 | 8 | 338 99120 | PRODUCT C | 100 | 0.8 |80 | 338 99120 | PRODUCT C | 200 | 0.8 | 160 | 338 99120 | PRODUCT C | 100 | 0.9 |90 | 338 92110 | PRODUCT A |10 | 1 |10 | 120 92110 | PRODUCT A | 5 | 0.9 | 9 | 120 92110 | PRODUCT A | 100 | 0.9 |90 | 120 92110 | PRODUCT A |10 | 1.1 |11 | 120 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |20 | 0.8 | 8 | 41 If you don't need all that intermediate stuff: SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1 ORDER BY 2 DESC; code | subtotal - ---+-- 99120 | 338 92110 | 120 92190 | 41 If you do need the other rows, you will have to specify a way of ordering the rows within a code group. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506161458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM 79gJZ2hUgDk1jL3LDQv3le0= =mpnW -END PGP SIGNATURE- -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
