Re: [SQL] Distinct oddity
am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsarg...@rocketmail.com: >Is firmen a table or a view? It's a table. am 08.05.2009 21:52 Uhr schrieb Tom Lane unter t...@sss.pgh.pa.us: >> It doesn't seem to be related to null values (which wouldn't explain it >> anyway) nor to this particular field... > > Can you reproduce it in other contexts than specifically count(distinct)? > In particular I'd try > > select count(*) from > (select distinct f.bezeichnung from firmen f) ss; FAKDB=# select count(*) from FAKDB-# (select distinct f.bezeichnung from firmen f) ss; count --- 73437 (1 row) > > select count(*) from > (select distinct f.bezeichnung||'e' from firmen f) ss; FAKDB=# select count(*) from (select distinct f.bezeichnung||'e' from firmen f) ss; count --- 72535 (1 row) > If those give the same numbers as you're showing here, then the > next step would be to dump out the actual results of the SELECT DISTINCT > queries and compare them --- looking at the actual data values should > give some insight as to what's happening. FAKDB=# select distinct f.bezeichnung from firmen f order by 1 limit 5 FAKDB-# ; bezeichnung - sterreichisches Verkehrsbro AG \x01Assistenz \x10Frohstoff Design & Textilveredelung "1. Mittelschule ""Am Kupferberg""" (5 rows) FAKDB=# select distinct f.bezeichnung||'e' from firmen f order by 1 limit 5 ; ?column? -- Österreich/Welt (Ltg.)e sterreichisches Verkehrsbro AGe \x01Assistenze \x10Frohstoff Design & Textilveredelunge "1. Mittelschule ""Am Kupferberg"""e (5 rows) Aha, the "Österreich/Welt (Ltg.)"-entry is missing in the 1st query. So that does smell like a locale problem. > BTW, what is the datatype of f.bezeichnung, It's character varying(255). Just for the record: FAKDB=# explain analyze select distinct f.bezeichnung||'e' from firmen f order by 1 limit 5; QUERY PLAN - Limit (cost=16173.07..16174.08 rows=5 width=18) (actual time=1667.841..1667.855 rows=5 loops=1) -> Unique (cost=16173.07..16933.66 rows=3765 width=18) (actual time=1667.839..1667.851 rows=5 loops=1) -> Sort (cost=16173.07..16553.36 rows=152117 width=18) (actual time=1667.837..1667.844 rows=5 loops=1) Sort Key: (((bezeichnung)::text || 'e'::text)) Sort Method: external merge Disk: 4640kB -> Seq Scan on firmen f (cost=0.00..13646.46 rows=152117 width=18) (actual time=0.069..353.777 rows=152118 loops=1) Total runtime: 1669.998 ms (7 rows) > and what locale are you > running in? lc_collate | de_DE | Shows the collation order locale. lc_ctype| de_DE | Shows the character classification and case conversion locale. The encoding is UTF-8. Best, Maximilian Tyrtania -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and restore
On 2009-05-08, Jyoti Seth wrote: > Hello, > > I have two databases db1 and db2 with the same database structure on > different systems with no network connection. In the first system with the > db1 database user updates the master data. At the end of every day, the user > needs to take the backup of updated data of master tables on db1 and update > the data on another system with db2 database. > > We can't use WAL as in this as we want to take incremental backup of few > tables only and can't use slony as there is no network connection between > the systems. > > Please suggest some solution. some sort of diff? dump the tables you are interested in in a known order (perhaps use "copy from select order by " ) compare it with yesterdays ship the differences. dump the table at the remote end apply the patch truncate the table and load the patched version. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
Maximilian Tyrtania writes: > FAKDB=# select distinct f.bezeichnung from firmen f order by 1 limit 5 > FAKDB-# ; >bezeichnung > - > sterreichisches Verkehrsbro AG > \x01Assistenz > \x10Frohstoff Design & Textilveredelung > "1. Mittelschule ""Am Kupferberg""" > (5 rows) > FAKDB=# select distinct f.bezeichnung||'e' from firmen f order by 1 limit 5 > ; > ?column? > -- > Österreich/Welt (Ltg.)e > sterreichisches Verkehrsbro AGe > \x01Assistenze > \x10Frohstoff Design & Textilveredelunge > "1. Mittelschule ""Am Kupferberg"""e > (5 rows) > Aha, the "Österreich/Welt (Ltg.)"-entry is missing in the 1st query. So that > does smell like a locale problem. That only proves that adding the 'e' changes the sort order, which is completely unsurprising for any non-C locale. What you need to do is dump out the *entire* results of the DISTINCT queries and look for the unmatched lines. I'd try dumping to two files, stripping the 'e' with sed, and then sort/diff. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
On Sat, May 9, 2009 at 10:33 AM, Tom Lane wrote: > That only proves that adding the 'e' changes the sort order, which is > completely unsurprising for any non-C locale. What you need to do is > dump out the *entire* results of the DISTINCT queries and look for the > unmatched lines. I'd try dumping to two files, stripping the 'e' with > sed, and then sort/diff. How could adding an "e" change the sorting of "Österreich/Welt (Ltg.)" compared to "Šsterreichisches Verkehrsb ro AG" in de_DE or en_US (or any locale)? It's also odd that the "1. Mittelschule ..." line is getting sorted after those. -- Glenn Maynard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
Maximilian Tyrtania wrote: > am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsarg...@rocketmail.com: > > and what locale are you running in? > > lc_collate | de_DE > | Shows the collation order locale. > lc_ctype| de_DE > | Shows the character classification and case conversion locale. > > The encoding is UTF-8. Note that the de_DE locale uses Latin9 encoding, which is incompatible with UTF8. I'd try checking if the problem is reproducible in de_DE.utf8 (you need to create a new database for testing, obviously). If it's not, then the incompatible locale definition is causing the problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
