[BUGS] how to copy data from excel spreadsheet to postgresql database
Martin Kuria ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description how to copy data from excel spreadsheet to postgresql database Long Description I have enter data in excel spreadsheet, and I would like to enter or copy this data in my postgresql database, please do give a example, thanks best regards martin. Sample Code explain more on how how a copy command is used, please illustrate with an example No file was uploaded with this report
Re: [BUGS] psql wraps lines at 256 characters
> the v7.0 psql wraps output lines at 256 characters. The 6.5 version did not. I >sometimes create huge xterms so I can get the output of a select all on one line per >row,and keep the display nicely formatted, but now I just get a big void of emptyness >on the right 3rd of my xterm, because psql is wrapping the output anyway. (Note the irony with wrapping here...) Of course output is normally wrapped at the end of the terminal. The decision about that is in the terminal driver's configuration, not psql. It might be that your pager (more/less) got in the way. If I write the output to a text file I can create lines much longer than 256 characters, which seems to support that claim of blame. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[BUGS] Inexplicably running wild and freezing with 7.0
Julian Coutts ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description Inexplicably running wild and freezing with 7.0 Long Description Running on a HP lpr 192RAM 600mz pIII computer. Running 7.0 (have been a big fan of 6.5.3 which was/is rock solid) Ran okay for about a week then would randomly do one of several things. 1. used up all the available cpu time and hung in the middle of a query never returning. The postmaster is what would eat up the CPU 2. about 1 time out of 10 would claim that the backend wasn't running and if you were in the DB directly and not using some object to connect to it it would give you "!" prompt and claim that the backend wasn't working. You would then have to quit the app and re-login. You wouldn't need to restart the postmaster and logging back in show no ill signs ... until about 10 queries later. 3. The computer's cpu would get eaten up by the klogd (daemon) and the machine would be slow as hell. 4. Would spawn additonal postmasters that would still be running after killing the one that was eating up the CPU. Very strange. other notes -I couldn't dump the db's after we decided to move it back a version. -this may be unrelated: At one point we decided to restart the machine it was on. The entire DB file disappeared including all DB's. (I had a back up) -two sys admins and 6 developers couldn't figure out a pattern with the problem. Inconsistent but the problem got worse as time went on. -Vaccum didn't help. -Regressing on the same machine to 6.5.3 has been rock solid ever since which kinda rules out a hardware problem. -unfortunatly it kinda scared my boss who wants to move my db over to ORACLE (uh! so bloated). I think that the 6.5.3 version is restoreing his confidence in it. Hope this helps you awesome postgres people. Julian Sample Code No file was uploaded with this report
[BUGS] very poorly optimised query
Orion Henry ([EMAIL PROTECTED]) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
very poorly optimised query
Long Description
This is for Postgres 7.0.1 running on x86 linux
While usually impressed with postgres's speed I ran into
a query that ran very very slowly. The simplified version of
the query is
select count(*) from foo where groupid in (select distinct groupid from foo);
It runs at exactly O(n*n). Theres were the times I experienced
running it on linux with an Athlon 700.
1000 rows 0.6 seconds
5000 rows 14.8 seconds
1 rows 59.6 seconds
I wrote an equivlent query that uses a temporary table
and all querys run well under a second. See example code.
I also noted that indexing the groupid makes no difference in time.
Sample Code
drop table foo;
drop sequence fooseq;
create table foo ( groupid int4 );
create sequence fooseq;
insert into foo values (nextval('fooseq'::text));
-- repeate this insert 10,00 times
-- this query is O(n*n);
select count(*) from foo where groupid in (select distinct groupid from foo);
-- this query produces the same results at O(n) or O(n*log(n))
-- cant tell too fast
select distinct groupid into temp table tmp from foo;
select count(*) from foo a, tmp b where a.groupid = b.groupid;
No file was uploaded with this report
[BUGS] Subselects lack functionality
Kuba Ober ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description Subselects lack functionality Long Description 1. Subselects don't allow one to use tuple set operators like UNION, INTERSECT, EXCEPT. It forces one to select the results into a temp table. 2. It would be gr8 if single column subselects would allow themselves to be treated the same way as lists do as right-side arguments of IN operator. This would allow e.g. much more powerful DELETE statement, like DELETE FROM table WHERE id IN (SELECT ...) 3. Subselects cannot be used as arguments to aggregation functions - I presume this is very weird and not expected at all, but I had such an idea. I don't know if it is expected behaviour (standards?), this should be explained in the docs (or am I searching in the wrong place?) Sample Code select (select id from table1 except select table1.id where expression) as id; (assuming that subselect would result just one tuple) No file was uploaded with this report
Re: [BUGS] Subselects lack functionality
[EMAIL PROTECTED] wrote: > 2. > It would be gr8 if single column subselects would allow themselves to be treated the >same way as lists do as right-side arguments of IN operator. > This would allow e.g. much more powerful DELETE statement, like > DELETE FROM table WHERE id IN (SELECT ...) This, AFAIK, works. I am using just that construct in UPDATE -- I would assume DELETE would work... Lessee...This works, as I just tested it on a client's database (inside a transaction, with a rollback at the end, of course). You say something like DELETE from users WHERE uid IN (SELECT uid FROM users WHERE username ~* 'testing') to delete (of course, I know that that is a contrived example, but a working one). More complex is DELETE from personal_data WHERE uid IN (SELECT uid FROM users WHERE username ~* 'testing') -- which works just fine on a client's database (again, inside a transaction so I could easily roll back the delete :-)). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [BUGS] Subselects lack functionality
[EMAIL PROTECTED] writes: > Subselects don't allow one to use tuple set operators like UNION, > INTERSECT, EXCEPT. Yeah, we know. The existing implementation of UNION etc. is a horrid kluge that only works at the top level of a SELECT (and not even very well there). Fixing this will require a redesign of querytrees, which is currently planned for 7.2. > It would be gr8 if single column subselects would allow themselves to > be treated the same way as lists do as right-side arguments of IN > operator. As Lamar pointed out, this works now. > Subselects cannot be used as arguments to aggregation functions You have that backwards. You don't do the select as an argument to the aggregate, you use it as a context for the aggregate, eg ... (SELECT max(foo) FROM bar WHERE baz) ... regards, tom lane
[BUGS] /contrib/soundex doesn't work. Version 7.0.2
Edmar Wiggers ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description /contrib/soundex doesn't work. Version 7.0.2 Long Description The code in /contrib/soundex doesn't work. It does compile though. I create the function in the database, but it always returns nothing (blank)! It should be a small stupid mistake in the source code. I bet you can fix it within 5 minutes (as opposed to me, who would take about 5 hours). If you can look into it, please drop me a line. Sample Code No file was uploaded with this report
