[SQL] eB7E2R
hi
Re: [SQL] identifying duplicates in table with redundancies
Hello again, Tarlika. In what concerns to indices, I 'm affraid I may not be the best person to advise you, my knowledge of them hardly goes beyond the most trivial cases. I'm sure there are plenty of other people on the list who are able to give you better advise than me. But, on this query in particular I would recomend an indice on trainer_name, as this field will be used on the join and on the group by. For the other query, the one you get by substituting trainer_name by trainer_id, place an index on trainer_id. Also, these indexes may help speed up the order by clause, if you use one. If you have a table with lots of data you can try them around and see how performance varies (and don't forget there's also EXPLAIN ANALYZE) Bear in mind, though, that this is just my 2 cents on a matter that I don't really master. Do not take this as an expert answer. There are many people on this list that can help you better Best, Oliveiros - Original Message - From: "Tarlika Elisabeth Schmitz" To: Sent: Monday, September 27, 2010 5:54 PM Subject: Re: [SQL] identifying duplicates in table with redundancies On Fri, 24 Sep 2010 18:12:18 +0100 Oliver d'Azevedo Christina wrote: SELECT DISTINCT trainer_id,trainer_name FROM ( SELECT trainer_name -- The field you want to test for duplicates FROM ( SELECT DISTINCT "trainer_id","trainer_name" FROM student ) x GROUP BY "trainer_name"-- the field you want to test for duplicates HAVING (COUNT(*) > 1) ) z NATURAL JOIN student y What indices would you recommend for this operation? -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] is there a distinct function for comma lists ?
Hi, > And as bonus ... is there a way to find IDs that are in the list but not > in the table without creating a temporary table and use a join? Does below satisfy you? select * from (values (1), (2), (3), (5), (7), (11), (3), (6), (13), (13), (3), (11)) as X(a) where a not in(select id from mytable) Serge http://www.sql-ex.com/ > Hi, > For the problem 1 perhaps something like > select distinct unnest(ARRAY[ 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ]) > Regards, > Andreas > -Ursprüngliche Nachricht- > Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > Im Auftrag von Andreas > Gesendet: Dienstag, 7. September 2010 13:52 > An: pgsql-sql@postgresql.org > Betreff: [SQL] is there a distinct function for comma lists ? > Hi, > is there a distinct function for comma separated lists ? > I sometimes need to update tables where I got a set of IDs, like: > update mytable > set someattribute = 42 > where mytable.id in > ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... ) > So there are double entries in the list but in this case its just > overhead but no problem. > But for calculated values this would not allways be desirable. > update mytable > set someattribute = someattribute + 1 > where mytable.id in > ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... ) > How could I get a distinct list? Those lists can have 2000-3000 IDs > sometimes. > One solution was as follows but perhaps there is something more elegant? > update mytable > set someattribute = someattribute + 1 > where mytable.id in > ( select distinct id from mytable where id in ( 1, 2, 3, 5, 7, 11, 3, > 6, 13, 13, 3, 11 ... ) ) > And as bonus ... is there a way to find IDs that are in the list but not > in the table without creating a temporary table and use a join? > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] identifying duplicates in table with redundancies
On Tue, 28 Sep 2010 11:34:31 +0100 "Oliveiros d'Azevedo Cristina" wrote: >- Original Message - >From: "Tarlika Elisabeth Schmitz" >To: >Sent: Monday, September 27, 2010 5:54 PM >Subject: Re: [SQL] identifying duplicates in table with redundancies > > >> On Fri, 24 Sep 2010 18:12:18 +0100 >> Oliver d'Azevedo Christina wrote: >> > SELECT DISTINCT trainer_id,trainer_name > FROM ( > SELECT trainer_name -- The field you want to test for duplicates > FROM ( > SELECT DISTINCT "trainer_id","trainer_name" > FROM student > ) x > GROUP BY "trainer_name"-- the field you want to test for > duplicates > HAVING (COUNT(*) > 1) > ) z > NATURAL JOIN student y >> >> >> >> What indices would you recommend for this operation? > >But, on this query in particular I would recomend an indice on >trainer_name, as this field will be used on the join and on the group >by. For the other query, the one you get by substituting trainer_name >by trainer_id, place an index on trainer_id. >Also, these indexes may help speed up the order by clause, if you use >one. > >If you have a table with lots of data you can try them around and see >how performance varies (and don't forget there's also EXPLAIN ANALYZE) Strangely, these indices did not do anything. Without, the query took about 8500ms. Same with index. The table has 25 records. 11000 have trainer_name = null. Only 13000 unique trainer_names. It is not hugely important as these queries are not time-critical. This is only a helper table, which I use to analyze the date prior to populating the destination tables with the data. Regards, Tarlika -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Shema with template
Dear List, I have created a database with the template postgis. In this database a shema named public was created, with the functions of postgis and two tables (geometry_columns and spatial_ref_sys). Now, I would like to create one shema like this for every village. How can I add a shema to a existing database by using a template? Thank you for your help. Salutation from Switzerland Andres Thomas zChuchichäschtli
