[GENERAL] The closest match
I'm doing a db layout in Logic Works' Erwin. It can generate SQL statements to create the database. Of course it doesn't include support for PostgreSQL (yet) but I wondered which syntax was closest of the supported ones. These are supported: DB2 SQL Server Rdb Oracle SQLBase Watcom/SQL Anywhere Ingres Sybase AS/400 Informix Progress Teradata Red Brick InterBase Also these client type "db"'s Clipper FoxPro dBASE III dBASE IV Access Paradox I've tried Access (too few data types) and Oracle (Ugh. Everything in capitals). Ingres seems to be close, but...?
[GENERAL] Dome
Anybody that knows anything about Dome ( http://www.htc.honeywell.com/dome/ ) ? It seems to be a quite extensive CASE tool. Can I use it to build a Database layout and let it generate sql statements to create the database??
Fw: [GENERAL] uppercase of char16
Please help - Original Message - From: Safa Pilavcı <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: 06 Ağustos 1999 Cuma 14:48 Subject: [GENERAL] uppercase of char16 > hello , > My problem is with SQL statement, > I have a field like, > my_fieldchar16 > > this field contains charecters all lowercase but I want to get them uppercase as a > result of select statement.. > I have tried ; > select upper(my_field) from my_table; > but the followwing error occured. > > function upper(char16) does not exist > > How can I get uppercase output of my_field > > > Safa Pilavcı > > >
Re: [GENERAL] Tr: DUMP database for sample
--Referential integrity: --1. Don't allow to add a detail without header --2. Delete all details in cascade if one decide to delete the header --3. Update details' key in cascade if header's key is changed DROP TABLE header; CREATE TABLE header ( year INTEGER NOT NULL, number INTEGER NOT NULL, date DATE NOT NULL, cod_client CHAR(4) NOT NULL, CONSTRAINT k_header PRIMARY KEY (year,number) ); DROP TABLE detail; CREATE TABLE detail ( year INTEGER NOT NULL, number INTEGER NOT NULL, cod_product CHAR(05) NOT NULL, qty INTEGER NOT NULL, cost DECIMAL(8,2), CONSTRAINT k_detail PRIMARY KEY (year,number,cod_product), CONSTRAINT k_extern FOREIGN KEY(year,number) 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 number into tot from header where year = new.year and number = new.number; if not found then raise notice ''I cannot add a detail without header!''; return NULL; else return new; end if; end; ' language 'plpgsql'; drop function f_del_detail(); create function f_del_detail() returns opaque as ' begin -- Delete details in cascade... delete from detail where detail.year = old.year and detail.number = old.number; return new; end; ' language 'plpgsql'; drop function f_upd_detail(); create function f_upd_detail() returns opaque as ' begin -- Updates details keys in cascade... update detail set year = new.year, number = new.number where detail.year = old.year and detail.number = old.number; return new; end; ' language 'plpgsql'; create trigger t_ins_after before INSERT on detail for each row execute procedure f_not_add_detail(); create trigger t_del_after after DELETE on detail for each row execute procedure f_not_add_detail(); create trigger t_del_after after DELETE on header for each row execute procedure f_del_detail(); create trigger t_upd_after after UPDATE on header for each row execute procedure f_upd_detail(); insert into header values(1999,321,current_date,'C128'); insert into detail values(1999,321,'B139',2,200.35); insert into header values(1997,132,current_date,'C500'); insert into detail values(1997,132,'B166',3,120.50); select * from header; select * from detail; update header set year=1998 where year=1999; select * from header; select * from detail; delete from header where year=1998; select * from header; select * from detail; insert into detail values(1999,321,'B139',2,200.35); --results: - insert into header values(1999,321,current_date,'C128'); INSERT 365317 1 insert into detail values(1999,321,'B139',2,200.35); INSERT 365318 1 insert into header values(1997,132,current_date,'C500'); INSERT 365319 1 insert into detail values(1997,132,'B166',3,120.50); INSERT 365320 1 select * from header; year|number|date |cod_client +--+--+-- 1999| 321|10/08/1999|C128 1997| 132|10/08/1999|C500 (2 rows) select * from detail; year|number|cod_product|qty| cost +--+---+---+-- 1999| 321|B139 | 2|200.35 1997| 132|B166 | 3|120.50 (2 rows) update header set year=1998 where year=1999; UPDATE 1 select * from header; year|number|date |cod_client +--+--+-- 1997| 132|10/08/1999|C500 1998| 321|10/08/1999|C128 (2 rows) select * from detail; year|number|cod_product|qty| cost +--+---+---+-- 1997| 132|B166 | 3|120.50 1998| 321|B139 | 2|200.35 (2 rows) delete from header where year=1998; DELETE 1 select * from header; year|number|date |cod_client +--+--+-- 1997| 132|10/08/1999|C500 (1 row) select * from detail; year|number|cod_product|qty| cost +--+---+---+-- 1997| 132|B166 | 3|120.50 (1 row) insert into detail values(1999,321,'B139',2,200.35); NOTICE: I cannot add a detail without header! INSERT 0 0 José Stéphane FILLON ha scritto: -Message d'origine- De : Stéphane FILLON <[EMAIL PROTECTED]> À : [EMAIL PROTECTED] <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date : dimanche 1 août 1999 09:13 Objet : Tr: DUMP database for sample > > >>Hi ! >> >>I am trying a big commercial database application with PostgreSQL and I >>encounter some problems seach as how to create a foreign key constraint.. >> >>I would be very nice if someone could send me a dump of a real database >>application with TRIGGER / FUNCTION / TYPE / FOREIGN KEY AND PRIMARY KEY. >> >>I think that it would b
Re: Fw: [GENERAL] uppercase of char16
At 14:30 +0300 on 10/08/1999, =?iso-8859-9?Q?Safa_Pilavc=FD?= wrote: > Please help Char16 has little support. Any possibility of changing the definition to char(16)? Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Re: Fw: [GENERAL] uppercase of char16
Hmm, char16 is not a currently recognized type - what version of postgresql are you using? In 6.5, upper(foo) works for all the char and text types of foo I can find. Ross On Tue, Aug 10, 1999 at 02:30:54PM +0300, Safa Pilavcý wrote: > Please help > > > - Original Message - > From: Safa Pilavc? <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: 06 A?ustos 1999 Cuma 14:48 > Subject: [GENERAL] uppercase of char16 > > > > hello , > > My problem is with SQL statement, > > I have a field like, > > my_fieldchar16 > > > > this field contains charecters all lowercase but I want to get them > uppercase as a > > result of select statement.. > > I have tried ; > > select upper(my_field) from my_table; > > but the followwing error occured. > > > > function upper(char16) does not exist > > > > How can I get uppercase output of my_field > > > > > > Safa Pilavc? > > > > > > > -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
RE: [GENERAL] Backend cache problem revisited
You need to increase your max backends. The default for 6.4.2 was 64. I believe it is a configure option (or it's in config.h). You might also need to increase your semaphore limits (that's a kernel option). > -Original Message- > From: Yin-So Chen [SMTP:[EMAIL PROTECTED]] > Sent: Sunday, August 08, 1999 12:11 AM > To: [EMAIL PROTECTED] > Subject: [GENERAL] Backend cache problem revisited > > Hi, > > after reading through the archives it seems that this particular error > > NOTICE: SIAssignBackendId: discarding tag 2147483553 > Connection to database failed > FATAL 1: Backend cache invalidation initialization failed > > had not come up for a while for most people. However I didn't see any > postings about a solution either, so I tried to figure out if this > problem went away after the 6.3.2 build. On 6.3.2 this error occurs > after there are more than 32 concurrent connections. On 6.4.2 the > number of concurrent connections allowed doubled to 64, but it occurs > again if you create more. My system environment has dual PII450, 1GB > RAM, and I am running RedHat5.2 with Apache1.3.6 & mod_perl & > Postgresql6.3.2 (all RedHat builds except for the kernel). I use > mod_perl to keep the database connection persistent. With 1GB of ram I > can hold at least 100 connections open at the same time, and I would > really like to be able to utilize my computer's resources fully. > > Can anyone tell me how to reconfigure postgresql so I can avoid this > error when opening more connections? Any help is greatly appreciated. > > Regards, > > Yin-So Chen
[GENERAL] repost: unique composite index with boolean fields
Hello there, Could anybody advise me how can I create composite index for table when one of key fields is of boolean type? Schema is as follows: create table test (indoor bool, pos int, name varchar(64) NOT NULL, check (pos >= 0 and pos <=32000)); create unique index test_pkey on test (indoor, pos); leads to error ERROR: Can't find a default operator class for type 16. Finally, I use char_ops as type class: create unique index test_pkey on test (indoor char_ops, pos); -- is it correct? Sincerely, D.Marck [DM5020, DM268-RIPE, DM3-RIPN] *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- [EMAIL PROTECTED] ***
Re: [GENERAL] ALTER TABLE
On Mon, 9 Aug 1999, Jonathan R. Karlen wrote: JRK> Is there a way to drop a field from a table? ALTER TABLE seems to only JRK> allow the adding and modification of fields. Try to select all content to temporary table, then drop old table, create new with unneeded column(s) removed, and them reselect all data back. Also, don't forget to re-create indexes, rules, views and other table-related things as they are reference table by its (internal to Postgres) ID. Also don't forget to create needed permissions. For large tables, dumping, editing schema & data with simple script and then recreating database may be simpler solution... Sincerely, D.Marck [DM5020, DM268-RIPE, DM3-RIPN] *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- [EMAIL PROTECTED] ***
[GENERAL] Selecting between dates
Hi, I am creating a script that sends out reminders to subscribers to a site who have not paid. I can't figure out the math that will add 7 days to a date. I want something like this: select * from company where created is between (lastpaid + 14 days) and (created + 28 days); I can't figure out the syntax that will let me add days to a date. I checked the archives and the Postgresql doc's, but nothing. Ideas? Thanks, Wim Kerkhoff.
[GENERAL] anomalous, persistent high loads with postgresql 6.5.1
I've searched the list archives and read the FAQ, neither of which seem to describe my current situation: I'm running Linux 2.2.10 on a production webserver, along with PostgreSQL 6.5.1 (in RPM format from ftp.postgresql.org). My main database is a set of names/addresses/etc. tables for our membership, about 60,000 records apiece, with indexes in all the right places. Whenever I run any sort of involved query, the postmaster process kicks into CPU-eating mode; even after I've made sure no more queries running (including the original), postmaster/postgres processes keep the system at a load average of 3 and rising; the only way it seems to subside is if I kill and restart postmaster, which is obviously a problem ^_^;; Is this a weird OS/dbms/query combination bug, or am I just doing something horribly wrong?
Re: [GENERAL] Selecting between dates
hi wim.. >select * from company where created >is between (lastpaid + 14 days) and (created + 28 days); assuming that lastpaid and created are dates, pgsql will handle the addition quite nicely for you... as examples: test=> select '4-1-1999'::date + 7; ?column? -- 04-08-1999 (1 row) test=> select '4-26-1999'::date + 7; ?column? -- 05-03-1999 test=> select '12-26-1999'::date + 7; ?column? -- 01-02-2000 (1 row) Aaron J. Seigo Systems Analyst/Administrator
Re: [GENERAL] Selecting between dates
Thanks Aaron, That is exactly what I wanted. Much more simple then what I was thinking. On 10-Aug-99 Aaron Seigo wrote: > hi wim.. > >>select * from company where created >>is between (lastpaid + 14 days) and (created + 28 days); > > assuming that lastpaid and created are dates, pgsql will handle the > addition quite nicely for you... as examples: > > test=> select '4-1-1999'::date + 7; > ?column? > -- > 04-08-1999 > (1 row) > > test=> select '4-26-1999'::date + 7; > ?column? > -- > 05-03-1999 > > test=> select '12-26-1999'::date + 7; > ?column? > -- > 01-02-2000 > (1 row) > Regards, --- Wim Kerkhoff [EMAIL PROTECTED] www.canadianhomes.net/wim ICQ: 23284586
Re: [GENERAL] anomalous, persistent high loads with postgresql 6.5.1
Could you please post the schema and query? Also, I'm assuming you VACUUM on a regular basis? Mike M. --- Bill Garrett <[EMAIL PROTECTED]> wrote: > I've searched the list archives and read the FAQ, > neither of which seem to > describe my current situation: > > I'm running Linux 2.2.10 on a production webserver, > along with PostgreSQL > 6.5.1 (in RPM format from ftp.postgresql.org). My > main database is a set > of names/addresses/etc. tables for our membership, > about 60,000 records > apiece, with indexes in all the right places. > Whenever I run any sort of > involved query, the postmaster process kicks into > CPU-eating mode; even > after I've made sure no more queries running > (including the original), > postmaster/postgres processes keep the system at a > load average of 3 and > rising; the only way it seems to subside is if I > kill and restart > postmaster, which is obviously a problem ^_^;; > > Is this a weird OS/dbms/query combination bug, or am > I just doing > something horribly wrong? > > > _ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
