[SQL] Uppercase and Lowercase
Hello In my PostgreSQL database i have records inserted in Uppercase. For example: VIA SENATO What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work. When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work.. Anyone got any idea?? Thanks Shavonne Wijesinghe
Re: [SQL] Uppercase and Lowercase
Replace && by || >>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-02-19 16:22 >>> Hello In my PostgreSQL database i have records inserted in Uppercase. For example: VIA SENATO What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work. When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work.. Anyone got any idea?? Thanks Shavonne Wijesinghe
Re: [SQL] Uppercase and Lowercase
On Mon, Feb 19, 2007 at 04:22:06PM +0100, Shavonne Marietta Wijesinghe wrote: > Hello > > In my PostgreSQL database i have records inserted in Uppercase. > For example: VIA SENATO > > What i want is to change them to "Via Senato". Have a look at the initcap() function. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Uppercase and Lowercase
Hey thanks Bart. it worked ;) but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato" Anyoneee?? Shavonne Wijesinghe - Original Message - From: Bart Degryse To: pgsql-sql@postgresql.org Sent: Monday, February 19, 2007 4:25 PM Subject: Re: [SQL] Uppercase and Lowercase Replace && by || >>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-02-19 16:22 >>> Hello In my PostgreSQL database i have records inserted in Uppercase. For example: VIA SENATO What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work. When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work.. Anyone got any idea?? Thanks Shavonne Wijesinghe
Re: [SQL] Uppercase and Lowercase
Of course it does. That's what you ask your query to do. You only used the wrong operator for string concatenation, which is why it didn't seem to work. But your query will not do this for each word in a field. For that you need a function or regular expressions. I suggest you try select initcap(pa_indirizzo) from .. Please always reply to pgsql-sql@postgresql.org and not to the senders email address. >>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-02-19 16:39 >>> Hey thanks Bart. it worked ;) but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato" Anyoneee?? Shavonne Wijesinghe - Original Message - From: Bart Degryse ( mailto:[EMAIL PROTECTED] ) To: pgsql-sql@postgresql.org Sent: Monday, February 19, 2007 4:25 PM Subject: Re: [SQL] Uppercase and Lowercase Replace && by || >>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-02-19 16:22 >>> Hello In my PostgreSQL database i have records inserted in Uppercase. For example: VIA SENATO What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work. When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work.. Anyone got any idea?? Thanks Shavonne Wijesinghe
Re: [SQL] Uppercase and Lowercase
Shavonne Marietta Wijesinghe wrote: > Hey thanks Bart. it worked ;) > > but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i > need is "Via Senato" > > Anyoneee?? initcap() does what you want. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Uppercase and Lowercase
Robert is right of course. You can eg use a trigger to do that... I haven't tested, but I guess something like this would do what you want whenever you insert records in your table CREATE OR REPLACE FUNCTION "public"."func_change_case" () RETURNS trigger AS $body$ BEGIN NEW.pa_indirizzo := initcap(NEW.pa_indirizzo); RETURN NEW; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER "TRIG_yourtablename" BEFORE INSERT ON "public"."yourtablename" FOR EACH ROW EXECUTE PROCEDURE "public"."func_change_case"(); >>> "Hiltibidal, Robert" <[EMAIL PROTECTED]> 2007-02-19 16:55 >>> Exactly. Since its only a select group of words this query applies to its better to correct for this before the information goes into the database. From:[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bart Degryse Sent: Monday, February 19, 2007 9:49 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Uppercase and Lowercase Of course it does. That's what you ask your query to do. You only used the wrong operator for string concatenation, which is why it didn't seem to work. But your query will not do this for each word in a field. For that you need a function or regular expressions. I suggest you try select initcap(pa_indirizzo) from .. Please always reply to pgsql-sql@postgresql.org and not to the senders email address. >>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-02-19 16:39 >>> Hey thanks Bart. it worked ;) but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato" Anyoneee?? Shavonne Wijesinghe - Original Message - From:Bart Degryse ( mailto:[EMAIL PROTECTED] ) To:pgsql-sql@postgresql.org Sent:Monday, February 19, 2007 4:25 PM Subject:Re: [SQL] Uppercase and Lowercase Replace && by || >>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-02-19 16:22 >>> Hello In my PostgreSQL database i have records inserted in Uppercase. For example: VIA SENATO What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work. When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work.. Anyone got any idea?? Thanks Shavonne Wijesinghe PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:[EMAIL PROTECTED] Thank you.
[SQL] cartesian product
Hi,
I have a query regarding an SQL statement I'm trying to execute. I
have the following table:
sequence
-+
AK
AKCMK
CMKA
I execute the following statement (Cartesian product):
SELECT p1.sequence as sequence1, p2.sequence as sequence2
FROM potential_pairs p1, potential_pairs p2
which gives me:
sequence1 | sequence2
+--
AK | AK
AK | AKCMK
AK | CMKA
AKCMK| AK
AKCMK| AKCMK
AKCMK| CMKA
CMKA | AK
CMKA | AKCMK
CMKA | CMKA
(9 rows)
I want to eliminate duplicates and by duplicate I mean a tuple such as
{AK, CMKA} should be regarded as the same as {CMKA, AK}. So I would
like the following result:
sequence1 | sequence2
+--
AK | AK
AK | AKCMK
AK | CMKA
AKCMK| AKCMK
AKCMK| CMKA
CMKA | CMKA
Any help would be appreciated.
- Salman
---(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] cartesian product
On Mon, 2007-02-19 at 10:58, Salman Tahir wrote:
> Hi,
>
> I have a query regarding an SQL statement I'm trying to execute. I
> have the following table:
>
> sequence
> -+
> AK
> AKCMK
> CMKA
>
> I execute the following statement (Cartesian product):
>
> SELECT p1.sequence as sequence1, p2.sequence as sequence2
> FROM potential_pairs p1, potential_pairs p2
>
> which gives me:
>
> sequence1 | sequence2
> +--
> AK | AK
> AK | AKCMK
> AK | CMKA
> AKCMK| AK
> AKCMK| AKCMK
> AKCMK| CMKA
> CMKA | AK
> CMKA | AKCMK
> CMKA | CMKA
> (9 rows)
>
> I want to eliminate duplicates and by duplicate I mean a tuple such as
> {AK, CMKA} should be regarded as the same as {CMKA, AK}. So I would
> like the following result:
>
> sequence1 | sequence2
> +--
> AK | AK
> AK | AKCMK
> AK | CMKA
> AKCMK| AKCMK
> AKCMK| CMKA
> CMKA | CMKA
>
SELECT p1.sequence as sequence1, p2.sequence as sequence2 FROM
potential_pairs p1, join potential_pairs p2 on
(p1.sequence<>p2.sequence) where sequence1 > sequence2
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] cartesian product
On Mon, 2007-02-19 at 10:58, Salman Tahir wrote:
> Hi,
>
> I have a query regarding an SQL statement I'm trying to execute. I
> have the following table:
>
> sequence
> -+
> AK
> AKCMK
> CMKA
>
> I execute the following statement (Cartesian product):
>
> SELECT p1.sequence as sequence1, p2.sequence as sequence2
> FROM potential_pairs p1, potential_pairs p2
>
> which gives me:
>
> sequence1 | sequence2
> +--
> AK | AK
> AK | AKCMK
> AK | CMKA
> AKCMK| AK
> AKCMK| AKCMK
> AKCMK| CMKA
> CMKA | AK
> CMKA | AKCMK
> CMKA | CMKA
> (9 rows)
>
> I want to eliminate duplicates and by duplicate I mean a tuple such as
> {AK, CMKA} should be regarded as the same as {CMKA, AK}. So I would
> like the following result:
>
> sequence1 | sequence2
> +--
> AK | AK
> AK | AKCMK
> AK | CMKA
> AKCMK| AKCMK
> AKCMK| CMKA
> CMKA | CMKA
Oh wait, slightly different thing you meant. OK, you'll want something
like:
select p1.sequence as sequence1, p2.sequence as sequence2 from
potential_pairs p1, potential_pairs p2 where p1.sequence >= p2.sequence
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] cartesian product
Hi, Salman
Maybe this isn't so much elegant, but works:
SELECT p1.sequence as sequence1, p2.sequence as sequence2
FROM potential_pairs p1, potential_pairs p2
WHERE p1.sequence <= p2.sequence
Hope this helps
Salman Tahir wrote:
Hi,
I have a query regarding an SQL statement I'm trying to execute. I
have the following table:
sequence
-+
AK
AKCMK
CMKA
I execute the following statement (Cartesian product):
SELECT p1.sequence as sequence1, p2.sequence as sequence2
FROM potential_pairs p1, potential_pairs p2
which gives me:
sequence1 | sequence2
+--
AK | AK
AK | AKCMK
AK | CMKA
AKCMK| AK
AKCMK| AKCMK
AKCMK| CMKA
CMKA | AK
CMKA | AKCMK
CMKA | CMKA
(9 rows)
I want to eliminate duplicates and by duplicate I mean a tuple such as
{AK, CMKA} should be regarded as the same as {CMKA, AK}. So I would
like the following result:
sequence1 | sequence2
+--
AK | AK
AK | AKCMK
AK | CMKA
AKCMK| AKCMK
AKCMK| CMKA
CMKA | CMKA
Any help would be appreciated.
- Salman
---(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
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
---(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
[SQL] DISTINCT ON not working...?
Hi all, Strange one - I have a nightly export / import routine that exports from one database and imports to another. Has been working fine for several months, but last night it died on a unique constraint. To cut out all the details, the code that is causing the problem: SELECT DISTINCT ON (ean) code, CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN null ELSE ean END AS ean FROMTMPTABLE WHERE code NOT IN (SELECT code FROM stock_deleted) ANDean IS NOT NULL That is the code that generates the error on the unique constraint against the ean column. If I play with that and run this: SELECT DISTINCT ON (ean) CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN null ELSE ean END AS ean, count(*) FROMTMPTABLE WHERE code NOT IN (SELECT code FROM stock_deleted) ANDean IS NOT NULL GROUP BY ean I get a several thousand rows returned, all with a count(*) of 1, except one row: 3246576919422 2 DISTINCT ON should eliminate one of those rows that is making that 2 - as I said, it's been working fine for several months, and it is still doing it correctly for approximately 100 other rows that have duplicate ean codes. Can anyone give me a hand to work out why this one is doubling up?! Cheers, ~p ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] DISTINCT ON not working...?
"Phillip Smith" <[EMAIL PROTECTED]> writes: > To cut out all the details, the code that is causing the problem: > SELECT DISTINCT ON (ean) > code, > CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN > null ELSE ean END AS ean > FROMTMPTABLE > WHERE code NOT IN (SELECT code FROM stock_deleted) > ANDean IS NOT NULL Perhaps you've confused yourself by using "ean" as both an input and an output column name? I think that the "ean" in the DISTINCT ON clause will effectively refer to that CASE-expression, whereas the one in the WHERE clause is just referring to the underlying column (and thus making the IS NULL test in the CASE rather pointless). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] DISTINCT ON not working...?
Removing the CASE statement all together: SELECT DISTINCT ON (ean) ean, count(*) FROMTMPTABLE WHERE code NOT IN (SELECT code FROM stock_deleted) ANDean IS NOT NULL GROUP BY ean Still gives me: 3246576919422 2 -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 February 2007 15:33 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] DISTINCT ON not working...? Perhaps you've confused yourself by using "ean" as both an input and an output column name? I think that the "ean" in the DISTINCT ON clause will effectively refer to that CASE-expression, whereas the one in the WHERE clause is just referring to the underlying column (and thus making the IS NULL test in the CASE rather pointless). regards, tom lane ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] can someone explain confusing array indexing nomenclature
Στις Παρασκευή 16 Φεβρουάριος 2007 20:35, ο/η chrisj έγραψε:
> I am quite sure the [2] is not discarded, easy enough to test but I don't
> have access to PG at the moment.
Well it should, since
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
text
-
{{meeting}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
text
---
{{meeting,lunch}}
(1 row)
dynacom=#
>
> Achilleas Mantzios wrote:
> > Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
> >> Thanks Achilleas,
> >>
> >> I see what you are saying, but if we consider just the index "[2]" for a
> >> moment,
> >> it means something different depending upon the context (in one case it
> >> means "2" and in the other case it means "1:2") and the context is
> >> determined by the format of indexes on other dimensions.
> >>
> >> I believe I understandbut incredibly confusing.
> >
> > Now that i think about it again, i speculate that the [2] is discarded.
> >
> >> - chris
> >>
> >> Achilleas Mantzios wrote:
> >> > Ξ£ΟΞΉΟ Ξ�Ξ΅ΟΞ¬ΟΟΞ· 14 ΦΡβΟΞΏΟ
Ξ¬ΟΞΉΞΏΟ 2007 21:31, ΞΏ/Ξ·
> >>
> >> chrisj
> >
> > ΞΞ³ΟΞ±ΟΞ΅:
> >> >> given the following table:
> >> >>
> >> >> protocal2=> select * from sal_emp ;
> >> >> name | pay_by_quarter | schedule
> >>
> >> ---+---+
> >>
> >> >>--- Bill | {1,1,1,1} |
> >> >> {{meeting,lunch},{training,presentation}}
> >> >> Carol | {2,25000,25000,25000} |
> >> >> {{breakfast,consulting},{meeting,lunch}}
> >> >> (2 rows)
> >> >>
> >> >> why do the following two queries yield different results??
> >> >>
> >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> >> >> schedule
> >> >> --
> >> >> lunch
> >> >> (1 row)
> >> >>
> >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> >> >> schedule
> >> >> ---
> >> >> {{meeting,lunch}}
> >> >> (1 row)
> >> >
> >> > The [n:m] notation denotes a slice of the array (not element).
> >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> >> > while schedule[1:1][2] could mean
> >> > the second row of the subarray schedule[1:1][1:2].
> >> > So these two are foundamentally different things.
> >> > In my 7.4 even if you gave
> >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> >> > you would still get {{meeting,lunch}} as a result.
> >> > (Right or wrong is another story).
> >> > Anyway the first time you query for a "text",
> >> > the second time you query for a "text[]", so you should expect
> >> > different results.
> >> > --
> >> > Achilleas Mantzios
> >> >
> >> > ---(end of
> >>
> >> broadcast)---
> >>
> >> > TIP 3: Have you checked our extensive FAQ?
> >> >
> >> >http://www.postgresql.org/docs/faq
> >
> > --
> > Achilleas Mantzios
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
