[SQL] lower() not working correctly...?
I have the following query: select lower(firstname) || ' ' || lower(lastname) from person firstname and lastname are VARCHAR lower() returns NULL when firstname OR lastname is NULL, is this correct? This is 8.2devel from 24.08.2006. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 1: 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] lower() not working correctly...?
am Fri, dem 15.09.2006, um 10:06:40 +0200 mailte Andreas Joseph Krogh folgendes: > I have the following query: > > select lower(firstname) || ' ' || lower(lastname) from person > > firstname and lastname are VARCHAR > > lower() returns NULL when firstname OR lastname is NULL, is this correct? Yes. You can user coalesce for this: select coalesce(lower(firstname),'') || ... HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] lower() not working correctly...?
Andreas Joseph Krogh a ecrit le 15/09/2006 10:06: I have the following query: select lower(firstname) || ' ' || lower(lastname) from person firstname and lastname are VARCHAR lower() returns NULL when firstname OR lastname is NULL, is this correct? This is 8.2devel from 24.08.2006. NULL is an unkown value. lower of an unkown value is unknown... thus NULL. This is correct behavior. -- Guillaume. ---(end of broadcast)--- TIP 1: 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] lower() not working correctly...?
unsubscribe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sorting items in aggregate function (thanks)
On Tue, Sep 12, 2006 at 04:37:55PM -0400, Tom Lane wrote: > Note that if you need to GROUP in the outer query, it's best to sort the > inner query's output first by the outer query's grouping: ... Great - this works fine. Thanks also to the other people who replied. Steven. -- w: http://www.cl.cam.ac.uk/users/sjm217/ pgpWJlR3KcKXf.pgp Description: PGP signature
[SQL] Omission of 'FROM' ? diff between pgsql 7.4.1 and pgsql 8.1.4
hi I am upgrading from pgsql 7.4.1 to pgsql 8.1.4. However, some of the application queries won't run on the newer version of pgsql. An example of the type of query is: SELECT protein_external_links.protein_id, external_links.external_id, proteins.public_name WHERE protein_external_links.external_link_id = external_links.id and protein_external_links.protein_id = proteins.id and external_links.external_database_id != 8 and external_links.external_id_type_id = 2; on pgsql7.4.1 what tables need to be queried seems to be implicitly deduced from the rest of the SQL without the need for a FROM clause. However, on pgsql 8.1.4 the following error is given: ERROR: missing FROM-clause entry for table "protein_external_links" Is there some query optimization config setting I can set in pgsql 8.1.4 to fix this problem rather than having to change the queries in the code? I've read over the changes between pgsql 7 and pgsql 8 but nothing seems to stand out to me as referencing this problem. Anyone with more knowledge that can point me in the right direction? Thanks Kate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Omission of 'FROM' ? diff between pgsql 7.4.1 and pgsql 8.1.4
[EMAIL PROTECTED] написа: [...] > Is there some query optimization config setting I can set in pgsql 8.1.4 to > fix > this problem rather than having to change the queries in the code? I've read > over the changes between pgsql 7 and pgsql 8 but nothing seems to stand out to > me as referencing this problem. Anyone with more knowledge that can point me > in > the right direction? Look here for "add_missing_from" - http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION -- Milen A. Radev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Omission of 'FROM' ? diff between pgsql 7.4.1 and pgsql 8.1.4
am Fri, dem 15.09.2006, um 11:16:06 +0200 mailte [EMAIL PROTECTED] folgendes: > Is there some query optimization config setting I can set in pgsql 8.1.4 to > fix Yes. 'add_missing_from = true' in the postgresql.conf http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html But, you should rewrite your SQL... HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: 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] [HACKERS] lower() not working correctly...?
On fös, 2006-09-15 at 09:52 +0200, Andreas Joseph Krogh wrote: > I have the following query: > > select lower(firstname) || ' ' || lower(lastname) from person > > firstname and lastname are VARCHAR > > lower() returns NULL when firstname OR lastname is NULL, is this correct? In fact, your problem has nothing to do with lower(). You probably did not expect the || operator to return NULL when any of the operands is NULL as seen in select firstname || ' ' || lastname from person so , as someone else has already mentioned, you should use coalesce. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Aggregates with internal state type?
Hi, Tom, Markus Schaber wrote: >>> This lead me to the question whether it is possible to use "internal" as >>> state type for an Aggregate whose functions are implemented in C. >> No, because the system has no idea what the representation of an >> "internal" state value might be, and in particular how to copy it. >> The same goes for other pseudotypes. > Ah, I see. So there's no possibility to pass some void* kind of > intermediate data, I have to craft at least a dummy PostgreSQL datatype > for it. > This also solves the question how such things would be cleaned up in > case of an intermediate error. As we are at it: How would you estimate the chances for a Patch to get included that lets C functions pass some void* data pointer around in an aggregate, when the aggregate provides a custom "clean-up" function that gets called whenever the scan gets aborted prematurely? I assume that this could help speeding up e. G. Array Aggregates, or PostGIS geomUnion() or string concatenations etc. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
