[GENERAL] Encoding question when dumping/restoring databases for upgrade
Hello, I am sitting on version 7.4.x and am going to upgrade to version 8.3.x. From all I can read I should have no problem with actual format of the pgdump file (for actual dumping and restoring purposes) but I am having problems with encoding (which I was fairly sure I would). I have searched the web for solutions and one solution given (in one thread where Tom Lane answered) was to set the correct encoding in the version 8.3.x database. However, the default encoding in the version 8.3.x instance is currently UTF8 and I am happy with that (in fact, I would even want it to be UNICODE). The encoding for most of the databases in the version 7.4.x was LATIN1. Is there any way I can ignore the LATIN1 encoding and force the database to accept the UTF8 encoding of the new version 8.3.x instance? I get the below message when I try the psql -f command. psql:aranzo20090812:30: ERROR: encoding LATIN1 does not match server's locale en_US.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. Any help would be appreciated. Archie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] String handling function, substring vs. substr
Hi all, I want to call one of the two functions above many times (in an aggregate function) and it says in the manual pages that substr is the same as substring. Does this mean that substr calls substring internally?? Or is it the other way around?? Or are they independent of each other?? So in short, which is faster to use?? Thanks, Archie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] String handling function, substring vs. substr
Hi all, brian <[EMAIL PROTECTED]> writes: [EMAIL PROTECTED] wrote: Does this mean that substr calls substring internally?? Or is it the other way around?? Or are they independent of each other?? Looks like they're pretty evenly matched. Actually, a bit of poking into the contents of pg_proc will show you that they are both aliases for the same C function (text_substr_no_len). So they should be *exactly* the same speed. regards, tom lane Thanks Brian for your answer (although that wasn't what I was looking for, I was looking for Tom's answer). Thanks Tom for your answer. Archie ---(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
[GENERAL] Need help with a function from hell..
Hi all, I have a small coding problem where my function is becoming, well, too ugly for comfort. I haven't finished it but you will get picture below. First a small description of the purpose. I have an aggregate function that takes a string and simply concatenates that string to the previous (internal state) value of the aggregate, example: "Hello:World" || ", " || "World:Hello" --> "Hello:World, World:Hello" My problem is that I sometimes get the same value before the colon sign and in those cases I should not add the whole string to the previous value of the aggregate but extract the value that is behind the colon and add it to already existing part which matched the value before the colon but with a slash as a delimiter, example: Internal state: "Hello:World, World:Hello" New value: "Hello:Dolly" After function is run: "Hello:World/Dolly, World:Hello" So what I am doing is a lot of strpos() and substr() functions (I have previously asked for the speed of the substr() function) but it is beginning to look really alwful. It seems very odd that there doesn't exist something else like what I need but I haven't found anything, although I admit I might not understand all aspects of the PostGreSQL database and what I can do with the SQL in connection to it. Below you will find my unfinished function, but it will show you what I mean when I say ugly.. Any help is appreciated. Thanks in advance, Archie CREATE FUNCTION rarity_concat(text, text) RETURNS text AS 'DECLARE colon_pos integer; set_str text; rarity_str text; set_exist_pos integer; rarity_exist_str_middle text; rarity_exist_str_end text; BEGIN colon_pos := strpos($2, ':'); set_str := substr($2, 1, colon_pos); set_exist_pos := strpos($1, set_str); IF set_exist_pos > 0 THEN rarity_str := substr($2, colon_pos + 2); rarity_exist_str_start := substr($1, 1, set_exist_pos - 1); comma_pos := ELSE RETURN $1 || \', \' || $2; END IF; END' LANGUAGE 'plpgsql'; ---(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
