Re: [GENERAL] russian case-insensitive regexp search not working
Oleg Bartunov wrote:
alexander,
lc_ctype and lc_collate can be changed only at initdb !
You need to read localization chapter
http://www.postgresql.org/docs/current/static/charset.html
Yes, i knew about this, but i thought maybe somehow it can be changed
onthefly.
... (10 minutes later)
Yes, now when initdb done with --locale=ru_RU.UTF-8,
lower('RussianString') gives me 'russianstring', though, case-insensiive
regexp still not working. I guess i'll stick with lower() ~ lower()
construction.
And thanks everybody who replied!
Oleg
On Thu, 12 Jul 2007, alexander lunyov wrote:
Tom Lane wrote:
alexander lunyov <[EMAIL PROTECTED]> writes:
With this i just wanted to say that lower() doesn't work at all on
russian unicode characters,
In that case you're using the wrong locale (ie, not russian unicode).
Check "show lc_ctype".
db=> SHOW LC_CTYPE;
lc_ctype
--
C
(1 запись)
db=> SHOW LC_COLLATE;
lc_collate
C
(1 запись)
Where can i change this? Trying to SET this parameters gives error
"parameter "lc_collate" cannot be changed"
Or [ checks back in thread... ] maybe you're using the wrong operating
system. Not so long ago FreeBSD didn't have Unicode locale support at
all; I'm not sure if 6.2 has that problem but it is worth checking.
Does it work for you to do case-insensitive russian comparisons in
"grep", for instance?
I put to textfile 3 russian strings with different case of first char
and grep'ed them all:
# cat > textfile
Зеленая
Зеленодольская
зеленая
# grep -i зелен *
textfile:Зеленая
textfile:Зеленодольская
textfile:зеленая
So i think system is fine about unicode.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
alexander lunyov
[EMAIL PROTECTED]
---(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
Re: [GENERAL] russian case-insensitive regexp search not working
alexander, lc_ctype and lc_collate can be changed only at initdb ! You need to read localization chapter http://www.postgresql.org/docs/current/static/charset.html Oleg On Thu, 12 Jul 2007, alexander lunyov wrote: Tom Lane wrote: alexander lunyov <[EMAIL PROTECTED]> writes: With this i just wanted to say that lower() doesn't work at all on russian unicode characters, In that case you're using the wrong locale (ie, not russian unicode). Check "show lc_ctype". db=> SHOW LC_CTYPE; lc_ctype -- C (1 запись) db=> SHOW LC_COLLATE; lc_collate C (1 запись) Where can i change this? Trying to SET this parameters gives error "parameter "lc_collate" cannot be changed" Or [ checks back in thread... ] maybe you're using the wrong operating system. Not so long ago FreeBSD didn't have Unicode locale support at all; I'm not sure if 6.2 has that problem but it is worth checking. Does it work for you to do case-insensitive russian comparisons in "grep", for instance? I put to textfile 3 russian strings with different case of first char and grep'ed them all: # cat > textfile Зеленая Зеленодольская зеленая # grep -i зелен * textfile:Зеленая textfile:Зеленодольская textfile:зеленая So i think system is fine about unicode. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] russian case-insensitive regexp search not working
Tom Lane wrote: alexander lunyov <[EMAIL PROTECTED]> writes: With this i just wanted to say that lower() doesn't work at all on russian unicode characters, In that case you're using the wrong locale (ie, not russian unicode). Check "show lc_ctype". db=> SHOW LC_CTYPE; lc_ctype -- C (1 запись) db=> SHOW LC_COLLATE; lc_collate C (1 запись) Where can i change this? Trying to SET this parameters gives error "parameter "lc_collate" cannot be changed" Or [ checks back in thread... ] maybe you're using the wrong operating system. Not so long ago FreeBSD didn't have Unicode locale support at all; I'm not sure if 6.2 has that problem but it is worth checking. Does it work for you to do case-insensitive russian comparisons in "grep", for instance? I put to textfile 3 russian strings with different case of first char and grep'ed them all: # cat > textfile Зеленая Зеленодольская зеленая # grep -i зелен * textfile:Зеленая textfile:Зеленодольская textfile:зеленая So i think system is fine about unicode. -- alexander lunyov [EMAIL PROTECTED] ---(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
Re: [GENERAL] deleting rows with foreign keys
Tom Allison <[EMAIL PROTECTED]> writes: > I have ~250K rows in this table. > the token_idx is referenced in two other tables, both of whom have a > foreign key constraint to ON DELETE CASCADE. > Of the other two tables, one has ~1M rows and the other ~350K rows. > Problem: > Deleting one row can take 2 seconds. > Is there something I can do to improve the speed. Put indexes on the referencing columns. Without that, a seqscan is required to look for referencing rows. regards, tom lane ---(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] deleting rows with foreign keys
Here's my table:
Table "public.tokens"
Column |Type |
Modifiers
---+-
+
token_idx | bigint | not null default nextval
('tokens_token_idx_seq'::regclass)
token | character varying(140) | not null
last_seen | timestamp without time zone | default now()
Indexes:
"tokens_pkey" PRIMARY KEY, btree (token_idx)
"tokens_token_key" UNIQUE, btree (token)
I have ~250K rows in this table.
the token_idx is referenced in two other tables, both of whom have a
foreign key constraint to ON DELETE CASCADE.
Of the other two tables, one has ~1M rows and the other ~350K rows.
So they are both one to many relationships with many of the token
table rows appearing in the other two.
Problem:
Deleting one row can take 2 seconds.
Is there something I can do to improve the speed. Locking the tables
is an option as this is a maintenance procedure and not regular
business. But I don't know that this will be useful or practical.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Count(*) throws error
"Jasbinder Singh Bali" <[EMAIL PROTECTED]> writes:
> I'm using the following statement in my plpgsql function
> SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
> I have decalred no_rows int4 and initialized it to zero
> Running the function throws the following error:
> ERROR: syntax error at or near "(" at character 13
> QUERY: SELECT $1 (*) FROM tbl_concurrent
I'll bet a nickel you have a local variable named "count" in that
function, and plpgsql is blindly trying to substitute its value into
the SQL query. The replacement of "COUNT" by " $1 " in the query
text is the tip-off.
regards, tom lane
---(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
[GENERAL] Re:[GENERAL] auditing question - PARTIALY SOLVED
Hello, with help of Bricklen, I have found that the problem is the scope of call EXECUTE. When this is called it seems to be "launched" outside of the trigger's scope and OLD/NEW is not defined. (Manual suggests it) The remaining question is how to compare OLD.* and NEW.* in generic way for 8.0.x version Regards, Bohdan > > IF TG_OP = 'DELETE' THEN > EXECUTE 'INSERT INTO audit.' || TG_RELNAME || > ' SELECT _name, now(), OLD.*;'; > RETURN OLD; > = > > and binding the procedure to trigger AFTER INSERT,DELETE, UPDATE, gives me > problem: > > ERROR: NEW used in query that is not in a rule > CONTEXT: SQL statement "INSERT INTO audit.communities SELECT _name, now(), > row(NEW);" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Count(*) throws error
On Jul 11, 4:00 pm, [EMAIL PROTECTED] ("Jasbinder Singh Bali") wrote:
> I don't know why isn't count(*) working
Works for me:
create table tbl_concurrent(a int);
insert into tbl_concurrent values (1);
insert into tbl_concurrent values (9);
insert into tbl_concurrent values (4);
create or replace function foo() returns int as
$$
declare
no_rows int := 0;
begin
select into no_rows count(*) from tbl_concurrent;
return no_rows;
end;
$$
language plpgsql;
select foo();
foo
-
3
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Count(*) throws error
-- Original message --
From: "Jasbinder Singh Bali" <[EMAIL PROTECTED]>
> Hi,
>
> I'm using the following statement in my plpgsql function
>
> SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
>
> I have decalred no_rows int4 and initialized it to zero
>
> Running the function throws the following error:
>
> ERROR: syntax error at or near "(" at character 13
> QUERY: SELECT $1 (*) FROM tbl_concurrent
> CONTEXT: SQL statement in PL/PgSQL function "sp_insert_tbl_concurrent" near
> line 8
>
Try SELECT COUNT(*) INTO no _rows FROM tbl_concurrent;
See
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
for complete syntax.
> If i comment this count(*) line, the error goes.
>
> I don't know why isn't count(*) working
>
> Thanks
> Jas
--- Begin Message ---
Hi,I'm using the following statement in my plpgsql functionSELECT INTO no_rows COUNT(*) FROM tbl_concurrent; I have decalred no_rows int4 and initialized it to zeroRunning the function throws the following error:
ERROR: syntax error at or near "(" at character 13QUERY: SELECT $1 (*) FROM tbl_concurrentCONTEXT: SQL statement in PL/PgSQL function "sp_insert_tbl_concurrent" near line 8
If i comment this count(*) line, the error goes.I don't know why isn't count(*) workingThanksJas
--- End Message ---
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Count(*) throws error
On Jul 11, 2007, at 4:00 PM, Jasbinder Singh Bali wrote:
Hi,
I'm using the following statement in my plpgsql function
SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
I have decalred no_rows int4 and initialized it to zero
Running the function throws the following error:
ERROR: syntax error at or near "(" at character 13
QUERY: SELECT $1 (*) FROM tbl_concurrent
CONTEXT: SQL statement in PL/PgSQL function
"sp_insert_tbl_concurrent" near line 8
If i comment this count(*) line, the error goes.
I don't know why isn't count(*) working
SELECT COUNT(*) INTO no_rows FROM tbl_concurrent;
Erik Jones
Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Postgres 8.2 binary for ubuntu 6.10?
Just a word of advice... unless you plan to spend lots of time on your db (like you want to think about it more than twice a week sort of thing...), just go with what you have in terms of the distro. We are running 8.1.4. And it just works, yes, even after all this time! You are certainly behind a good firewall, so if you have X.X.X, and it works (ie, your developpers have certified for X.X.X), why think about having the latest? Upgrading to new versions may well expose problems (like I remember someone talking about query optimisations a while back) that are non issues. If you are going to be pushing the limits, then compiling your own versions is not going to be an issue... Just my 2c Cheers Anton ps. I know, when a new version comes out so often it is soo hard to resist! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Count(*) throws error
Hi,
I'm using the following statement in my plpgsql function
SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
I have decalred no_rows int4 and initialized it to zero
Running the function throws the following error:
ERROR: syntax error at or near "(" at character 13
QUERY: SELECT $1 (*) FROM tbl_concurrent
CONTEXT: SQL statement in PL/PgSQL function "sp_insert_tbl_concurrent" near
line 8
If i comment this count(*) line, the error goes.
I don't know why isn't count(*) working
Thanks
Jas
Re: [GENERAL] how does a temp table work?
On Wed, Jul 11, 2007 at 02:36:54PM -0500, Ron Johnson wrote: > > Will GLOBAL TEMP tables be added any time soon? I think there's a recent thread/bunfight about this topic on -hackers. You may want to draw your own conclusions on the basis of what you see there -- I'm not willing to speculate :) A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] exit code -1073741819
"Shuo Liu" <[EMAIL PROTECTED]> writes: > Thank you so much for pointing out the problems. The first one seems very > silly :-(. For the second one, I may stay out of trouble if I don't use > transform() at all (I can tolerate some error without transform), am I right? Hmm, I wouldn't suggest removing functionality to work around a bug that presumably will get fixed soon. It's not that common to run out of memory (else this bug would've been found long ago), so I think you could just live with it. regards, tom lane ---(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
Re: [GENERAL] exit code -1073741819
Hi, Tom, Thank you so much for pointing out the problems. The first one seems very silly :-(. For the second one, I may stay out of trouble if I don't use transform() at all (I can tolerate some error without transform), am I right? --- On Wed 07/11, Tom Lane < [EMAIL PROTECTED] > wrote: From: Tom Lane [mailto: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Date: Wed, 11 Jul 2007 15:33:41 -0400 Subject: Re: [GENERAL] exit code -1073741819 "Shuo Liu" <[EMAIL PROTECTED]> writes:>> Whoa ... that is a whole lot more data than I'm used to seeing in>> TopMemoryContext. How many stats dump lines are there exactly (from>> here to the crash report)? > OK, I didn't know that was a surprise. There are about 600 stats dump lines > in between.Well, my suspicion was correct: there were boatloads of memory contexts,but we weren't seeing all of them. The answer is twofold:1. Your function is opening a cursor (maybe more than one, I didn't readtoo carefully) on each iteration, and not closing it. This results ina sizable amount of memory being eaten --- it looks like a couplehundred K per cursor on my machine. Eventually you run out of memory.2. PostGIS has a bug that causes a guaranteed crash once you run out ofmemory, if you've been using transform(), because it sets up a memorycontext that has no stats() method. That's why the context statsprintout failed partway through --- it crashed as soon as it came toone of the PostGIS contexts.I've complained to the PostGIS folk about the latter, and hopefullythey'll fix it in their next release. Meanwhile, you need to be morecareful about closing cursors when you're done with them. regards, tom lane---(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 ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Am I missing something about the output ofpg_stop_backup()?
On Tue, 2007-07-10 at 12:44 -0700, Ben wrote: > ...which I then need to tokenize in order to find which archived wal > files I'll need to keep as part of the backup. For instance, that > output above will result in a WAL file named like so: Try using pg_xlogfile_name() http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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
Re: [GENERAL] how does a temp table work?
On 07/11/07 12:06, Andrew Sullivan wrote: On Wed, Jul 11, 2007 at 10:28:25PM +0530, Joshua N Pritikin wrote: and foo is executed from different transactions in parallel then each transaction will see its own private temp table, right? They will see their own private temp tables _no matter what_. There's no "shared temp table" support in Postgres. Will GLOBAL TEMP tables be added any time soon? I know the docs say that the standards implies needing modules, but "shared definition" is really handy. We use regularly such GLOBAL TEMP tables without modules. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] exit code -1073741819
"Shuo Liu" <[EMAIL PROTECTED]> writes: >> Whoa ... that is a whole lot more data than I'm used to seeing in >> TopMemoryContext. How many stats dump lines are there exactly (from >> here to the crash report)? > OK, I didn't know that was a surprise. There are about 600 stats dump lines > in between. Well, my suspicion was correct: there were boatloads of memory contexts, but we weren't seeing all of them. The answer is twofold: 1. Your function is opening a cursor (maybe more than one, I didn't read too carefully) on each iteration, and not closing it. This results in a sizable amount of memory being eaten --- it looks like a couple hundred K per cursor on my machine. Eventually you run out of memory. 2. PostGIS has a bug that causes a guaranteed crash once you run out of memory, if you've been using transform(), because it sets up a memory context that has no stats() method. That's why the context stats printout failed partway through --- it crashed as soon as it came to one of the PostGIS contexts. I've complained to the PostGIS folk about the latter, and hopefully they'll fix it in their next release. Meanwhile, you need to be more careful about closing cursors when you're done with them. regards, tom lane ---(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
Re: [GENERAL] troubble with contrib compile
marcelo Cortez <[EMAIL PROTECTED]> writes: > i downloaded postgres8.2.4 sources , expand and > ./configure and install with success. > Now i need one module from contrib directory , > fuzzystrmatch > cd /postgres/contrib/fuzzymatchstr [ ok ] > make > throws > make > Makefile:15: ../../src/Makefile.global: No such file > or directory You seem to have removed the results of configure. As a general rule it's best to build the contrib modules in the same tree where you just built the Postgres core --- they definitely need configure's outputs and I think some of them require other files that get built along the way. If you are trying to match a previously built core system, be sure to re-configure with the exact same configure options, else the contrib modules may not work. pg_config --configure will help refresh your memory if you forgot what you used ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] auditing question
Hello, I am restricted to 8.0.7 version of postgresql and I am facing two problems when trying to build generic auditing function. I went through documentation http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html example: Example 35-3. A PL/pgSQL Trigger Procedure For Auditing had rewritten it into format: audit. is copy of without constraints and also inherits some data from generic auditing table (as name, when, ...) = CREATE OR REPLACE FUNCTION audit_table() RETURNS trigger AS $$ DECLARE _name TEXT; BEGIN -- Get current user SELECT INTO _name CURRENT_USER; IF TG_OP = 'DELETE' THEN EXECUTE 'INSERT INTO audit.' || TG_RELNAME || ' SELECT _name, now(), OLD.*;'; RETURN OLD; ELSIF TG_OP = 'INSERT' THEN EXECUTE 'INSERT INTO audit.' || TG_RELNAME || ' SELECT _name, now(), NEW.*;'; RETURN NEW; ... = and binding the procedure to trigger AFTER INSERT,DELETE, UPDATE, gives me problem: ERROR: NEW used in query that is not in a rule CONTEXT: SQL statement "INSERT INTO audit.communities SELECT _name, now(), row(NEW);" Thus do I do something wrong or example is not compatible with my version? Even tried to remove EXECUTE and limited it to one table only - no help. I like to use this approach instead of rules, while I can setup SECURITY DEFINER on procedure and therefore do not need to solve permissions on audit tables. Going further rules and triggers give me NEW and OLD records at my disposal. Can these records be comparable "at-once"? Imagine table, which has significant number of attributes so you do not want to list them explicitly in condition. Especially when you have more such tables. Even any generic function which will take two records as parameter, compare them and return BOOLEAN is enough Thank you for help, Bohdan ---(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
Re: [GENERAL] how does a temp table work?
On Wed, Jul 11, 2007 at 10:28:25PM +0530, Joshua N Pritikin wrote: > and foo is executed from different transactions in parallel then each > transaction will see its own private temp table, right? They will see their own private temp tables _no matter what_. There's no "shared temp table" support in Postgres. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(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
[GENERAL] how does a temp table work?
I read the docs but I'm still not sure. If I create a temp table with ON COMMIT DROP then is that table private to the transaction? In other words, if the temp table is created in plpgsql stored procedure "foo" and foo is executed from different transactions in parallel then each transaction will see its own private temp table, right? -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] troubble with contrib compile
hi all i downloaded postgres8.2.4 sources , expand and ./configure and install with success. Now i need one module from contrib directory , fuzzystrmatch cd /postgres/contrib/fuzzymatchstr [ ok ] make throws make Makefile:15: ../../src/Makefile.global: No such file or directory Makefile:16: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. i'm wrong? any clue? best regards mdc more data : ( from select version() ). "PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 (Gentoo 4.1.1-r3)" __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange round behaviour w/ more than 2 decimals
Hi Tom, Sorry. After sending to group one friend of mine explained to me how round works -- not postgresql round, but round in math. I do not know any software that would produce that output. I thought that when 1.8947 was rounded to 2 decimal places, 7 would round-up 4, then "5" would round-up 9. Reading http://www.ai.com.br/pessoal/indices/2A1A.HTM show the correct way (sorry, the page is in brazilian portuguese). Anyway, the document shows: DO NOT DO RECURSIVE ROUNDS! hehe. Thanks for the answer and sorry for the incorrect question. Regards, Fernando Pinheiro 2007/7/11, Tom Lane <[EMAIL PROTECTED]>: "Fernando Madruga Pinheiro" <[EMAIL PROTECTED]> writes: > If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89. Why do you consider that wrong? It's the closest approximation. Do you know of any other software that would produce 1.90 from a similar query? > Rounding to 3 decimal places, then 2 (eg.: ROUND(ROUND(1.8947, 3), 2) ) then > I get 1.90! Since ROUND() is a lossy process by definition, you cannot expect that different multi-step paths will always yield the same result. regards, tom lane
Re: [GENERAL] Strange round behaviour w/ more than 2 decimals
"Fernando Madruga Pinheiro" <[EMAIL PROTECTED]> writes: > If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89. Why do you consider that wrong? It's the closest approximation. Do you know of any other software that would produce 1.90 from a similar query? > Rounding to 3 decimal places, then 2 (eg.: ROUND(ROUND(1.8947, 3), 2) ) then > I get 1.90! Since ROUND() is a lossy process by definition, you cannot expect that different multi-step paths will always yield the same result. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange round behaviour w/ more than 2 decimals
On Wed, Jul 11, 2007 at 09:50:25AM -0300, Fernando Madruga Pinheiro wrote: > Hi. I'm having some strange round behaviour when using 4 decimals. > > If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89. I asked my math teacher a similar question back in 7th grade: If you round 1.8947 to 1.89 the error is 0.0047. If you round 1.8947 to 1.90 the error is 0.0053. My numbers where 1.49 and the rounding I proposed was: round to 1 decimals, round to 0 decimals to get 2 out of 1.49. I think he is correct and that the postgresql function is also correct. Peter ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] odbc parameters
am Wed, dem 11.07.2007, um 14:55:28 +0200 mailte Zlatko Matic folgendes:
> I have already tried COPY.
> But, it has problems with type castings.
> For example, COPY operation fails because PostgreSQL can't copy value
> 7.844,000 into NUMERIC field...
Either copy such values into a temp. table with text-columns and work
with arbitrary sql-funktions (you can convert it to numeric with, for
instance, regexp_replace('7.844,000',',.*$', '')::numeric) to fill the
destination table with the values or work before the COPY with
text-lools like sed, awk, perl, ...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] odbc parameters
I have already tried COPY. But, it has problems with type castings. For example, COPY operation fails because PostgreSQL can't copy value 7.844,000 into NUMERIC field... Regards, Zlatko - Original Message - From: "A. Kretschmer" <[EMAIL PROTECTED]> To: Sent: Wednesday, July 11, 2007 2:31 PM Subject: Re: [GENERAL] odbc parameters am Wed, dem 11.07.2007, um 14:15:02 +0200 mailte Zlatko Matic folgendes: Hello, please don't hijack other threads. If you only change the subject for a new question, your mail will be sorted completely wrong. (within modern email-clients such thunderbird or mutt) I use MS Access for data import. Access imports csv file, make some calculation and transffers data to PostgreSQL. Import the csv-file directly in postgresql and make the calculations within PG? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Strange round behaviour w/ more than 2 decimals
Hi. I'm having some strange round behaviour when using 4 decimals. If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89. Rounding to 3 decimal places, then 2 (eg.: ROUND(ROUND(1.8947, 3), 2) ) then I get 1.90! This way, when rounding, ROUND is considering only decimal places parameter + 1 to determine which is the rounding (ROUND(1.8951, 2) returns 1.90). I've tested in 8.1.3 and 8.2.4. Is it expected to work this way? There's a way to "force" the round to consider all the decimal places? Thanks, Fernando Pinheiro
Re: [GENERAL] odbc parameters
am Wed, dem 11.07.2007, um 14:15:02 +0200 mailte Zlatko Matic folgendes: > Hello, please don't hijack other threads. If you only change the subject for a new question, your mail will be sorted completely wrong. (within modern email-clients such thunderbird or mutt) > > I use MS Access for data import. Access imports csv file, make some > calculation and transffers data to PostgreSQL. Import the csv-file directly in postgresql and make the calculations within PG? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] odbc parameters
Hello, I use MS Access for data import. Access imports csv file, make some calculation and transffers data to PostgreSQL. Unfortunately, it takes a lot of time to transfer data to PostgreSQL. My odbc settings are following: [ODBC] DRIVER=PostgreSQL Unicode UID=postgres XaOpt=1 LowerCaseIdentifier=0 UseServerSidePrepare=1 ByteaAsLongVarBinary=0 BI=0 TrueIsMinus1=0 DisallowPremature=1 UpdatableCursors=1 LFConversion=1 ExtraSysTablePrefixes=dd_ CancelAsFreeStmt=0 Parse=1 BoolsAsChar=0 UnknownsAsLongVarchar=0 TextAsLongVarchar=1 UseDeclareFetch=0 Ksqo=1 Optimizer=1 CommLog=0 Debug=0 MaxLongVarcharSize=8190 MaxVarcharSize=255 UnknownSizes=0 Socket=4096 Fetch=100 ConnSettings= ShowSystemTables=0 RowVersioning=1 ShowOidColumn=0 FakeOidIndex=0 Protocol=7.4-1 ReadOnly=0 SSLmode=allow PORT=5432 SERVER=localhost DATABASE=PLANINGZ Could you suggest what parameters values would yield best performance for batch import to PostgreSQL? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Day of week vs. Language
Am Freitag, 6. Juli 2007 11:15 schrieb Stefan Christ: > the documentation of PGSQL 8.0 tells me, that extract(dow ...) will > return 0 for Sundays. I know that this behaviour depends on the > language settings on MSSQL, so that I have to correct this settings > with an offset. What is the behaviour in PGSQL? Does the dow result > also depend on the language settings, or will the value 0 ALWAYS be a > Sunday? It's always that way for this particular function. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
Re: [GENERAL] Duplicate Unique Key constraint error
On Jul 10, 2007, at 3:09 PM, Tom Lane wrote: "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: Transaction 1 started, saw max(dig_id) = 30 and inserted new dig_id=31. Now the time when Transaction 2 started and read max(dig_id) it was still 30 and by the time it tried to insert 31, 31 was already inserted by Transaction 1 and hence the unique key constraint error. This is exactly why you're recommended to use sequences (ie serial columns) for generating IDs. Taking max()+1 does not work, unless you're willing to lock the whole table and throw away vast amounts of concurrency. I wonder how SQL server is handling this? Are they locking the table? I realize it's off-topic, but I'm still curious. Sequences are your friend. they come in INT and BIGINT flavors, but BIGINT is a lot of rows. Can set set Sequences to automatically rollover back to zero? ---(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
Re: [GENERAL] How to pg_dumpall without root password
On 11.07.2007 08:10, Dat Chu wrote: I am the new sysadmin and was given the task to move a postgresql installation from one box to another. So I want to do pg_dumpall . However, there is no record of postgresql superuser/root password. I do have root access to the system however. Does anyone have a suggestion on what I should do? Locate pg_hba.conf (should reside in $PGDATA), open it in an editor, replace md5 with trust. -- Regards, Hannes Dorbath ---(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
