Re: [SQL] [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
Dario V. Fassi wrote: > Yes you are right , the original data come from a DB2 with CodePage > IBM-850 and was inserted without complains in a Postgres 7.3.6 with > SQL_ASCII. If you have a program named "recode" installed you could try using that to recode the dump file to a supported encoding. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] [ocpfree] Query Optimization Help Needed.
Hi all, I need ur help regarding the optimization of following query. If possible pls reply asap. Thankz a ton in advance. SELECT company_name,coupon_id,coupon_title, city_name, category_name, NVL(person_name,' ') person_name, NVL (locality_name,' ') locality_name, NVL(company_address,' ') company_address, NVL(mobile_no,' ') mob, NVL(company_phone1,' ') ph1, NVL(company_phone2,' ') ph2, psid_1,discount1, NVL(psid_2,-1) p2, NVL(discount2,-1) d2, NVL(psid_3,-1) p3, NVL(discount3,-1) d3, TO_CHAR(expiry_date,'dd-mm-')expdate, REDEMPTION_METHOD, PICTURE_FILE_NAME, NVL(company_info,' ') company_info FROM TBL_MCP_COUPON a, TBL_MCP_LOCALITY, TBL_MCP_CITY, TBL_MCP_CATEGORY WHERE (verify='Y' OR verify='R') AND ROWNUM <= 10 AND (SYSDATE <= EXPIRY_DATE) AND a.city_id=4 AND a.category_id=10 AND a.category_id=TBL_MCP_CATEGORY.category_id AND a.locality_id=TBL_MCP_LOCALITY.locality_id AND a.city_id=TBL_MCP_CITY.city_id --check for with and without following two lines , becouse these lines will be included in query conditionally --AND a.locality_id=25 --AND (psid_1=113 OR psid_2=113 OR psid_3=113) AND a.coupon_id NOT IN ( SELECT a.coupon_id FROM TBL_MCP_USERCOUPONS b,TBL_MCP_COUPON a WHERE a.coupon_id=b.coupon_id AND a.REDEMPTION_METHOD = 1 AND b.MIN= 1234567891) ORDER BY LOWER(company_name); __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Yahoo! Groups Sponsor ~--> Yahoo! Domains - Claim yours for only $14.70 http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/o7folB/TM ~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ocpfree/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] FOR-IN-EXECUTE, why fail?
I wrote the following:
CREATE OR REPLACE FUNCTION escritorio.seq_valor_actual( text)
RETURNS integer AS '
DECLARE
secuencia ALIAS FOR $1;
valor_actual integer := 0;
v_query text;
actual integer;
BEGIN
RAISE NOTICE ''el parametro tiene el siguiente valor %'' ,secuencia;
v_query := ''SELECT last_value FROM '' || quote_ident(secuencia);
RAISE NOTICE ''la sentencia a ejecutar es %'' ,v_query;
FOR actual IN EXECUTE v_query LOOP
valor_actual := actual;
END LOOP;
RETURN valor_actual;
END;
'LANGUAGE 'plpgsql';
And i call:
select seq_valor_actual('s_id_reserva');
The s_id_reserva is a sequence. The error is the following:
WARNING: plpgsql: ERROR during compile of seq_valor_actual near line 12
ERROR: missing .. at end of SQL expression
I think the problem is in the FOR but i see correct all.
Can i help me?
Any idea?
Tahnks
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
Tom Lane wrote: "Dario V. Fassi" <[EMAIL PROTECTED]> writes: A simple question, we need to migrate many (>20) postgres databases from SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server. SQL_ASCII is not an encoding (it's more like the absence of knowledge about an encoding). What is the data actually stored as? With Dump/Restore , we get an error (Invalid Unicode) in any field that has a 8 bits character coming from the SQL_ASCII , even setting the client_encoding to WIN, ISO-8859-1, and others encodings. It might work to just UPDATE pg_database to set datencoding to the correct value reflecting what you have actually stored. You might then need to REINDEX any indexes on textual columns, but I don't think anything else would go wrong. If you have a mishmash of different encodings in a single database, then of course there is no simple solution; you are in for some pain while you try to fix the data. Yes you are right , the original data come from a DB2 with CodePage IBM-850 and was inserted without complains in a Postgres 7.3.6 with SQL_ASCII. Now we are in a Jail , because IBM-850 , isn't WIN, isn't ISO-xx , isn't no one postgresql's encoding. So when in change via pg_databases the encoding , 8 bits characters become garbage. More even if we accept this garbage chars and we set encoding to e.g. ISO-8859-1 it's impossible go to a UNICODE because this garbage chars are invalid in client's encoding , so they are reject (in translation process as invalid unicode chars). We are in a big problem, and the only way out I can imagine is fix the data by hand :-! . Dario,
Re: [SQL] [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
Tom Lane wrote: "Dario V. Fassi" <[EMAIL PROTECTED]> writes: SQL_ASCII is not an encoding (it's more like the absence of knowledge about an encoding). What is the data actually stored as? Yes you are right , the original data come from a DB2 with CodePage IBM-850 and was inserted without complains in a Postgres 7.3.6 with SQL_ASCII. Ugh. You'll have to work out how to convert that codepage to one of the encodings that PG supports. Or else add it as a supported encoding (I'm not sure how hard that is, but it's not out of the question). regards, tom lane Ok, I'm interested is do that , but someone is the development group would give me some assistence to get a start point to work with. I program in Java, C, and less frequently in C++. Dario.
