Re: [SQL] Case Insensitive Queries
Can you please explain in little more detail? I am curious.
I haven't noticed any discussion about upper() being different from
lower() when it comes to such comparisons. As far as I know, upper()
and lower() only operate on ascii characters a-z. If you are using the
default locale, neither function should have any impact on characters
in the extended ascii range.
If upper() and lower() operate on characters in 8859-1 and other character
sets when the appropriate locale is set, then a difference in the behavior
of upper() and lower() would seem like a bug.
If you can shed some light on this, I would appreciate it.
Thanks,
Troy
>
> Mark writes:
> > Is it possible to execute a query using a where clause that allows case
> > insensitive comparison between a field and text.
>
> select * from account where upper(username) = upper('test')
>
> (Upper used because, as has been remarked on this list and in other places,
> folding from richer character sets is likely to get better matches this way).
>
> And yes, you can create an index on upper(fieldname).
>
> Dan
>
> ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Difficult SQL Statement
> I have a table that has the following columns: AUTHOR_NO, ASMT_CODE, & > STATUS. The table would look something like this: > AUTHOR_NO ASMT_CODE STATUS > 12345 1 PASSED > 12345 2 FAILED > 12345 3 FAILED > 12345 4 PASSED > 12346 1 PASSED > 12346 2 PASSED > 12346 3 PASSED > 654321 1 FAILED > 654321 2 PASSED > 654321 3 FAILED > 654321 4 FAILED > 000123 1 PASSED > > So I am trying to write a SQL statement that will return the > ASMT_CODE, the total number of PASSED for the ASMT_CODE, > the total number of participants for that ASMT_CODE and finally a > percent of the PASSED for that particular ASMT_CODE over the number of > participants for that ASMT_CODE. > So, if I had the table above I would get something like this: > > ASMT_CODE # PASSEDTOTAL # % of Total > 1 3 4 75 > 2 2 3 66.67 > 3 1 3 33.34 > 4 1 2 50 > > As you notice I am look for the ASMT_CODE base percentage rather than > the over all percentage. What would be the SQL to do this? > > I have tried to write this, but cannot figure out how to calculate the > last two columns. Here is what I have so far: > select d1.asmt_code, count(d1.amst_code) > from test_run d1 > where d1.status = 'PASSED' > group by d1.asmt_code > order by d1.asmt_code > BUT this only yields me the first two columns. > > CAN ANYONE HELP? You can get the first 3 columns with one statement - the fourth column should be calculated outside the query. Try this: select d1.asmt_code, count(case when d1.status = 'PASSED' then 1 else NULL end) as passed, count(d1.amst_code) as total from test_run d1 group by d1.asmt_code order by d1.asmt_code HTH, -- Renato Sao Paulo - SP - Brasil [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Left Joins...
> I've got a nasty query that joins a table onto itself like 22 times. > I'm wondering if there might be a better way to do this, and also how > I can left join every additional table on the first one. By this I > mean that if f1 matches my criteria and therefore isn't null, then > every other joined field will occur, null or not... > > Here is a snippet of my query so you can see what I'm doing: > SELECT > f1.strval,f2.strval,f3.strval,f4.strval,f5.strval,f6.strval,f7.strval, > f8.strval,f9.strval,f10.strval,f11.strval,f12.strval,f13.strval,f14.st > rval > ,f15.strval,f16.strval,f17.strval,f18.strval,f19.strval,f20.strval,m1. > strval > FROM formdata AS f1 > LEFT JOIN formdata AS f2 ON (f2.formid=4 AND f2.occid=1 AND > f2.fieldid=2 AND f2.userid=f1.userid) > LEFT JOIN formdata AS f3 ON (f3.formid=4 AND f3.occid=1 AND > f3.fieldid=3 AND f3.userid=f1.userid) > LEFT JOIN formdata AS f4 ON (f4.formid=4 AND f4.occid=1 AND > f4.fieldid=4 AND f4.userid=f1.userid) > LEFT JOIN formdata AS f5 ON (f5.formid=4 AND f5.occid=1 AND > f5.fieldid=5 AND f5.userid=f1.userid) > LEFT JOIN formdata AS f6 ON (f6.formid=4 AND f6.occid=1 AND > f6.fieldid=6 AND f6.userid=f1.userid) > LEFT JOIN formdata AS f7 ON (f7.formid=4 AND f7.occid=1 AND > f7.fieldid=7 AND f7.userid=f1.userid) > LEFT JOIN formdata AS f8 ON (f8.formid=4 AND f8.occid=1 AND > f8.fieldid=8 AND f8.userid=f1.userid) > LEFT JOIN formdata AS f9 ON (f9.formid=4 AND f9.occid=1 AND > f9.fieldid=9 AND f9.userid=f1.userid) > [...] > > So I don't care if f2..f22 do not exist, but f1 must exist... > > Any ideas? I'm not sure if I understood your problem, perhaps you want something like this: SELECT f1.strval AS val1, (SELECT f2.strval FROM formdata f2 WHERE f2.formid=f1.formid AND f2.occid=f1.occid AND f2.fieldid=2 AND f2.userid=f1.userid) AS val2, (SELECT f3.strval FROM formdata f3 WHERE f3.formid=f1.formid AND f3.occid=f1.occid AND f3.fieldid=3 AND f3.userid=f1.userid) AS val3, (SELECT f4.strval FROM formdata f4 WHERE f4.formid=f1.formid AND f4.occid=f1.occid AND f4.fieldid=4 AND f4.userid=f1.userid) AS val4,... FROM formdata f1 WHERE f1.formid=4 AND f1.occid=1 AND f1.fieldid=1 HTH, -- Renato Sao Paulo - SP - Brasil [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ERROR: Class '37632' not found
Hello, I have a problem to drop a rule from my database. When I want to create a rule *r_name*, I get the message, that this rule still exists and when I want to drop my rule *r_name*, I get the message: Class '37632' not found Please, can anyone help me to solve this problem. Thanks in advance Irina E-Mail: [EMAIL PROTECTED]
Re: [SQL] Case Insensitive Queries
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > If upper() and lower() operate on characters in 8859-1 and other character > sets when the appropriate locale is set, then a difference in the behavior > of upper() and lower() would seem like a bug. Au contraire ... upper() and lower() are not symmetric operations in quite a few non-English locales. I'll let those who regularly work with them give specific details, but handling of accents, German esstet (sp?), etc are the gotchas that I recall. 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] ERROR: Class '37632' not found
=?iso-8859-1?Q?DI_Hasen=F6hrl?= <[EMAIL PROTECTED]> writes: > When I want to create a rule *r_name*, I get the message, that this rule st= > ill exists and when I want to drop my rule *r_name*, I get the message: Cla= > ss '37632' not found Curious. That seems to indicate that the table the old rule is for has been deleted ... but why is the rule still there? It should've been deleted too. What version of Postgres is this? Do you recall exactly what you did with the old table? You might be able to get back into a consistent state by manually removing the rule entry (delete from pg_rewrite where rulename = 'foo') but I'm hesitant to recommend that when we don't know how you got into this state in the first place. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Case Insensitive Queries
It appears that the behavior of a bpchar compare with a string literal
is not implicitly trimming the bpchar before the compare, which IMHO is
incorrect behavior. Is my opinion valid? If so, how difficult of a fix
would this be in terms of time and effort? Should I submit a bug report
to another list, or is a developer receiving this? Is this a feature?
This is an important issue for me, because I am converting a db from MS
SQL to postgresql. The MS SQL database uses bpchar (or just char in MS
SQL terms) because performance is slightly better; the compares
automatically trim the blanks off of the char at compare time. I have
over 150 tables to work with, and I would rather not have to change them
from bpchar to varchar, not to mention the performance decrease this
might incur.
You might be thinking, 'just use trim(username) everywhere you compare'.
Yes, that is a solution, but not a practical one in my case. If this is
a bug, I don't want to hack around it: I'd rather wait for the fix.
Varchars would incur performance penalties I want to try to avoid if at
all possible.
Thanks,
Mark
On 29 May 2001 09:55:18 -0700, Dan Lyke wrote:
> Mark writes:
> > Is it possible to execute a query using a where clause that allows
case
> > insensitive comparison between a field and text.
>
> select * from account where upper(username) = upper('test')
>
> (Upper used because, as has been remarked on this list and in other
places,
> folding from richer character sets is likely to get better matches
this way).
>
> And yes, you can create an index on upper(fieldname).
>
> Dan
>
> ---(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://www.postgresql.org/search.mpl
Re: [SQL] Case Insensitive Queries
It appears that the behavior of a bpchar compare with a string literal
is not implicitly trimming the bpchar before the compare, which IMHO is
incorrect behavior. Is my opinion valid? If so, how difficult of a fix
would this be in terms of time and effort? Should I submit a bug report
to another list, or is a developer receiving this? Is this a feature?
This is an important issue for me, because I am converting a db from MS
SQL to postgresql. The MS SQL database uses bpchar (or just char in MS
SQL terms) because performance is slightly better; the compares
automatically trim the blanks off of the char at compare time. I have
over 150 tables to work with, and I would rather not have to change them
from bpchar to varchar, not to mention the performance decrease this
might incur.
You might be thinking, 'just use trim(username) everywhere you compare'.
Yes, that is a solution, but not a practical one in my case. If this is
a bug, I don't want to hack around it: I'd rather wait for the fix.
Varchars would incur performance penalties I want to try to avoid if at
all possible.
Thanks,
Mark
On 29 May 2001 09:55:18 -0700, Dan Lyke wrote:
> Mark writes:
> > Is it possible to execute a query using a where clause that allows
case
> > insensitive comparison between a field and text.
>
> select * from account where upper(username) = upper('test')
>
> (Upper used because, as has been remarked on this list and in other
places,
> folding from richer character sets is likely to get better matches
this way).
>
> And yes, you can create an index on upper(fieldname).
>
> Dan
>
> ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
bpchar compares (was Re: [SQL] Case Insensitive Queries)
It appears that the behavior of a bpchar compare with a string literal is not implicitly trimming the bpchar before the compare, which IMHO is incorrect behavior. Is my opinion valid? If so, how difficult of a fix would this be in terms of time and effort? Should I submit a bug report to another list, or is a developer receiving this? Is this a feature? This is an important issue for me, because I am converting a db from MS SQL to postgresql. The MS SQL database uses bpchar (or just char in MS SQL terms) because performance is slightly better; the compares automatically trim the blanks off of the char at compare time. I have over 150 tables to work with, and I would rather not have to change them from bpchar to varchar, not to mention the performance decrease this might incur. You might be thinking, 'just use trim(username) everywhere you compare'. Yes, that is a solution, but not a practical one in my case. If this is a bug, I don't want to hack around it: I'd rather wait for the fix. Varchars would incur performance penalties I want to try to avoid if at all possible. Thanks, Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case Insensitive Queries
I use a few of them, and in my opinion there is a distinct group of
characters at last in the 8859-1 character set which have a lower and
upper case instance. The ranges are 0xC0 to 0xDD for upper case and 0xE0
to 0xFD for upper (with the exception of 0xD0, 0xD7, 0xF0, and 0xF7).
I haven't examined all the relevant docs, so I might be wrong. The lists
are based on my own observations of the characters in question.
There is probably no harm in sending a few extra bytes, so I am appending
a related function below. If someone finds a flaw with the function,
please tell me; that would be greatly appreciated.
I am also including a list of related characters. This email is going
out with 8859-1 as the charset, so I hope you are able to view them.
UPPER CASE:
192: À (0xc0) 193: Á (0xc1) 194: Â (0xc2) 195: Ã (0xc3)
196: Ä (0xc4) 197: Å (0xc5) 198: Æ (0xc6) 199: Ç (0xc7)
200: È (0xc8) 201: É (0xc9) 202: Ê (0xca) 203: Ë (0xcb)
204: Ì (0xcc) 205: Í (0xcd) 206: Î (0xce) 207: Ï (0xcf)
209: Ñ (0xd1) 210: Ò (0xd2) 211: Ó (0xd3)
212: Ô (0xd4) 213: Õ (0xd5) 214: Ö (0xd6)
216: Ø (0xd8) 217: Ù (0xd9) 218: Ú (0xda) 219: Û (0xdb)
220: Ü (0xdc) 221: Ý (0xdd)
LOWER CASE:
224: à (0xe0) 225: á (0xe1) 226: â (0xe2) 227: ã (0xe3)
228: ä (0xe4) 229: å (0xe5) 230: æ (0xe6) 231: ç (0xe7)
232: è (0xe8) 233: é (0xe9) 234: ê (0xea) 235: ë (0xeb)
236: ì (0xec) 237: í (0xed) 238: î (0xee) 239: ï (0xef)
241: ñ (0xf1) 242: ò (0xf2) 243: ó (0xf3)
244: ô (0xf4) 245: õ (0xf5) 246: ö (0xf6)
248: ø (0xf8) 249: ù (0xf9) 250: ú (0xfa) 251: û (0xfb)
252: ü (0xfc) 253: ý (0xfd)
SKIPPED
208: Ð (0xd0)
215: × (0xd7)
222: Þ (0xde)
240: ð (0xf0)
247: ÷ (0xf7)
254: þ (0xfe)
CREATE FUNCTION lower8859_1 (text) RETURNS text
AS '/usr/include/pgsql/lib/str8859_1.so'
LANGUAGE 'C';
/* No warranty of any kind, use at your own risk. Use freely.
*/
text * lower8859_1 (text * str1) {
text * result;
int32 len1 = 0, i;
unsigned char * p, * p2, c;
unsigned char upper_min = 0xC0;
unsigned char upper_max = 0xDD;
len1 = VARSIZE(str1) - VARHDRSZ;
if (len1 <= 0)
return str1;
result = (text *) palloc (len1 + 2 + VARHDRSZ);
if (! result)
return str1;
memset (result, 0, len1 + 2 + VARHDRSZ);
p = VARDATA(result);
p2 = VARDATA(str1);
for (i=0; i < len1; i++) {
c = p2[i];
if (isupper(c) || (c >= upper_min && c <= upper_max && c != 0xD0 && c != 0xD7))
p[i] = c + 0x20;
else
p[i] = c;
}
VARSIZE(result) = len1 + VARHDRSZ;
return result;
}
Troy
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> > If upper() and lower() operate on characters in 8859-1 and other character
> > sets when the appropriate locale is set, then a difference in the behavior
> > of upper() and lower() would seem like a bug.
>
> Au contraire ... upper() and lower() are not symmetric operations in
> quite a few non-English locales. I'll let those who regularly work with
> them give specific details, but handling of accents, German esstet (sp?),
> etc are the gotchas that I recall.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] primary key scans in sequence
I have the same problem, my primary key is defined as a serial though.
Other tables use tables are defined as serials as well, but DO use Index
Scans
some tables do, some tables don't, even when creating 'my own' index on
the primary key, it still uses sequencial scans!
This one works fine:
Table "sponsors"
Attribute | Type | Modifier
---+-+--
---
id| integer | not null default nextval('sponsors_id_seq'::text)
name | text| not null
email | text|
logo | text| not null default
'images/sponsors/logo_default.gif'
url | text|
qoute | text|
active| boolean | default 't'::bool
main | boolean | default 'f'::bool
Indices: index_sponsors_main,
index_sponsors_name,
sponsors_pkey,
unq_sponosrs_name
dsc_competition=# explain select * from sponsors where id = 4;
NOTICE: QUERY PLAN:
Index Scan using sponsors_pkey on sponsors (cost=0.00..2.01 rows=1
width=66)
EXPLAIN
Now this one doesn't:
Table "teams"
Attribute | Type | Modifier
---+-+--
id| integer | not null default nextval('teams_id_seq'::text)
name | text| not null
mgr_name | text|
address | text|
zipcode | text|
city | text|
country | text|
email | text|
telnr | text|
mobnr | text|
faxnr | text|
logo | text| not null default 'images/teams/logo_default.gif'
movie | text|
url | text|
qoute | text|
active| boolean | default 't'::bool
Indices: index_teams_id, <=!!! 'my own' index
index_teams_name,
teams_pkey,<=normal pkey index
unq_teams_name
NOTICE: QUERY PLAN:
Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
EXPLAIN
I really don't understand the difference between the two, and it didn't
work before i created an extra index on id...
Kind regards,
Koen Antonissen
-Original Message-
From: Richard Poole [mailto:[EMAIL PROTECTED]]
Sent: vrijdag 30 maart 2001 18:12
To: bernd
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] primary key scans in sequence
Because the type of the "mitgliedid" is "bigint", but the type of the
constant "833228" is "integer" (I think; certainly it isn't "bigint").
Postgres doesn't realise that it can use an index on a bigint to do
comparisons to an integer. If you explicitly cast the constant to a
bigint, it should be willing to do an index scan, like so:
select * from mitglied where mitgliedid = 833228::bigint
Yes, this is a bit unpleasant to have to in your client code, and no,
I don't know if there's a neater way to let Postgres know it can use
this index for this query. But what I've just described does work.
Richard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SELECT * INTO TABLE is not working for me.
I am testing my SQL commands in pgaccess before I put them into my C++ code. Trying to copy a table using... SELECT * INTO TABLE copy_stuff FROM the_stuff It creates the view but no table called copy_stuff exists after I run it. Why? I can use... CREATE TABLE copy_stuff AS SELECT * FROM the_stuff that works and makes a new table, but it causes other problems with the clients that this app is running from. I would like to make the SELECT * INTO work. Any idea? -- Roy Souther <[EMAIL PROTECTED]> 01100010 10101110 11000110 11010110 0100 10110010 10010110 11000110 01001110 0110 11001110 00010110 10010110 00101110 1100 1100 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Case Insensitive Queries
Mark <[EMAIL PROTECTED]> writes:
> It appears that the behavior of a bpchar compare with a string literal
> is not implicitly trimming the bpchar before the compare, which IMHO is
> incorrect behavior. Is my opinion valid?
regression=# create table foo (f1 char(20));
CREATE
regression=# insert into foo values ('zz');
INSERT 800569 1
regression=# select * from foo;
f1
--
zz
(1 row)
regression=# select * from foo where f1 = 'zz';
f1
--
zz
(1 row)
regression=#
You'll need to be more specific about what you're unhappy about.
> Varchars would incur performance penalties I want to try to avoid if at
> all possible.
You are operating under misinformation about what's efficient or not.
There are no performance penalties that I know of for varchar ... if
anything, bpchar is the less efficient choice, at least in Postgres.
The extra I/O costs for those padding blanks add up, and there's no
compensatory savings anywhere.
In any case, if your data is really variable-length strings, forcing
it into a datatype that doesn't match its semantics because of dubious
micro-efficiency considerations is just plain bad database design.
Rather than having blanks that you want to pretend aren't there, you
should not have the blanks in the first place. IMHO anyway.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] primary key scans in sequence
On Wed, 30 May 2001, Koen Antonissen wrote:
> Now this one doesn't:
> Table "teams"
> Attribute | Type | Modifier
> ---+-+--
> id| integer | not null default nextval('teams_id_seq'::text)
> name | text| not null
> mgr_name | text|
> address | text|
> zipcode | text|
> city | text|
> country | text|
> email | text|
> telnr | text|
> mobnr | text|
> faxnr | text|
> logo | text| not null default 'images/teams/logo_default.gif'
> movie | text|
> url | text|
> qoute | text|
> active| boolean | default 't'::bool
> Indices: index_teams_id, <=!!! 'my own' index
> index_teams_name,
> teams_pkey, <=normal pkey index
> unq_teams_name
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
Looking at that cost, I have to wonder, how many rows are in the table
and has vacuum analyze been run on it? If the number of rows in the
table is small, the seq scan is definately better than having to read
from both the index and heap.
---(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] Case Insensitive Queries
On Wed, 30 May 2001, Tom Lane wrote:
> Mark <[EMAIL PROTECTED]> writes:
> > It appears that the behavior of a bpchar compare with a string literal
> > is not implicitly trimming the bpchar before the compare, which IMHO is
> > incorrect behavior. Is my opinion valid?
>
> regression=# create table foo (f1 char(20));
> CREATE
> regression=# insert into foo values ('zz');
> INSERT 800569 1
> regression=# select * from foo;
> f1
> --
> zz
> (1 row)
>
> regression=# select * from foo where f1 = 'zz';
> f1
> --
> zz
> (1 row)
>
> regression=#
>
> You'll need to be more specific about what you're unhappy about.
Given the thread, I think the problem he's having is tied up in
upper and lower implicitly converting to text.
select * from foo where upper(f1)='ZZ';
gives no rows but if you put 18 spaces after the ZZ you get the
row.
---(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] Case Insensitive Queries
On 30 May 2001 11:16:35 -0700, Stephan Szabo wrote:
> On Wed, 30 May 2001, Tom Lane wrote:
>
> > Mark <[EMAIL PROTECTED]> writes:
> > > It appears that the behavior of a bpchar compare with a string literal
> > > is not implicitly trimming the bpchar before the compare, which IMHO is
> > > incorrect behavior. Is my opinion valid?
> >
> > regression=# create table foo (f1 char(20));
> > CREATE
> > regression=# insert into foo values ('zz');
> > INSERT 800569 1
> > regression=# select * from foo;
> > f1
> > --
> > zz
> > (1 row)
> >
> > regression=# select * from foo where f1 = 'zz';
> > f1
> > --
> > zz
> > (1 row)
> >
> > regression=#
> >
> > You'll need to be more specific about what you're unhappy about.
>
> Given the thread, I think the problem he's having is tied up in
> upper and lower implicitly converting to text.
>
> select * from foo where upper(f1)='ZZ';
> gives no rows but if you put 18 spaces after the ZZ you get the
> row.
>
>
could I cast from text to something else?
---(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] Case Insensitive Queries
On 30 May 2001, Mark wrote:
> On 30 May 2001 11:16:35 -0700, Stephan Szabo wrote:
> > On Wed, 30 May 2001, Tom Lane wrote:
> >
> > > Mark <[EMAIL PROTECTED]> writes:
> > > > It appears that the behavior of a bpchar compare with a string literal
> > > > is not implicitly trimming the bpchar before the compare, which IMHO is
> > > > incorrect behavior. Is my opinion valid?
> > >
> > > regression=# create table foo (f1 char(20));
> > > CREATE
> > > regression=# insert into foo values ('zz');
> > > INSERT 800569 1
> > > regression=# select * from foo;
> > > f1
> > > --
> > > zz
> > > (1 row)
> > >
> > > regression=# select * from foo where f1 = 'zz';
> > > f1
> > > --
> > > zz
> > > (1 row)
> > >
> > > regression=#
> > >
> > > You'll need to be more specific about what you're unhappy about.
> >
> > Given the thread, I think the problem he's having is tied up in
> > upper and lower implicitly converting to text.
> >
> > select * from foo where upper(f1)='ZZ';
> > gives no rows but if you put 18 spaces after the ZZ you get the
> > row.
> >
> >
>
>
> could I cast from text to something else?
You might be able to get away with something like:
create function upper(char) returns char as 'upper'
language 'internal';
It seems to work for me, but I'm not 100% sure how
safe it is.
With the function above,
select * from foo where upper(f1)='ZZ' returns me the zz
row.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Case Insensitive Queries
On 30 May 2001 12:53:22 -0400, Tom Lane wrote: > > You are operating under misinformation about what's efficient or not. > There are no performance penalties that I know of for varchar ... if > anything, bpchar is the less efficient choice, at least in Postgres. > The extra I/O costs for those padding blanks add up, and there's no > compensatory savings anywhere. with varchars, as I understand it (and postgresql may be different), each varchar field has a header that stores the length of the particular entry's length. Further, if the varchar field precedes another field, the system loses the ability to use fixed-length addressing to access the field after the varchar, since the system must determine on a case-by-case basis how to access the field after the varchar. It has to calculate the size of the varchar, add that to the start of the varchar (plus header length), and then it has the address of the next field. With non-variant char it is fixed length, so selects and updates operate much more quickly. Even the postgresql documentation asserts something similar to this: 'Both TEXT and VARCHAR() store only the number of characters in the string. CHAR(length) is similar to VARCHAR(), except it always stores exactly length characters. This type pads the value with trailing spaces to achieve the specified length, and provides slightly faster access than TEXT or VARCHAR().' Perhaps I am misinformed. > > In any case, if your data is really variable-length strings, forcing > it into a datatype that doesn't match its semantics because of dubious > micro-efficiency considerations is just plain bad database design. > Rather than having blanks that you want to pretend aren't there, you > should not have the blanks in the first place. IMHO anyway. > Point well taken. If the gain from using bpchar is not much more than using varchar and the data used is actualy variable length up to a max length, the argument is unfounded. So, what to make of all of this? It depends on the performance gain/loss of using varchar. We originally used fixed-length chars because of the performance gain. We try to avoid varchars for that reason. Now, if postgresql is different, then we'll use varchars, as that precisely models our data. Thanks, Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Unique record Identifier?
Hi all, I'm busy writing an application using PostGreSQL and PHP, so my db reads are 'stateless' and I don't know at record 'write' time which record I have read to begin with. The records I have, have an index, most tables do have a unique index but the index values could get changed during an update. In order to be able to re-read a record, I would like to use some unique identifier of that record, like a unique db-wide rec-id. Does this exist in PostGres and if so, how do I access it? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Case Insensitive Queries
Mark <[EMAIL PROTECTED]> writes: > Even the postgresql documentation asserts something similar to this: You're reading obsolete documentation. There is no such assertion (as far as I can find, anyway) in the 7.1 documentation. The speed advantage of bpchar --- which was always extremely marginal anyway, if it was real at all when you consider I/O costs --- is gone completely now, because with TOAST in the picture the system cannot assume that bpchar is fixed length on disk. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Unique record Identifier?
Chris,
> I'm busy writing an application using PostGreSQL and PHP, so my db
> reads are
> 'stateless' and I don't know at record 'write' time which record I
> have read
> to begin with. The records I have, have an index, most tables do have
> a
> unique index but the index values could get changed during an update.
> In
> order to be able to re-read a record, I would like to use some unique
> identifier of that record, like a unique db-wide rec-id. Does this
> exist in
> PostGres and if so, how do I access it?
One does -- the OID. However, for various reasons (already discussed ad
naseum on this list) you don't want to use the OID as your client side
unique index. Instead, you'll want to "roll your own" global unique id:
1. Create a sequence ('gui_sq'). Set it to start at a number that will
allow you to backfill exisiting records.
2. Add a column of type INT4 to each table ('gsq')
3. Create the default value of this column as NEXTVAL('gui_sq').
4. Backfill existing records.
5. Add a unique index on each table for this column.
You now have a unique identifier that is unique not only for each table
but between tables.
-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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Using indexes
Hi, Indexes are used to find a record faster in a table. It only seems to work if I am working with one table. eg) > create index customer_id_idx on customer(id); > select * from customer where id=1; -- This uses the index I create However, when I start to join 2 or more tables together it doesn't seem to use it.. WHY??? eg) > create index customer_id_idx on customer(id); > select * from customer, purchaseorder where customer.id=purchaseorder.id; -- This results in a seq scan .. WHY?? Is there a way to make it so that it uses my index Thanks again. ---(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] Unique record Identifier?
on 05/30/2001 16:33, Josh Berkus at [EMAIL PROTECTED] wrote: > Chris, > Thanks Josh - that was my next step. At the moment, the database is not in production, I'm running a Progress database there, but I'm trying to learn the dos and don'ts in PG. I don't really need to backfill the records, I can just drop the table and re-create it (On my Mac PowerBook it loads 1.2 million records in about 5 minutes without any index on the table). I'm sure, I will have many more questions in the future ... Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Too many rows returning
Hi, Does the number of rows affect how long it takes to execute a query. I have 3 UNIONS. One table has 97 rows, another has 7375 rows, and 1558; In each union there are at least one LEFT OUTER JOIN and each subselect has at least 2 JOINed tables. Something like this: select ..,..,,...,. from (subselect. ... ) as T1 LEFT JOIN (subselect ... ) as T2 on T1.field1=T2.field2 UNION select ..,..,,...,. from ((subselect. ...) as T1 LEFT JOIN (subselect ) as T2 on T1.field1=T2.field1) as T3 LEFT JOIN (subselect ... ) as T2 on T1.field1=T2.field2 UNION select ..,..,,...,. from (subselect. ... ) as T1 LEFT JOIN (subselect ... ) as T2 on T1.field1=T2.field2 When I do my sql stmt it takes more than 10minutes to return and sometimes it display the maximum 30 sec has exceed... Can anyone provide any suggestion in how I can speed up the execution and possibly return something to me without the error message. I have tried index but it doesn't seem to have an affect.. because I did the explain it still using seq scan.. (re: last post "using indexes") Thanks again Linh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
