Re: [SQL] [SPAM]-D] How to find broken UTF-8 characters ?
On 2010-04-29, 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«
use a regular expression.
ISO8859-1 is easy, all the caracters a grouped together in unicode so
the regular expression consists of a single inverted range class
SELECT pkey FROM tabname
WHERE ( textfield || textfiled2 || textfield3 ) ~
('[^'||chr(1)||'-'||chr(255)||']');
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [SPAM]-D] How to find broken UTF-8 characters ?
On 2010-04-29, Justin Graf wrote: > I'm pretty sure this is the regualr expression to find all non ASCII=20 > chars.. [^\x00-\xFF] Not in postgres. \x00 does not work well in strings, and \xFF is invalid utf-8. this is why I used char() (also ASCII is undefined past at \x7F ... but the original request was for LATIN-1 which does end at char(255)) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] LIMIT problem
suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT problem
On 04/30/2010 06:20 PM, silly sad wrote: suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick? It will execute foo only once, and give only one row out of the n rows it returns. If I'm wrong please correct me. -- Nilesh Govindarajan Site & Server Administrator www.itech7.com मेरा भारत महान ! मम भारत: महत्तम भवतु ! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT problem
On 2010-04-30, silly sad wrote: > suppose i request > > SELECT foo(t.x) FROM t LIMIT 1; > > Whither it DEFINED how many times foo() will be executed? foo will be executed repeatedly until it returns a result or all the rows in t are exhausted. > May anyone rely on it? not sure > Or we have to avoid this non SQLish trick? This will execute it once (or not at all where t has no rows) SELECT foo(x) FROM (SELECT x FROM t LIMIT 1) as bar; But may return a number of records differing from 1 in the case where foo is a set-returning function. jasen=# select a from foo; a --- 1 4 7 6 3 6 rows) jasen=# select generate_series(1,a),a from foo limit 1; generate_series | a -+--- 1 | 1 (1 row) the first row jas 1 and the first row from generate_series(1,1) is returned jasen=# select generate_series(5,a),a from foo limit 1; generate_series | a -+--- 5 | 7 (1 row) the 1st row has 1 and generate_series(5,1) returns 0 rows the 2nd row has 4 and generate_series(5,4) returns 0 rows the 3rd row has 7 and generate_series(5,7) returns 3 rows And the first of those is returned. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
