current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to sort by converted column, but psql returns error:

SELECT * FROM table ORDER BY CONVERT(col1 USING utf8_to_iso_8859_2);

ERROR: syntax error at or near "USING" LINE 1: SELECT * FROM table ORDER BY CONVERT(col1 USING utf8... ^

*** Error ***

ERROR: syntax error at or near "USING" SQL state: 42601

I saw this function used liked this before and it's in documentation too, so I don't understand why I'm getting this error. Anything I missed?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

It looks like you are mixing two features. One is collation support, that would make sense in an ORDER BY clause:

SELECT * FROM table ORDER BY col1 COLLATE "fr_FR";

The other one is the convert() function that transcodes a string from a source encoding to a defined destination encoding:

SELECT convert('my_string', 'UTF8', 'ISO_8859_2')

But that has no effect on the sort order - except with locale C or a bytea column.

share|improve this answer
    
Thank you, ORDER BY COLLATE was exactly what I needed. I had to upgrade psql from 8.4 to 9.1, but fortunately it was quite easy. –  GvS Feb 3 '12 at 0:16
    
@GvS: Cool! And v9.1 won't hurt, either. Lots of shiny new stuff. :) –  Erwin Brandstetter Feb 3 '12 at 0:28
up vote 2 down vote

You probably mean to use:

CONVERT(col1, 'iso-8859-2')

to convert col1 (text) to iso-8859-2 (bytea), assuming your database is stored as utf-8. convert, convert_to and convert_from are showin in table 9-6 here: http://www.postgresql.org/docs/9.1/static/functions-string.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.