Search DaniWeb - postgre

Re: postgre - how to make schema copy 11 Years Ago by LastMitch **@SPeed_FANat1c** >postgre - how to make schema copy So you want to copy … Re: Postgre time zone problems 11 Years Ago by LastMitch … codeigniter. OK, Then you really don't need to used **Postgre time zone** at all. You know you never mention **Codeigniter… Re: Postgre time zone problems 11 Years Ago by McLaren … timezone is UTC+2, Vilnius. Hmm, I already done using postgre. Need to think before understanding this if would work for… Load xml files in a postgre database 15 Years Ago by Typhoon Since there was no section for postgre databases, I decided to post my question here. After having … previous post) I would like to load them in a postgre database. Since I used Talend Open Studio, to do the… CSV to postgre with \copy problem/question 11 Years Ago by Elandir … been trying to import data from csv file to remote postgre database with no luck. Locally i can do it with… Postgre time zone problems 11 Years Ago by McLaren Hello, we are saving in the database time as our local time which is UTC+2 How to convert to other times? I found in that this should work: select sell_date, sell_date at time zone 'UTC+2' as UTC2 FROM cards order by id desc limit 1 This sql should return same time as it is, because its the same timezone. But it is … Re: Postgre time zone problems 11 Years Ago by LastMitch >But it is returing added 5 hours. Why could be that? Where to search the problem? Since your local time is UTC+2 and it 5 hours ahead. Try to used **TIMESTAMP WITH TIME ZONE**: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm Re: Postgre time zone problems 11 Years Ago by McLaren (sell_date::timestamp with time zone) at time zone 'UTC+2' still is not giving rith result. Is missing 4 hours sell_date at time zone 'UTC+2' as UTC2 BUt now I see it adds 4 hours instead of 5 in the above example. Not sure if I made mistake or what when thinking it add 5 hrs. Re: Postgre time zone problems 11 Years Ago by LastMitch **@SPeed_FANat1c** >BUt now I see it adds 4 hours instead of 5 in the above example. Not sure if I made mistake or what when thinking it add 5 hrs. Instead of `sell_date at time zone 'UTC+2' as UTC2` Try to used `sell_date at time zone 'Europe/London'` Read add **Time Zone Input**: http://www.postgresql.org/docs/current/interactive/datatype… postgre - how to make schema copy 11 Years Ago by McLaren I try in psql shell pg_dump --schema-only but is is doing nothing after hitting enter it just shows glab-> why is that? I tried adding ';' at the end of line - still the same. But before it there is glab=> so one char differs. Why is that? also is thera some way to save the login to database when connecting via shell? Re: postgre - how to make schema copy 11 Years Ago by McLaren thanks. I took a quickl look, but did not find difference except file name added after command, tried that out but did not do anything as well. So maybe I misslloked. But for now I don't need that. The problem was - the hard disk was full of the server and I could not use my application, so I wanted to make a copy of those tables in my local … Re: Postgre time zone problems 11 Years Ago by McLaren >Try to used sell_date at time zone 'Europe/London' this adds 2 hours to the time. London is at zero offset. So 2 hour diference. Looks like it returns good time. So I also tried sell_date at time zone 'Europe/Vilnius' as vilnius and it returns good time. Also sell_date at time zone 'EET' as EET, returns good - same time.… Re: Postgre time zone problems 11 Years Ago by McLaren BTW I guess by abreviatioin is incorrect to select. I selected from this: `select * from pg_timezone_names` and there are timezones with same abbreviatsions but different time. For example AST is having offset +3 hours at Asia/Qatar and same AST is having offset -4 hours at America/Puerto_Rico. Thats interesting. So then I need to select by … Re: Postgre time zone problems 11 Years Ago by LastMitch **@SPeed_FANat1c** The link you provided is for **CodeIgniter**. Unless you created a code using it then you need to put it in the Framework or another words put in the folder of your destination. I don't think it will work if you are using the code for something else it will only work on **CodeIgniter** >o then I need to select by full name… Re: Postgre time zone problems 11 Years Ago by McLaren I am using codeigniter. Postgre convert non latin chars case 11 Years Ago by McLaren SELECT getdatabaseencoding(), current_setting('client_encoding'), current_setting('lc_ctype'), current_setting('lc_collate'), version(); returns UTF8 UNICODE C C PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit select lower('ČĘĖĮ') returns ČĘĖĮ Actually what I … Re: Postgre convert non latin chars case 11 Years Ago by LastMitch >UTF8 UNICODE C C PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit May I ask what is the default encoding on **PostgreSQL**? I think it is not recognizing it. Try this: psql my_database -c 'SHOW SERVER_ENCODING' Read this (it has a function which you can used to test … Re: Postgre convert non latin chars case 11 Years Ago by McLaren I executed SHOW SERVER_ENCODING in navicat and it shows UTF8. It probably deos not matter if I use navicat and not psql, right? Re: Postgre convert non latin chars case 11 Years Ago by LastMitch >in navicat and it shows UTF8. It probably deos not matter if I use navicat and not psql, right? I never heard or used **navicat** until you mention it. This is more than just an query issue I think it has something to do with configuration. Read these short instructions (I assume you have read this if not you can take a look at it, it's … Re: Postgre convert non latin chars case 11 Years Ago by McLaren those links are just about set up on navicat. But think I need in general. I mean seeting up navicat will not make in php it to work well. And it does not matter how it shows in navicat those chars, the problem is that they are not converted to lowercase Re: Postgre convert non latin chars case 11 Years Ago by LastMitch >the problem is that they are not converted to lowercase Try these query: SELECT LOWER('word') FROM SomeTable WHERE word ILIKE 'ČĘĖĮ%'; SELECT * FROM SomeTable where LOWER('word') ILIKE 'ČĘĖĮ%'; To me this word **`ČĘĖĮ`**, those letters have accents so I think it's not changing the word in lower case? Have you try just using… Re: Postgre convert non latin chars case 11 Years Ago by McLaren SELECT LOWER('word') FROM SomeTable WHERE word ILIKE 'ČĘĖĮ%'; you meant lower(word) probably. ----------- SELECT LOWER(surname) FROM managers WHERE surname ILIKE 'ČĘĖĮ%'; returns ČĘĖĮ -------------- this SELECT * FROM managers where LOWER(surname) ILIKE 'ČĘĖĮ%'; returns row, but its ok. WHen we change to 'ČĘĖį' (… Re: Postgre convert non latin chars case 11 Years Ago by LastMitch >returns row, but its ok. WHen we change to 'ČĘĖį' (one letter is lower) then it does not return row. SELECT * FROM managers where LOWER(surname) ILIKE 'ČĘĖĮ%'; I think that's a good thing. What language is this word **ČĘĖĮ**? Since it did lower one letter then I think it has something to do with accents preventing it to lower case and … Re: Postgre convert non latin chars case 11 Years Ago by McLaren What language is this word ČĘĖĮ? Its just random letters. Those letters are from lithuanian language. it would be very weird that UTF-8 would be bad. Every site which uses UTF-8 works well with lituanian chars. I did not try another encoding. Re: Postgre convert non latin chars case 11 Years Ago by McLaren http://www.postgresql.org/message-id/3CD6B446.70408@giub.uni-bonn.de here somebody had also same problem and he tried creating tables with different encodings. But it was 2002, now itw 11 years after.. Is it really still a bug or what the hell. On mysql tried - works like charm. Re: Postgre convert non latin chars case 11 Years Ago by LastMitch >Is it really still a bug or what the hell. On mysql tried - works like charm. That link is pretty old but useful. There's nothing wrong with the **UTF8 encoding** so disregard what I mention. You're right it would be wierd not to have that. Read this (it's more updated and more detail about the issue you are having): http://postgresql.… Re: Postgre convert non latin chars case 11 Years Ago by McLaren Yeah, converting to ASCII might help, but then there is no convertion back. But for search purpose it might do the job - convert to ascii, then what user enters in the for also convert to ascii, both lower case, and serach will find. Then ILIKE will work as well so I will not even need function lower(). Re: Postgre convert non latin chars case 11 Years Ago by McLaren here is what I made: CREATE OR REPLACE FUNCTION transliterate(my_text VARCHAR) RETURNS varchar AS $$ DECLARE text_out VARCHAR DEFAULT ''; BEGIN text_out := my_text; text_out := translate(text_out, 'àâäåáăąãā', 'aaaaaaaaa'); text_out := replace(text_out, 'æ', 'ae'); text_out := translate(text_out, … Re: Load xml files in a postgre database 15 Years Ago by gtd yes, of course it is possible since talend open studio is a comprehensive open source data integration software. Do you need help to do it? Re: Load xml files in a postgre database 15 Years Ago by Typhoon I am going to see if I can manage to do it myself on monday. Can you tell me where I can find a kind of tutorial on Talend Open Studio please ?