Thread: ...
Hello All, Does anybody knows where to send bugreport for postgresql? I can't find this information on postgres site. Best regards, Yury ICQ 11831432 mailto:yura@vpcit.ru
helllo! is there a way to set up UNICODE table for databases? Or how to set launguages for certain databases or the whole server. I have asked this before while i was away but the disk on server crashed and i lost all my e-mails, before i had a chance to do anything. Thanks for your answers! Mitja Novak
Hi, How do I create a table from a 'select' statement? I tried the help from psql '\h create table', it does not explain how. I tried: create table blah2 as select pin from tbs_billing_record where date(start_time) between '01-JAN-2000' and '23-JAN-2000' order by pin; I received error saying: ERROR: parser: parse error at or near "order" Why is that? Could someone help me please? Regards, Chai
Chai, Just miss out the order by, it has no meaning in a table definition. ie Create table blah2 as select pin from tbs_billing_record where date(start_time) between '01-JAN-2000' and '23-JAN-2000'; Dave
> Hi, > > How do I create a table from a 'select' statement? > I tried the help from psql '\h create table', > it does not explain how. > > I tried: > > create table blah2 as > select pin from tbs_billing_record > where date(start_time) between '01-JAN-2000' and '23-JAN-2000' > order by pin; SELECT ... INTO... -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
pgsql-bugs@postgresql.org On 2000-01-24, Yury Don mentioned: > Hello All, > > Does anybody knows where to send bugreport for postgresql? I can't > find this information on postgres site. > > > Best regards, > Yury ICQ 11831432 > mailto:yura@vpcit.ru > > > > ************ > > -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Hi, I am using Postgresql 6.5.2 on SuSE 6.2, Pentium III 500, 128 MB RAM. I have a database with 2 tables. Table Detail has 500,000 records. Table Customer has 1,000 records. Both table has customer_number field. The query looks like this: select a.custnum, b.fullname, b.address, a.usages from Detail a, Customer b where a.custnum=b.custnum; I wrote a CGI script with Pg module. When I click 'submit' button on netscape, the postgresql starts selecting. (I can see from 'ps aux'). However, after a few minutes, Netscape will say 'no data', while postgresql is still selecting. How do I overcome this problem? How can I tell netscape not to expire the time, and keep waiting until the data is sent to netscape? I want netscape to wait for my data, no matter how long it takes to 'select'. Is there a way to tell netscape, or do I have to change my script? If I run from psql prompt, the query takes about 12 ~ 15 minutes to finish. Regards, Chai
Hello, On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote: > The query looks like this: > select a.custnum, b.fullname, b.address, a.usages from > Detail a, Customer b > where a.custnum=b.custnum; Have you got any keys setup on these tables? That would help the query a lot. Mail out your table definitions (and the script) and then we can have a look. Regards, Mark. -- Mark Jewiss Knowledge Matters Limited http://www.knowledge.com
Mark Jewiss wrote:
>
> Hello,
>
> On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote:
>
> > The query looks like this:
> > select a.custnum, b.fullname, b.address, a.usages from
> > Detail a, Customer b
> > where a.custnum=b.custnum;
>
> Have you got any keys setup on these tables? That would help the query a
> lot.
>
> Mail out your table definitions (and the script) and then we can have a
> look.
I do have a lot of index combinations to help speed up the query,
however
it still takes too long. Here is the script and the table definitions.
SCRIPT
$sql = "select a.pin, b.first_name, b.last_name,
start_time, duration,
country_code, area_code, phone_number,
a.service_type, total_units
from tbs_billing_record a, ibs_subscriber b
where a.pin=b.pin
and date(start_time) between '$begindate' and '$enddate'
and a.service_type='$service_type'
order by $sortby1,$sortby2";
Pg::doQuery($conn,$sql,\@bilrec);
The values of $begindate, $enddate, $service_type, $sortby1, $sortby2
are given by user, through HTML form using CGI script.
TABLE DEFINITION
create sequence tbs_br_seq
increment 1
minvalue 0000000001
start 1;
create table tbs_billing_record
(
tbs_br_seq int DEFAULT nextval('tbs_br_seq') NOT NULL,
pin varchar(128) NOT NULL,
start_time datetime,
duration integer,
service_code varchar(2),
country_code varchar(3),
area_code varchar(3),
phone_number varchar(24),
service_type varchar(1),
total_units float,
);
create index tbs_br_pin_idx on tbs_billing_record (pin);
create index tbs_br_start_time_idx on tbs_billing_record (start_time);
create index tbs_br_duration_idx on tbs_billing_record (duration);
create index tbs_br_country_code_idx on tbs_billing_record
(country_code);
create index tbs_br_total_units_idx on tbs_billing_record (total_units);
create index tbs_br_pin_start_time_idx on tbs_billing_record (pin,
start_time);
create index tbs_br_pin_duration_idx on tbs_billing_record (pin,
duration);
create index tbs_br_pin_total_units_idx on tbs_billing_record (pin,
total_units);
create index tbs_br_start_time_duration_idx on tbs_billing_record
(start_time, duration);
create index tbs_br_tbsbrseq_idx on tbs_billing_record (tbs_br_seq);
create index tbs_br_pinstarttimeservicetype_idx on tbs_billing_record
(pin,start_time,service_type);
create index tbs_br_starttimeservicetype_idx on tbs_billing_record
(start_time,service_type);
create table ibs_subscriber
(
id integer,
last_name varchar(30),
first_name varchar(20),
mi_name varchar(1),
password varchar(20),
pin varchar(128),
);
create index ibs_subs_id_idx on ibs_subscriber (id);
create index ibs_subs_last_name_idx on ibs_subscriber (last_name);
create index ibs_subs_first_name_idx on ibs_subscriber (first_name);
create index ibs_subs_password_idx on ibs_subscriber (password);
create index ibs_subs_last_first_name_idx on ibs_subscriber (last_name,
first_name);
create index ibs_subs_id_password_idx on ibs_subscriber (id, password);
create index ibs_subs_pin_idx on ibs_subscriber (pin);
create index ibs_subs_pin_flname_idx on ibs_subscriber
(pin,first_name,last_name);
To print out the detail I use this (part of the long script):
foreach $record (@bilrec)
{
($pin,$first_name,$last_name,$start_time,$duration,
$country_code,$area_code,$phone_number,$service_type,
$total_units)=@$record;
$new_duration = ($duration/60);
($front,$back) = split(/\./,$new_duration);
$new_back = substr($back,0,2);
$new_duration = $front.'.'.$new_back;
$no = $no + 1;
$fullname=$first_name.' '.$last_name;
push(@rows,
td([$no,$pin,$fullname,$start_time,center($new_duration),
$country_code,$area_code,$phone_number,
$service_type,
$total_units]));
}
print table
({-border=>'', -cellspacing=>3, -cellpadding=>3},
TR(\@rows)
Regards,
Chai
Hello, On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote: > $sql = "select a.pin, b.first_name, b.last_name, > start_time, duration, > country_code, area_code, phone_number, > a.service_type, total_units > from tbs_billing_record a, ibs_subscriber b > where a.pin=b.pin > and date(start_time) between '$begindate' and '$enddate' > and a.service_type='$service_type' > order by $sortby1,$sortby2"; Ok, I've had a very quick look at this, and going on my past experiences, I'd say that the problem is the query above. I've always had horribly slow query returns when you are only pulling out rows with data between two dates. If you take the 'date between' line out of the query, how much quicker is the result returned? One way around this may be to create a temporary table that contains the results of the query that you want. Your script query would then be "select * from temptable". You'd then need some sort of schedule so that the temp table is updated every XX mins to suit your application - it comes down to working out how important accurate information is to your customer. Regards, Mark. -- Mark Jewiss Knowledge Matters Limited http://www.knowledge.com
Mark Jewiss wrote: > > Hello, > > On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote: > > > $sql = "select a.pin, b.first_name, b.last_name, > > start_time, duration, > > country_code, area_code, phone_number, > > a.service_type, total_units > > from tbs_billing_record a, ibs_subscriber b > > where a.pin=b.pin > > and date(start_time) between '$begindate' and '$enddate' > > and a.service_type='$service_type' > > order by $sortby1,$sortby2"; > > Ok, I've had a very quick look at this, and going on my past experiences, > I'd say that the problem is the query above. I've always had horribly slow > query returns when you are only pulling out rows with data between two > dates. > > If you take the 'date between' line out of the query, how much quicker is > the result returned? > > One way around this may be to create a temporary table that contains the > results of the query that you want. Your script query would then be > "select * from temptable". > > You'd then need some sort of schedule so that the temp table is updated > every XX mins to suit your application - it comes down to working out how > important accurate information is to your customer. That's what I am doing now, however I only put one month data to the temporary table. The problem is when two months data is needed, I have to create another temporary table, and change my script again. I may have to find a way to tell netscape not to timeout too quickly. Do you have other suggestions? Thanks Regards, Chai