Re: [GENERAL] Referential integrity
Try using triggers: (see attached example) José Pablo Sentis ha scritto: Part 1.1Type: Plain Text (text/plain) Encoding: quoted-printable --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 ( yearinteger NOT NULL, number INTEGER NOT NULL, dateDATE NOT NULL, cod_client CHAR(4) NOT NULL, CONSTRAINT k_header PRIMARY KEY (year,number) ); DROP TABLE detail; CREATE TABLE detail ( yearinteger NOT NULL, number INTEGER NOT NULL, cod_product CHAR(05) NOT NULL, qty INTEGER NOT NULL, costDECIMAL(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 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);
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 Stphane FILLON ha scritto: -Message d'origine- De : Stphane FILLON [EMAIL PROTECTED]> : [EMAIL PROTECTED] [EMAIL PROTECTED]>; [EMAIL PROTECTED] [EMAIL PROTECTED]> Date : dimanche 1 aot 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 be nice if we could make a documentation with a real >>example. If you are interested I can make it during my build. >> >> >>Best Regards, >> >>A special thanks for the PostgreSQL's team,, KEEP THE GOOD JOB. >> >>Stephane. >> >
Re: [GENERAL] int8-datatype: possible bug
Jelle Ruttenberg ha scritto:
Hello all,
when I try to put a numeric 'timestamp' like 19980528112607 into
an
int8-datatype I get the notice 'NOTICE: Integer input '19980528112607'
is out of range; promoted to float' and the value is put in the
database
as 9223091508918288644.
It doesn't matter if I change the value: it will allways be the
magical
value 9223091508918288644.
When I use the float8-datatype it all goes well.
Is this a bug in PostgreSQL or has it something to do with
platform-specific limitations?
Thanks,
Jelle.
PostgreSQL: 6.4.2
Platform: SGI IRIX 6.2. IP22
--
NEROC Publishing Solutions
Jelle Ruttenberg
De Run 1131, 5503 LB Veldhoven Phone : +31-(0)40-2586641
P.O.Box 133, 5500 AC Veldhoven Fax
: +31-(0)40-2541893
The Netherlands
E-mail : [EMAIL PROTECTED]
--
Let me try on v6.5beta1...
prova=> create table test(i8 int8);
CREATE
prova=> insert into test values(19980528112607 );
NOTICE: Integer input '19980528112607' is out of range; promoted
to float
INSERT 145481 1
prova=> insert into test values('19980528112607' );
INSERT 145482 1
prova=> insert into test values('19980528112607'::int8);
INSERT 145483 1
prova=> select * from test;
i8
--
19980528112607
19980528112607
19980528112607
(3 rows)
Seems that works fine. And now trying bounds...
prova=> insert into test values(-9223372036854775808);
NOTICE: Integer input '9223372036854775808' is out of range;
promoted to float
ERROR: Floating point conversion to int64 is out of range
prova=> insert into test values('-9223372036854775808');
INSERT 145486 1
prova=> insert into test values(9223372036854775807);
NOTICE: Integer input '9223372036854775807' is out of range;
promoted to float
ERROR: Floating point conversion to int64 is out of range
prova=> insert into test values('9223372036854775807');
INSERT 145487 1
prova=> select * from test;
i8
19980528112607
19980528112607
19980528112607
-9223372036854775808
9223372036854775807
(5 rows)
It works if the data is enclosed with ''. :)
prova=> select version();
version
---
PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1
(1 row)
Jose'
Re: [GENERAL] int8-datatype: possible bug
Seems that your platform has problems with int8.
Here the begining of ../contrib/int8/README from Thomas G. Lockhart. Try to
ask him :)
---
Thomas G. Lockhart [EMAIL PROTECTED]
This is a first attempt at 64-bit integer arithmetic for Postgres. The code
should support any 64-bit architecture and any 32-bit machine using a recent
GNU C compiler. At the moment, DEC-Alpha and Linux/gcc are explicitly
supported. The code uses "long long int" support in GNU C on 32-bit machines.
This type is an extension to ANSI-C, and may not appear on any other compiler.
The modules are built and installed as user-defined types, so destination
directories are pointing away from the standard Postgres areas.
Other compilers and architectures should be supportable, so please let me know
what changes were required to run on your machine, and I will fold those into
this standard distribution.
--
Jelle Ruttenberg ha scritto:
Hello José Soares,
thank you. I tried your testcase and all input was changed in the magical
value. The 'NOTICE' disappeared when I quoted the value, but the result
was all the same.
edahrecepten= create table test(i8 int8);
CREATE
edahrecepten= insert into test values(19980528112607 );
NOTICE: Integer input '19980528112607' is out of range; promoted to float
INSERT 147055 1
edahrecepten= select * from test;
i8
---
9223091440198811892
(1 row)
edahrecepten= insert into test values('19980528112607' );
INSERT 147056 1
edahrecepten= select * from test;
i8
---
9223091440198811892
9223091440198811892
(2 rows)
edahrecepten= insert into test values('19980528112607'::int8);
INSERT 147057 1
edahrecepten= select * from test;
i8
---
9223091440198811892
9223091440198811892
9223091440198811892
(3 rows)
edahrecepten= select version();
version
PostgreSQL 6.4.2 on mips-sgi-irix6.2, compiled by cc
(1 row)
So for the time being I use float8...
Jelle.
Jelle Ruttenberg ha scritto:
Hello all,
when I try to put a numeric 'timestamp' like 19980528112607 into an
int8-datatype I get the notice 'NOTICE: Integer input '19980528112607'
is out of range; promoted to float' and the value is put in the database
as 9223091508918288644.
It doesn't matter if I change the value: it will allways be the magical
value 9223091508918288644.
When I use the float8-datatype it all goes well.
Is this a bug in PostgreSQL or has it something to do with
platform-specific limitations?
Thanks,
Jelle.
PostgreSQL: 6.4.2
Platform: SGI IRIX 6.2. IP22
--
NEROC Publishing Solutions
Jelle Ruttenberg
De Run 1131, 5503 LB VeldhovenPhone : +31-(0)40-2586641
P.O.Box 133, 5500 AC VeldhovenFax: +31-(0)40-2541893
The Netherlands E-mail : [EMAIL PROTECTED]
--
Let me try on v6.5beta1...
prova= create table test(i8 int8);
CREATE
prova= insert into test values(19980528112607 );
NOTICE: Integer input '19980528112607' is out of range; promoted to float
INSERT 145481 1
prova= insert into test values('19980528112607' );
INSERT 145482 1
prova= insert into test values('19980528112607'::int8);
INSERT 145483 1
prova= select * from test;
i8
--
19980528112607
19980528112607
19980528112607
(3 rows)
Seems that works fine. And now trying bounds...
prova= insert into test values(-9223372036854775808);
NOTICE: Integer input '9223372036854775808' is out of range; promoted to
float
ERROR: Floating point conversion to int64 is out of range
prova= insert into test values('-9223372036854775808');
INSERT 145486 1
prova= insert into test values(9223372036854775807);
NOTICE: Integer input '9223372036854775807' is out of range; promoted to
float
ERROR: Floating point conversion to int64 is out of range
prova= insert into test values('9223372036854775807');
INSERT 145487 1
prova= select * from test;
i8
19980528112607
19980528112607
19980528112607
-9223372036854775808
9223372036854775807
(5 rows)
It works if the data is enclosed with ''. :)
prova= select version();
version
---
PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1
(1 row)
Jose'
--
NEROC Publishing Solutions
Jelle Ruttenberg
De Run 1131, 5503 LB VeldhovenPhone : +31-(0)40-2586641
P.O.Box
Re: [GENERAL] index on an int8 column
It works on 6.5beta1. Sarah Officer ha scritto: I have a table with a column of type int8. When I try to create an index on it, the database protests and gives the following error message: ERROR: Can't find a default operator class for type 20. Is there an easy fix for this? I assumed builtin numeric types would have default comparison functions. Also, if I am not posting to the appropriate mailing list, please let me know. Thanks in advance, Sarah Officer [EMAIL PROTECTED] __ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^ Jose'
Re: [GENERAL] LIMIT QUESTION
Martin Wong ha scritto: Sorry for the previous posting. The following worked. BTW, this affects just this database or throughout the entire postgresql server? Only current_session; And, how does one reset this variable to max? RESET QUERY_LIMIT; __ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^ Jose'
Re: [GENERAL] Object-oriented stuff and postgres
--retrieve column information... SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'comuni' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY attnum ; attnum|attname |typname|attlen|atttypmod|attnotnull|atthasdef --+--+---+--+-+--+- 1|istat |bpchar | -1| 10|t |f 2|nome |bpchar | -1| 54|t |f 3|provincia |bpchar | -1| 6|f |f 4|codice_fiscale|bpchar | -1| 8|f |f 5|cap |bpchar | -1| 9|f |f 6|regione |bpchar | -1| 7|f |f 7|distretto |bpchar | -1| 8|f |f (7 rows) Jos Chris Bitmead ha scritto: What's the best way to do this in postgres? (basicly finding the type of objects). I want to run a web site with different types of content - question and answers, stories etc. I propose an object hierarchy... webobject (title, body) question inherits webobject story (image) inherits (webobject). The idea being you could have a search screen that searches questions AND stories with the one SELECT query. But then each result would have a link to examine the body of the search result. But different types of objects would have different URLs to display that content. So basicly I need to know the type of objects returned. I am loath to store the object type inside the object because it is wasteful. PG obviously already knows the type of objects, the question is how to get at that info.
Re: [GENERAL] Re: [HACKERS] Gregorian Calendar
Hi Thomas, Thomas Lockhart ha scritto: I have a question about dates. The Gregorian reform of calendar skiped 10 days on Oct, 1582. This reform was accepted by Great Britain and Dominions (including what is now the USA) only in 1752. If I insert a date that doesn't exist PostgreSQL accepts it. Should it be considered normal ? As Peter says, this is tricky. Date conventions before the 19th century make for interesting reading, but are not imho consistant enough to warrant coding into a date/time handler. As you probably have noticed, we use Julian date calculations for our date/time support. I suppose you refer to Julian Day invented by the French scholar Joseph Justus Scaliger (1540-1609) that probably takes its name from the Scaliger's father, the Italian scholar Julius Caesar Scaliger (1484-1558). Astronomers have used the Julian period to assign a unique number to every day since 1 January 4713 BC. This is the so-called Julian Day (JD). JD 0 designates the 24 hours from noon UTC on 1 January 4713 BC to noon UTC on 2 January 4713 BC. Julian Day is different from Julian Date The Julian calendar was introduced by Julius Caesar in 45 BC. It was in common use until the 1582, when countries started changing to the Gregorian calendar. In the Julian calendar, the tropical year is approximated as 365 1/4 days = 365.25 days. This gives an error of 1 day in approximately 128 and this is why pope Gregory XIII in accordance with instructions from the Council of Trent reformed the calendar to correct this error. In the Gregorian calendar, the tropical year is approximated as 365 + 97 / 400 days = 365.2425 days. Thus it takes approximately 3300 years for the tropical year to shift one day with respect to the Gregorian calendar. The approximation 365+97/400 is achieved by having 97 leap years every 400 years. The Gregorian calendar has 97 leap years every 400 years: Every year divisible by 4 is a leap year. However, every year divisible by 100 is not a leap year. However, every year divisible by 400 is a leap year after all. So, 1700, 1800, 1900, 2100, and 2200 are not leap years. But 1600, 2000, and 2400 are leap years. instead in the Julian calendar only years divisible by 4 are leap years. The papal bull of February 1582 decreed that 10 days should be dropped from October 1582 so that 15 October should follow immediately after 4 October. This was observed in Italy, Poland, Portugal, and Spain. Other Catholic countries followed shortly after, but Protestant countries were reluctant to change, and the Greek orthodox countries didn't change until the start of this century. The reform was observed by Great Britain and Dominions (including what is now the USA) in 1752. The 2 Sep 1752 was followed by 14 Sep 1752. This is why unix has the cal 9 1752 like this: September 1752 S M Tu W Th F S 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 My question is: If SQL92 says: (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992 5.3 literals 22)Within the definition of a datetime literal, the datetime values are constrained by the natural rules for dates and times according to the Gregorian calendar. ^^^ Dates between 1752-09-03 and 1752-09-13. Are they valid dates? They have the nice property of correctly predicting/calculating any date more recent than something like 4013BC to far into the future, using the assumption that the length of the year is 365.25 days. This is a very recently adopted convention (sometime in the 1800s I had thought, but perhaps it was during the same "reform" in 1752). I've toyed with the idea of implementing a Chinese dynastic calendar, since it seems to be more predictable than historical European calendars. People's Republic of China uses the Gregorian calendar for civil purposes. Chinese calendar is used for determining festivals. The beginnings of the Chinese calendar can be traced back to the 14th century BC. Legend has it that the Emperor Huangdi invented the calendar in 2637 B José
[GENERAL] Gregorian Calendar
Hi all,
I have a question about dates.
The Gregorian reform of calendar skiped 10 days on Oct, 1582.
This reform was accepted by Great Britain and Dominions (including what
is now the USA)
only in 1752.
This is way we have Sep, 1752 like this:
$ cal 9 1752
September 1752
S M Tu W Th F S
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
If I insert a date that doesn't exist PostgreSQL accepts it.
Should it be considered normal ?
prova= insert into test values('1752-09-10');
INSERT 403408 1
prova= select * from test;
date
--
1752-09-10
(1 rows)
José
