Re: [SQL] problem converting strings to timestamps with time zone

2010-04-29 Thread Edward Ross 

Tom Lane wrote:

Edward Ross  writes:

Here is a sandbox example of what I mean.



CREATE TABLE test_0
(
string_value varchar(2047),
timestamp_value timestamp with time zone
);



insert into test_0
   (string_value)
values
   ('2010-03-12 17:06:21-0800'),
   ('2009-08-14 16:47:40+0500'),
   ('2010-03-22 22:45:59-0400');



As expected, select * from test_0; , produces the following:



string_valuetimestamp_value
2010-03-12 17:06:21-0800
2009-08-14 16:47:40+0500
2010-03-22 22:45:59-0400



I would like to parse the strings into their equivalent timestamps
and put them in the timestamp_value column.



My attempt, so far, to update the table:



update value
set
timestamp_value =
to_timestamp(string_value, '-MM-DD HH24:MI:SS-');


Forget to_timestamp; just cast the string to timestamptz.  The
regular timestamp input converter will handle that format fine.

regards, tom lane



That worked great.

Thanks very much,

Edward Ross

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?

2010-04-29 Thread Justin Graf 
On 4/28/2010 10:34 PM, Andreas wrote:
> Hi,
>
> while writing the reply below I found it sounds like beeing OT but 
> it's actually not.
> I just need a way to check if a collumn contains values that CAN NOT 
> be converted from Utf8 to Latin1.
> I tried:
> Select convert_to (my_column::text, 'LATIN1') from my_table;
>
> It raises an error that says translated:
> ERROR:  character 0xe28093 in encoding »UTF8« has no equivalent in 
> »LATIN1«
>
> I'd like to select all those records.
> When I know which record has faulty content I can correct it.
>
> If this is really OT on the SQL list then please tell me where to ask.

That's easy enough  you need to write an Update statement using regular 
expression to replace  all non legal Latin/ASCII char
http://www.postgresql.org/docs/8.4/interactive/functions-string.html 


the command is regexp_replace('MyBadString', 
'SearchForallNoneAsccIIChars', 'ReplaceWithBlankString ')

I'm pretty sure this is the regualr expression to find all non ASCII 
chars.. [^\x00-\xFF]

To test is try to  Select regexp_replace( MyColumn, '[^\x00-\xFF]', ' ') 
from screweduptable

If the regular expression does not work, I'm dry well, when it comes to 
regular expressions.  Dd i say i hate regular expression.  It dam near 
impossible to write.
Once you get the expression right and working

the Update is straight forward.
Update mytable set mybadcolumn = regexp_replace( mybadcolumn, 
'[^\x00-\xFF]', ' ')

>> Select covert('MyUtf8', 'UTF8', 'LATIN')
>> or
>> Select covert_to('MyUtf8',  'LATIN')
>
> I found them before but didn't understand their output.
> e.g.
> Select convert('1aäßx', 'utf8', 'LATIN1') ;
> Result = "1a\344\337x"
> so it translated  ä = 344  and  ß = 337. The other 3 are just as they 
> were before.
> How can this be valid in a single byte charset like Latin1?
> Especially as ä, ß are E4 and DF.
> Why do they come out as escaped codes when they are in Latin1 aswell 
> as 1, a and x?

Someone with more knowledge how convert()  works is going to have to 
explain why they have been escaped. PgAdmin may have escaped them.  But 
those characters are valid Latin1 characters

http://en.wikipedia.org/wiki/%C3%84
http://en.wikipedia.org/wiki/%C3%9F
ß = latin Beta

It seems Access and Excel are putting in extra bits of data into the 
field.  In the past i had to change inserts/updates from Access so it 
would send data in a specific char encoding.  I had problems where 
Access was using a Windows Encoding, the ODBC converted it to Latin1, 
and the MsSQL Database put it in UTF8.  It was no fun cleaning it up.

>
>> What ever pg client library used to move Excel data to PG my have 
>> incorrectly converted some of the data or moved formatting 
>> information into the database.  I have seen Access and Excel do 
>> mightily odd things when connecting to DB's  I don't know about 
>> current versions but 2000 and 2003 Excels did really stupid things 
>> when trying to write to DB's including MSSQL.
>
> Cute ... we use Access 2000 and 2003   :(

Been more Screwed by Excel and Access flakiness, and Access Programmers 
thinking they are DBA's.





All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

[SQL] Tsearch not searching 'Y'

2010-04-29 Thread sandeep prakash dhumale 
Hello All,

I am trying to get tsearch working for my application but I am facing a
problem when alphabet 'Y' is the in the tsquery.

can anyone please share some light on it.


# SELECT 'hollywood'::tsvector  @@ to_tsquery('holly:*');
 ?column?
--
 f
(1 row)

SELECT 'hollywood'::tsvector  @@ to_tsquery('holl:*');
?column?
--
 t
(1 row)


It works when i put <> in y as below but i don't want to do it that way.

SELECT 'hollywood'::tsvector  @@ to_tsquery('holl:*');
 ?column?
--
 t

Thanks in advance

Re: [SQL] [GENERAL] Tsearch not searching 'Y'

2010-04-29 Thread Tom Lane 
"sandeep prakash dhumale"  writes:
> I am trying to get tsearch working for my application but I am facing a
> problem when alphabet 'Y' is the in the tsquery.

> # SELECT 'hollywood'::tsvector  @@ to_tsquery('holly:*');
>  ?column?
> --
>  f
> (1 row)

You can't use to_tsquery for this sort of thing, because it tries to
normalize the given words:

regression=# select to_tsquery('holly:*');
 to_tsquery 

 'holli':*
(1 row)

If you do this it works:

regression=# SELECT 'hollywood'::tsvector  @@ 'holly:*'::tsquery;
 ?column? 
--
 t
(1 row)

So if you want to use prefix matching, don't normalize.

regards, tom lane

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Re: Re: [GENERAL] [SQL] Tsearch not searching 'Y'

2010-04-29 Thread sandeep prakash dhumale 
 

On Thu, 29 Apr 2010 19:27:33 +0530  wrote
>On Thu, Apr 29, 2010 at 01:13:40PM -, sandeep prakash dhumale wrote:
>> Hello All,
>> 
>> I am trying to get tsearch working for my application but I am facing a
>> problem when alphabet 'Y' is the in the tsquery.
>> 
>> can anyone please share some light on it.
>> 
>> 
>> # SELECT 'hollywood'::tsvector @@ to_tsquery('holly:*');
>> ?column?
>> --
>> f
>> (1 row)
>> 
>> SELECT 'hollywood'::tsvector @@ to_tsquery('holl:*');
>> ?column?
>> --
>> t
>> (1 row)
>> 
>> 
>> It works when i put <> in y as below but i don't want to do it 
that way.
>> 
>> SELECT 'hollywood'::tsvector @@ to_tsquery('holl:*');
>> ?column?
>> --
>> t
>> 
>> Thanks in advance 
>
>That is because the to_tsquery() normalizes the tokens. Here is
>what I get from the default configuration:
>
>db=# select to_tsquery('holly:*');
> to_tsquery 
>
> 'holli':*
>(1 row)
>
>db=# select to_tsquery('holl:*');
> to_tsquery 
>
> 'holl':*
>(1 row)
>
>It is pretty easy to see why you see the behavior that you do.
>Maybe you need to change your tsearch configuration to match what
>you expect to happen.
>
>Regards,
>Ken
>
>
>-- 
>Sent via pgsql-general mailing list ([email protected])
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
>



First of all thanks for your replies Tom and Ken,

I am little newbie to Tsearch so I appologies if I sound a little confuse.

Tom: If i do by casting like you wrote then i ran into case sensitivity issue 
also then it does not work for other searches I guess then it sees for exact 
matches and not normalize to lexims.

Ken: As you said I need to change my configuration, It would be great if you 
can point me out where i can change that configuration 

and what about that   in the query how does it work, does that 
mean to explicitly include y in to_tsquery.


All your help is higly appriciated.


--Sandy

Re: [SQL] problem converting strings to timestamps with time zone

2010-04-29 Thread DM 
Could you please post the exact string how you did?

Thanks
Deepak

On Thu, Apr 29, 2010 at 12:59 AM, Edward Ross  wrote:

> Tom Lane wrote:
>
>> Edward Ross  writes:
>>
>>> Here is a sandbox example of what I mean.
>>>
>>
>>  CREATE TABLE test_0
>>> (
>>>string_value varchar(2047),
>>>timestamp_value timestamp with time zone
>>> );
>>>
>>
>>  insert into test_0
>>>   (string_value)
>>>values
>>>   ('2010-03-12 17:06:21-0800'),
>>>   ('2009-08-14 16:47:40+0500'),
>>>   ('2010-03-22 22:45:59-0400');
>>>
>>
>>  As expected, select * from test_0; , produces the following:
>>>
>>
>>  string_valuetimestamp_value
>>> 2010-03-12 17:06:21-0800
>>> 2009-08-14 16:47:40+0500
>>> 2010-03-22 22:45:59-0400
>>>
>>
>>  I would like to parse the strings into their equivalent timestamps
>>> and put them in the timestamp_value column.
>>>
>>
>>  My attempt, so far, to update the table:
>>>
>>
>>  update value
>>>set
>>>timestamp_value =
>>>to_timestamp(string_value, '-MM-DD HH24:MI:SS-');
>>>
>>
>> Forget to_timestamp; just cast the string to timestamptz.  The
>> regular timestamp input converter will handle that format fine.
>>
>>regards, tom lane
>>
>>
> That worked great.
>
> Thanks very much,
>
> Edward Ross
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>