[GENERAL] restricting select statements
I'm working with JDBC and if I issue a broad SELECT statement, with a good amount of records (2000) it takes quite a while to execute the query and send the results back, even up to a minute, when I only want about the first 100 records. If I restrict the search, it is satisfied in a much more reasonable amount of time. The problem is that I can only know how big the database. I could do a count and get an idea of how large the database is and make a guess that the data is uniformly distributed. There was a java command to restrict the number of records saved setMaxSize(); but this does not seem to decrease the time of execution of executeQuery(), and probably only uses less local memory. If I have a statement like: SELECT * FROM a; and only want about the first 150 records, is there a way of speeding up the query without doing a count(*) on a and doing something like this: SELECT * FROM a WHERE '' = a AND a 'C'; -- Bob VonMoss mailto:[EMAIL PROTECTED] from Chicago, IL
Re: [GENERAL] restricting select statements
I'm working with JDBC and if I issue a broad SELECT statement, with a good amount of records (2000) it takes quite a while to execute the query and send the results back, even up to a minute, when I only want about the first 100 records. If I restrict the search, it is satisfied in a much more reasonable amount of time. The problem is that I can only know how big the database. I could do a count and get an idea of how large the database is and make a guess that the data is uniformly distributed. There was a java command to restrict the number of records saved setMaxSize(); but this does not seem to decrease the time of execution of executeQuery(), and probably only uses less local memory. have a look at using cursors. worked for me. -- [ Jim MercerReptilian Research [EMAIL PROTECTED] +1 416 410-5633 ] [ The telephone, for those of you who have forgotten, was a commonly used ] [ communications technology in the days before electronic mail.] [ They're still easy to find in most large cities. -- Nathaniel Borenstein ]
[GENERAL] ISP with PostgreSQL
Hi all, Is anybody know an ISP which provided PostgreSQL access ? I'm developping a powerfull solution for Ebusiness using Linux RedHat 5.2 and PostgreSQL 6.4.2. But I can't find any ISP running PostgreSQL, they all works with MySQL. Porting to this database or other, is not so difficult because I use Perl/DBI but I really don't want to. I want PostgreSQL because I need PostgreSQL as a professional solution. Also, I'm a free developper. I do it by myself on my sleep time as most part of you, so I need cheapest services :-)))... Thanks a lot, Gilles Darold
Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL
Jose' Soares wrote: Valerio Santinelli wrote: First of all.. thanks to everybody for helping me out witht the "sequence" stuff. :) I've got another questions for you dudes. When using a CheckBox in a Form written in MSAccess that's related to a table in a PostgreSQL database, if I simply turn its status from ON to OFF it works fine, while if i'm doing the opposite it won't work. I noticed that when exporting the table from MSAccess to PostgreSQL the "yes/no" fields all became char(1) and not boolean.. maybe this could be the problem. I also noticed from the logs that when updating the status from ON to OFF the UPDATE goes out with something like field_name='0' I'm not sure it should use the "'" since it's more like a numerical, no ? Thanks again, Valerio Santinelli [EMAIL PROTECTED] You have to uncheck "Bools as Char" on Advanced options Driver on ODBC Data Source Administrator and then you have to create an operator for bool=int4 like this: /* M$Access tratta il valore booleano come un intero 0=false o -1=true mentre invece PostgreSQL lo tratta come una stringa: 'true','t','1','y','yes','false','f','0','n','no' Questo script crea l'operatore = e implicitamente per bool e int4 per compatibilita' con M$Access. */ drop operator = (bool,int4); drop function MsAccessBool(bool,int4); create function MsAccessBool(bool,int4) returns bool as '' language 'internal'; create operator = ( leftarg=bool, rightarg=int4, procedure=MsAccessBool, commutator='=', negator='', restrict=eqsel, join=eqjoinsel ); Some time ago I got this message and I tried to make booleans work with my database, but there's been no way to do that. First of all, during the "DROP MsAccessBool" my PostgreSQL database engine reports that the function can't be dropped because it's an internal (built-in). Second, it seems to replace it by creating the function (i don't know how it could if the function already exists..) but then again when I'm updating a record in a table with booleans it simply doesn't let me do that. I'm desperately seeking help now.. I don't really know how to solve this problem. Thanks in advance Valerio Santinelli [EMAIL PROTECTED]
RE: [GENERAL] ISP with PostgreSQL
Yea. Check out Epoch Internet, http://www.eni.net, 1-888-77-EPOCH, I think they offer PostgreSQL. You'll probably have to call to find out, I don't remember anything on the web page. DwD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gilles Darold Sent: Wednesday, February 24, 1999 3:53 AM To: [EMAIL PROTECTED] Subject: [GENERAL] ISP with PostgreSQL Hi all, Is anybody know an ISP which provided PostgreSQL access ? I'm developping a powerfull solution for Ebusiness using Linux RedHat 5.2 and PostgreSQL 6.4.2. But I can't find any ISP running PostgreSQL, they all works with MySQL. Porting to this database or other, is not so difficult because I use Perl/DBI but I really don't want to. I want PostgreSQL because I need PostgreSQL as a professional solution. Also, I'm a free developper. I do it by myself on my sleep time as most part of you, so I need cheapest services :-)))... Thanks a lot, Gilles Darold
[GENERAL] How to cast a boolean into an int2 ?
How do I cast a boolean into an int2 ? I want to do something like: INSERT INTO test SELECT id, (demo='t') from temp; but the second field isn't a boolean in the "test" table but an int2. -- C'ya! Valerio Santinelli a.k.a. TANiS [[EMAIL PROTECTED]]+:+[http://www.mediacom.it/~tanis]
[GENERAL] How to Retrieve Just One Record ?
Hi. I'd like to do select * from mytable but just get one record. I don't even care which record. Any ideas ? Thanks. -- The day is short, and the work is great, | Aharon Schkolnik and the laborers are lazy, and the reward| [EMAIL PROTECTED] is great, and the Master of the house is | +972 2 649 1241 impatient. - Ethics Of The Fathers Ch. 2 |
Re: [GENERAL] timestamps
[EMAIL PROTECTED] ha scritto:
I'm trying to create a column that defaults to the current time and date. I
tried the SQLServer like syntax below but potgresql choked:
CREATE TABLE clicks (
avo_userid varchar (10) NOT NULL ,
link_id int NOT NULL ,
the_time timestamp NOT NULL CONSTRAINT df_now DEFAULT (timestamp('now'))
);
NOTICE: there is more than one function named "timestamp"
NOTICE: that satisfies the given argument types. you will have to
NOTICE: retype your query using explicit typecasts.
ERROR: function timestamp(unknown) does not exist
Is "timestamp" not both a type and a function? How can I look it up?
You can create table like:
CREATE TABLE clicks (
useridvarchar(10) not null,
linksintnot null,
df_timetimestamp constraint df_now DEFAULT current_timestamp
);
but remember in such case you can insert a value to df_time column different
than current timestamp.
If you want to avoid this you have to create a trigger (see attached example).
--
And one more question: How does one construct a foreign key relationship in
postgres?
Thanks for any help.
Foreign key is not yet implemented but you may use triggers (See attached
example).
NB: You need v6.4.? to use examples.
--
- Jose' -
"No other success in life can compensate for failure in the home" (David O.
McKay)
DROP TABLE version_test;
CREATE TABLE version_test (
nometext,
usernamechar(10), -- user name
version timestamp -- last update
);
drop function f_version();
create function f_version() returns opaque as '
begin
new.version:= current_timestamp;
new.username:= current_user;
return new;
end;
' language 'plpgsql';
CREATE TRIGGER t_version
BEFORE INSERT OR UPDATE ON version_test
FOR EACH ROW
EXECUTE PROCEDURE f_version();
INSERT INTO version_test VALUES ('jose','marco','1998-12-31 12:30:00');
INSERT INTO version_test VALUES ('miriam');
update version_test set username='jose';
SELECT * FROM version_test;
DROP TABLE header;
CREATE TABLE header (
distretto CHAR(4) NOT NULL,
annoDECIMAL(4) NOT NULL,
numero INTEGER NOT NULL,
dataDATE NOT NULL,
azienda CHAR(11) NOT NULL,
CONSTRAINT k_header PRIMARY KEY (distretto,anno,numero)
);
DROP TABLE detail;
CREATE TABLE detail (
distretto CHAR(4) NOT NULL,
annoDECIMAL(4) NOT NULL,
numero INTEGER NOT NULL,
cod_prestazione CHAR(05) NOT NULL,
quantitaFLOAT(4) NOT NULL,
importo FLOAT(8),
CONSTRAINT k_detail PRIMARY KEY (distretto,anno,numero,cod_prestazione),
CONSTRAINT k_extern FOREIGN KEY(distretto,anno,numero) references HEADER
);
drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
/* to avoid insert detail if header doesn''t exist */
tot int;
begin
select numero into tot from header
where anno = new.anno and numero = new.numero;
if not found then
raise notice ''Impossible add new detail!'';
return NULL;
else
return new;
end if;
end;
' language 'plpgsql';
create trigger t_not_add_detail before insert
on detail for each row execute procedure f_not_add_detail();
--EXAMPLE:
select * from header;
select * from detail;
INSERT INTO detail VALUES ('E14','1999',2,'IMPOSSIBLE',123,345.5); --impossible
INSERT INTO header VALUES ('E14','1999',2,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',2,'AB',523,35.5);
INSERT INTO header VALUES ('E14','1999',1,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',1,'A1',423,45.5);
INSERT INTO detail VALUES ('E14','1999',1,'AC',123,345.5);
select * from header;
select * from detail;
drop function f_upd_key_detail();
create function f_upd_key_detail() returns opaque as '
declare
/* change in cascade the key of every detail if header key is changed */
tot int;
begin
update detail
set anno = new.anno, numero = new.numero
where anno = old.anno and numero = old.numero;
return NULL;
end;
' language 'plpgsql';
drop trigger t_upd_key_detail on header;
create trigger t_upd_key_detail after update
on header for each row execute procedure f_upd_key_detail();
--EXAMPLE:
select * from header;
select * from detail;
update header set anno='1997', numero=33 where numero = 1 and anno='1999';
select * from header;
select * from detail;
drop function f_del_cascade();
create function f_del_cascade() returns opaque as '
declare
/* cancel in cascade all details after header is deleted */
begin
delete from detail
where anno = old.anno and numero =
RE: [GENERAL] Checkboxes on MSAccess and PostgreSQL
I wrote a function that takes a boolean parameter and returns int2. -Original Message- From: Valerio Santinelli [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 24, 1999 1:10 PM To: Michael Davis Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL That's the same thing I did today to get rid of the boolean problem. Now I've got another one ;) I want to use my data contained in the table filled with boolean stuff and I'd like to convert all the booleans to int2. I know there's a way to do that by doing something like: INSERT INTO mynewtable SELECT field1, field2, (booleanfield='t'), field3,... FROM myoldtable; but "(booleanfield='t')" gives another boolean value. I think I should typecast it into an int2 but I don't know how to do that since there isn't a function liek a bool_to_int2. Any ideas ? Michael Davis wrote: I defined my Access boolean fields as int2 in Postgres. This, in combination with removing the "Bools as Char" flag in the ODBC driver appears to work. However, I have not been able to much testing. -Original Message- From: Valerio Santinelli [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 24, 1999 4:25 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL Jose' Soares wrote: Valerio Santinelli wrote: First of all.. thanks to everybody for helping me out witht the "sequence" stuff. :) I've got another questions for you dudes. When using a CheckBox in a Form written in MSAccess that's related to a table in a PostgreSQL database, if I simply turn its status from ON to OFF it works fine, while if i'm doing the opposite it won't work. I noticed that when exporting the table from MSAccess to PostgreSQL the "yes/no" fields all became char(1) and not boolean.. maybe this could be the problem. I also noticed from the logs that when updating the status from ON to OFF the UPDATE goes out with something like field_name='0' I'm not sure it should use the "'" since it's more like a numerical, no ? Thanks again, Valerio Santinelli [EMAIL PROTECTED] You have to uncheck "Bools as Char" on Advanced options Driver on ODBC Data Source Administrator and then you have to create an operator for bool=int4 like this: /* M$Access tratta il valore booleano come un intero 0=false o -1=true mentre invece PostgreSQL lo tratta come una stringa: 'true','t','1','y','yes','false','f','0','n','no' Questo script crea l'operatore = e implicitamente per bool e int4 per compatibilita' con M$Access. */ drop operator = (bool,int4); drop function MsAccessBool(bool,int4); create function MsAccessBool(bool,int4) returns bool as '' language 'internal'; create operator = ( leftarg=bool, rightarg=int4, procedure=MsAccessBool, commutator='=', negator='', restrict=eqsel, join=eqjoinsel ); Some time ago I got this message and I tried to make booleans work with my database, but there's been no way to do that. First of all, during the "DROP MsAccessBool" my PostgreSQL database engine reports that the function can't be dropped because it's an internal (built-in). Second, it seems to replace it by creating the function (i don't know how it could if the function already exists..) but then again when I'm updating a record in a table with booleans it simply doesn't let me do that. I'm
Re: [GENERAL] ISP with PostgreSQL
We offer PostgreSQL services...see http://www.hub.org/prices.html, and
note that all prices are in Canadian Dollars...
On Wed, 24 Feb 1999, Gilles Darold wrote:
Hi all,
Is anybody know an ISP which provided PostgreSQL access ? I'm developping
a
powerfull solution for Ebusiness using Linux RedHat 5.2 and PostgreSQL
6.4.2. But
I can't find any ISP running PostgreSQL, they all works with MySQL.
Porting to this database or other, is not so difficult because I use
Perl/DBI but I really don't
want to. I want PostgreSQL because I need PostgreSQL as a professional
solution.
Also, I'm a free developper. I do it by myself on my sleep time as most
part of you,
so I need cheapest services :-)))...
Thanks a lot,
Gilles Darold
Marc G. Fournier
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[GENERAL] Permission to create Postgres Views
Not yet received anything, so, i repost my question... I'm sure this is a small small question... :-) Thanks in advance to the lightman :-) A+ On Wed, 24 Feb 1999 [EMAIL PROTECTED] wrote: Dear Benjamin, I have recently noticed your posting on the postgres newsgroup. At present, I am a student in England trying to write a database using postgres. I am experiencing exactly the same error that you describe in your posting; (ie) pg_rewrite: Permission denied error when trying to create a view. Did you ever solve this problem? If so, I'd be most grateful if you could let me know how :) Thanks in advance, Timothy Leung Aston University. Cybersalutations. Benjamin Gonay --- _/Email internet : [EMAIL PROTECTED] _/ _/ ~~ Soyons realistes, exigeons l'impossible.(E. Che Guevara) ~~ _/ _/ - - [Belgium - Liege] - - - - - - - - - - - - - - - - - - - - - - - _/ _/ linux forever...Linux Forever...LINUX Forever...LINUX FOREVER ! _/ ---
[GENERAL] character limit on db names in PGSQL or what?
had a db named: prysm_shreveport with one table named 'shreveport' came back later and did: createdb prysm_shreveport2 psql -e prysm_shreveport2 shreveport2.sql -- which creates table 'shreveport2' then: psql prysm_shreveport2 which to my suprise had 2 tables! shreveport, and shreveport2 So I started with: createdb prysm_shreveport3 psql prysm_shreveport3 suprise, suprise, it already has two tables in it, shreveoprt, and shreveport2 so what I can tell createdb prysm_shreveport3 (does in deed create a new database, that I can see by doing: psql then \l to list all dbs) destroydb prysm_shreveport3 will actually delete the database. but to do something like psql prysm_shreveport3 doesn't actually connect me to that database it actually connects me to prysm_shreveport even though my command line looks like this in psql: prysm_shreveport3= even if there is no database listed as prysm_shreveport2 I can do: psql prysm_shreveort2 and it looks and acts as though I'm connected to prysm_shreveport2 when actually it's prysm_shreveport that I'm connected to. I'm thinking an error message somewhere along the line or something would have been nice, and fortunately no data was lost in this process, very easily could have been however. currently running postgres 6.3.x I've got 6.4.x running at home, I was going to try it there too, just to see what happens. I just thought this was strange. Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
