[GENERAL] Deleting all but one row of a list of non-uniques
Hi all I have a table, for simplicity's sake containing one field, called unid. for example, select unid, oid from table gives me something like this: unid | oid -+- XNO24ORK | 40276607 XNPGJDPU | 40276673 * XNPGJDPU | 40276674 * XNXAAVQ2 | 40277583 ZAZAFAA4 | 40276600 ZAZV5UG4 | 40276446 ZD66A1LL | 40277162 ZDXZ27RS | 40277454 ZEKRT3GM | 40277739 * ZEKRT3GM | 40277740 * ZEKRT3GM | 40277741 * (I've marked the rows with duplicated unids with the * to the right) I'd like to delete rows in such a way that one (and any one) row for each unid remains, and all other duplicates are removed. Does anyone have any ideas that may help me here please? slecting distinct is not helpful as in reality there are other fields which contain data like timestamps that will differ but I don't mind which one remains, but I'd need to work with them on selects to the table afterwards. I've tried: delete from table where oid in (select p1.oid from table p1, table p2 where p1.oid != p2.oid and p1.unid=p2.unid); which only works in a few cases - I suspect where there are only two rows with the same unid. Is it even possible? Alternatively, can I get the \copy command to ignore attempts to insert duplicated rows into a UNIQUE column instead of aborting the copy? Not sure if any of the options that can be supplied to the table at creation time for unique will help here. Thanks. Ciao Zak -- http://www.carfolio.com/Searchable database of 10 000+ car specs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] count(1) return 0?
On Mon, 01 Mar 2004 13:12:12 +1100 Klint Gore [EMAIL PROTECTED] wrote: The counting and grouping is done after the where clause is applied. since player iplaybadly (who was 1200's opponent) didnt win any, he/she is not included in the result set to be grouped and counted. You need to get iplaybadly into the result set first. try something like select fixture, home, sum(case winner=home then 1 else 0 end) from results group by fixture, home Ah, thanks, works a charm. Thanks too to all who offered help on this one. Ciao Zak -- http://www.carfolio.com/Searchable database of 10 000+ car specs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] count(1) return 0?
On Mon, 01 Mar 2004 12:13:07 +1100 Klint Gore [EMAIL PROTECTED] wrote: On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor [EMAIL PROTECTED] wrote: to return a 0 value instead of absolutely nothing if no rows match fixture=4916 and winner=away? I get absolutely no results at all. any ideas please? dont group by winner. it's not returned in the statement so it's not needed anyway. an exact value is specified in the where clause so it's not going to be different either. Thanks Klint, that works. I will need to group by fixture though (not winner - that was just one of the permutations that I was playing around with) at some point which presents the same problem. I have a number of fixtures in the results table which need to be aggregated to tally frames won per player per fixture (stored as a view) and those results further aggregated to find points for a points table. All is good until a player has won no frames in a fixture, at which point the fact that an empty result is returned becomes problematic. For instance: select fixture, count(1) as total, away from results where winner=away group by fixture, away; fixture | total | away -+---+-- 4913 | 4 | 1214 4916 | 9 | 1200 4918 | 7 | 1123 4928 | 9 | 1318 4935 | 5 | 1265 (5 rows) select fixture, count(1) as total, home from results where winner=home group by fixture, home; fixture | total | home -+---+-- 4913 | 9 | 1198 4918 | 9 | 1257 4928 | 1 | 1401 4935 | 9 | 1359 (4 rows) in fixture 4916, player 1200 won 9-0, but his opponent does not show as having 0 in the second result set. I suspect what I am expecting is somehow illogical, but I can't see why. I would expect to see a total of 0 for the case where winner=away and fixture=4916 = the fixtures are after all being grouped - if there are zero cases where fixture=4916 and winner=away, as far as I can see that should be the count() result... however as I said I am probably missing something quite basic. If anyone could please explain why what I expect to see is not what I actually see I would be very grateful indeed. Thanks Ciao Zak -- http://www.carfolio.com/Searchable database of 10 000+ car specs ---(end of broadcast)--- TIP 3: 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] Large OR query
Hi all If I have say 1000 values for an ID field, what is the best way to select from a table all the corresponding records? I have tried select * from blah where id in (id1,id2,id3...id1000) and select * from blah where id=id1 or id=id2 ... or id=id1000 and both are pretty slow. Is there a better way to do this please? Thanks Ciao -- Zak McGregorhttp://www.carfolio.com - Over 7000 car specs online Web mercenary - currently for hire. Perl/html/.js/sql/cgi/GNUlinux/php + - Trying to make bits uncopyable is like trying to make water not wet. The sooner people accept this, and build business models that take this into account, the sooner people will start making money again. -- Bruce Schneier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Queries across multiple databases (was: SELECT from a table in another database).
On Mon, 21 May 2001 07:55:13 -0400 Andrew Rawnsley [EMAIL PROTECTED] wrote: If I ever come up with said schema-dropping patch, and anyone else wants it, let me know. I'd dance a happy jig ;-) I'm not sure whether it is quite the way to do it, but I'd have a better time with things if I could span databases in a single request. Are there theoretical problems with spanning databases in a single query? Is it a feature of bad database design implementation? Thanks Ciao Zak -- Zak McGregor http://www.carfolio.com - Specifications of cars online. Over 7000! Of course my password is the same as my pet's name. My macaw's name was Q47pY!3, but I change it every 90 days. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] formatting a date
On Thu, 10 May 2001 15:30:01 -0400 Fran Fabrizio [EMAIL PROTECTED] wrote: I'm looking all over the place in the Pg docs and Momjian book and having no luck finding any functions that would turn a timestamp such as 2001-05-08 23:59:59-04 into May 8, 2001. (i.e. do what date_format() was doing for me in MySQL.) Is there equivalent functionality in Pg? You want to look at to_char(). To get May 10, 2001 you'd need to write something like: select to_char(now(), 'Month dd, '); It pads the output of the month to 9 places, btw. See http://postgresql.readysetnet.com/users-lounge/docs/7.1/user/functions-formatting.html (watch for potential wrapping of the link) for more info. -- Zak McGregor http://www.carfolio.com - Specifications of cars online. Over 7000! Of course my password is the same as my pet's name. My macaw's name was Q47pY!3, but I change it every 90 days. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Data transfer format between UNIX server and Windows client?
On Thu, 05 Apr 2001 09:25:28 -0500 "Paul A. Lender" [EMAIL PROTECTED] wrote: MY QUESTION -- When query results are sent to the client, where does the conversion of stored binary data to easily readable text take place: on the server before it leaves for the clients, or at libpq.dll ON the clients? I would say at the server, no question (Tom, others, please whack me with a cluestick if I'm wrong here...). Not that you can assume that binary data is safe from prying eyes either... If the transfer between boxes is text, does anyone have experience using an encrypted connection between a Unix box and a Windows client (without using a web server, that is)? Try and tunnel through ssh. Not sure about the MS box, but the *BSD box will handle it fine. http://www.cs.stanford.edu/security/tunnel.html (just one example) I have found putty to be a good Win32 ssh client. Search for putty.exe at http://www.google.com HTH Cheers ---(end of broadcast)--- TIP 3: 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] Views, indices and pg_dump
Hi all Thanks to everyone for this great forum, and for Postgres! I have a couple of questions: I have a smallish table which I join on another (smaller) table. I have created a view for this purpose, thinking it would save on overhead. The view also incorporates some calculated fields. However, when I do an EXPLAIN SELECT * FROM my_view it shows sequential scans on both tables and no use of any indices (I've created them on the view and the two other tables). It seems the view does the join on the fly. If so, will using a view speed up queries? Also, how do I make it use the indices for queries? Another, slightly unrelated question: Why does pg_dump dump the view as a normal table? Thanks so much for your help Cheers Zak ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] How to auto create a unique key for each newly added record?
On Mon, 26 Mar 2001 21:20:13 -0500 "Jeff" [EMAIL PROTECTED] wrote: Hi, How to if it is possible to let postgresql to auto assign unique key to each new record that is added to the database? Just set up the table with the field you are wanting to have as a unique id as type "serial". eg: create table auto_id (id serial, description varchar(32)); Cheers Zak ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] How to copy a table?
On Mon, 26 Mar 2001 21:21:12 -0500 "Jeff" [EMAIL PROTECTED] wrote: Hi, Can some one show me what's the best way to backup a table or copy a table? Backing up is simple with pg_dump and pg_dumpall. Copying a table (assuming within the same database) is also easy: create table test as select * from other_table; HTH Zak ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Joins and field types
Hi all What sort of performance penalty on joins using varchar(n) vs. int fields can I expect if both tables are fairly small (ie unlikely to have more than 2 rows each)? Is there a good reference or two on these sorts of questions? thanks a lot Ciao Zak McGregor
