Обсуждение: Notes on Postgresql 7.0 on FreeBSD
============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Rolf Grossmann Your email address : grossman@securitas.net System Configuration --------------------- Architecture (example: Intel Pentium) : AMD-K6 300 Operating System (example: Linux 2.0.26 ELF) : FreeBSD 3.4-STABLE PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0beta1 Compiler used (example: gcc 2.8.0) : gcc 2.95 Please enter a FULL description of your problem: ------------------------------------------------ Actually, I have found 2 platform specific problems: 1. miscadmin.h needs to include sys/types.h for a definition of pid_t This patch fixes the problem. *** miscadmin.h.orig Wed Feb 23 14:31:08 2000 --- miscadmin.h Tue Feb 22 18:27:48 2000 *************** *** 24,27 **** --- 24,29 ---- #define MISCADMIN_H + #include <sys/types.h> /* For pid_t */ + #include "postgres.h" 2. Regression tests fail for types int2 and int4 (which can easily be fixed by adding entries to resultmap) aswell as float8 and geometry, where floating point numbers appear to be rounded a little differently than in your expected results (besides that I also need the positive zeros file). I'm including a patch for the first 2, but I don't know whether the latter two are actually a bug in postgres or a bug in the OS or even allowed difference. I'm including my results for reference. *** resultmap.orig Wed Feb 23 14:33:19 2000 --- resultmap Wed Feb 23 13:20:06 2000 *************** *** 3,6 **** --- 3,8 ---- int2/.*-netbsd=int2-too-large int4/.*-netbsd=int4-too-large + int2/.*-freebsd=int2-too-large + int4/.*-freebsd=int4-too-large int2/i.86-pc-linux-gnulibc=int2-not-representable int4/i.86-pc-linux-gnulibc=int4-not-representable *************** *** 9,12 **** --- 11,15 ---- geometry/hppa=geometry-positive-zeros geometry/.*-netbsd=geometry-positive-zeros + geometry/.*-freebsd=geometry-positive-zeros geometry/i.86-.*-gnulibc=geometry-i86-gnulibc geometry/sparc-sun-solaris=geometry-solaris-precision Thanks for your attention and keep up the good work. Bye, Rolf
Applied. > ============================================================================ > POSTGRESQL BUG REPORT TEMPLATE > ============================================================================ > > > Your name : Rolf Grossmann > Your email address : grossman@securitas.net > > > System Configuration > --------------------- > Architecture (example: Intel Pentium) : AMD-K6 300 > > Operating System (example: Linux 2.0.26 ELF) : FreeBSD 3.4-STABLE > > PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0beta1 > > Compiler used (example: gcc 2.8.0) : gcc 2.95 > > > Please enter a FULL description of your problem: > ------------------------------------------------ > > Actually, I have found 2 platform specific problems: > > 1. miscadmin.h needs to include sys/types.h for a definition of pid_t > > This patch fixes the problem. > > *** miscadmin.h.orig Wed Feb 23 14:31:08 2000 > --- miscadmin.h Tue Feb 22 18:27:48 2000 > *************** > *** 24,27 **** > --- 24,29 ---- > #define MISCADMIN_H > > + #include <sys/types.h> /* For pid_t */ > + > #include "postgres.h" > > 2. Regression tests fail for types int2 and int4 (which can easily be > fixed by adding entries to resultmap) aswell as float8 and geometry, > where floating point numbers appear to be rounded a little differently > than in your expected results (besides that I also need the positive > zeros file). I'm including a patch for the first 2, but I don't know > whether the latter two are actually a bug in postgres or a bug in the > OS or even allowed difference. I'm including my results for reference. > > *** resultmap.orig Wed Feb 23 14:33:19 2000 > --- resultmap Wed Feb 23 13:20:06 2000 > *************** > *** 3,6 **** > --- 3,8 ---- > int2/.*-netbsd=int2-too-large > int4/.*-netbsd=int4-too-large > + int2/.*-freebsd=int2-too-large > + int4/.*-freebsd=int4-too-large > int2/i.86-pc-linux-gnulibc=int2-not-representable > int4/i.86-pc-linux-gnulibc=int4-not-representable > *************** > *** 9,12 **** > --- 11,15 ---- > geometry/hppa=geometry-positive-zeros > geometry/.*-netbsd=geometry-positive-zeros > + geometry/.*-freebsd=geometry-positive-zeros > geometry/i.86-.*-gnulibc=geometry-i86-gnulibc > geometry/sparc-sun-solaris=geometry-solaris-precision > > Thanks for your attention and keep up the good work. > > Bye, Rolf > > ************ > > -- 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
Hi, I wrote > [...] but I don't know > whether the latter two are actually a bug in postgres or a bug in the > OS or even allowed difference. I'm including my results for reference. Looks like I forgot to include the other two files, sorry. Here they are. Bye, Rolf -- -- FLOAT8 -- CREATE TABLE FLOAT8_TBL(f1 float8); INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); SELECT '' AS five, FLOAT8_TBL.*; five | f1 ------+---------------------- | 0 | 1004.3 | -34.84 | 1.2345678901234e+200 | 1.2345678901234e-200 (5 rows) SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; four | f1 ------+---------------------- | 0 | -34.84 | 1.2345678901234e+200 | 1.2345678901234e-200 (4 rows) SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; one | f1 -----+-------- | 1004.3 (1 row) SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; three | f1 -------+---------------------- | 0 | -34.84 | 1.2345678901234e-200 (3 rows) SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; three | f1 -------+---------------------- | 0 | -34.84 | 1.2345678901234e-200 (3 rows) SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; four | f1 ------+---------------------- | 0 | 1004.3 | -34.84 | 1.2345678901234e-200 (4 rows) SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; four | f1 ------+---------------------- | 0 | 1004.3 | -34.84 | 1.2345678901234e-200 (4 rows) SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; three | f1 | x -------+----------------------+----------------------- | 1004.3 | -10043 | 1.2345678901234e+200 | -1.2345678901234e+201 | 1.2345678901234e-200 | -1.2345678901234e-199 (3 rows) SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; three | f1 | x -------+----------------------+---------------------- | 1004.3 | 994.3 | 1.2345678901234e+200 | 1.2345678901234e+200 | 1.2345678901234e-200 | -10 (3 rows) SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; three | f1 | x -------+----------------------+----------------------- | 1004.3 | -100.43 | 1.2345678901234e+200 | -1.2345678901234e+199 | 1.2345678901234e-200 | -1.2345678901234e-201 (3 rows) SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; three | f1 | x -------+----------------------+---------------------- | 1004.3 | 1014.3 | 1.2345678901234e+200 | 1.2345678901234e+200 | 1.2345678901234e-200 | 10 (3 rows) SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 FROM FLOAT8_TBL f where f.f1 = '1004.3'; one | square_f1 -----+------------ | 1008618.49 (1 row) -- absolute value SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT8_TBL f; five | f1 | abs_f1 ------+----------------------+---------------------- | 0 | 0 | 1004.3 | 1004.3 | -34.84 | 34.84 | 1.2345678901234e+200 | 1.2345678901234e+200 | 1.2345678901234e-200 | 1.2345678901234e-200 (5 rows) -- truncate SELECT '' AS five, f.f1, %f.f1 AS trunc_f1 FROM FLOAT8_TBL f; five | f1 | trunc_f1 ------+----------------------+---------------------- | 0 | 0 | 1004.3 | 1004 | -34.84 | -34 | 1.2345678901234e+200 | 1.2345678901234e+200 | 1.2345678901234e-200 | 0 (5 rows) -- round SELECT '' AS five, f.f1, f.f1 % AS round_f1 FROM FLOAT8_TBL f; five | f1 | round_f1 ------+----------------------+---------------------- | 0 | 0 | 1004.3 | 1004 | -34.84 | -35 | 1.2345678901234e+200 | 1.2345678901234e+200 | 1.2345678901234e-200 | 0 (5 rows) -- square root SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; three | f1 | sqrt_f1 -------+----------------------+----------------------- | 1004.3 | 31.6906926399535 | 1.2345678901234e+200 | 1.11111110611109e+100 | 1.2345678901234e-200 | 1.11111110611109e-100 (3 rows) -- take exp of ln(f.f1) SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1 FROM FLOAT8_TBL f WHERE f.f1 > '0.0'; three | f1 | exp_ln_f1 -------+----------------------+----------------------- | 1004.3 | 1004.3 | 1.2345678901234e+200 | 1.23456789012338e+200 | 1.2345678901234e-200 | 1.23456789012339e-200 (3 rows) -- cube root SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; five | f1 | cbrt_f1 ------+----------------------+---------------------- | 0 | 0 | 1004.3 | 10.014312837827 | -34.84 | -3.26607421344208 | 1.2345678901234e+200 | 4.97933859234765e+66 | 1.2345678901234e-200 | 2.3112042409018e-67 (5 rows) SELECT '' AS five, FLOAT8_TBL.*; five | f1 ------+---------------------- | 0 | 1004.3 | -34.84 | 1.2345678901234e+200 | 1.2345678901234e-200 (5 rows) UPDATE FLOAT8_TBL SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 > '0.0'; SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by zero SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; ERROR: pow() result is out of range SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ; ERROR: can't take log of zero SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ; ERROR: can't take log of a negative number SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f; ERROR: exp() result is out of range SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; ERROR: float8div: divide by zero error SELECT '' AS five, FLOAT8_TBL.*; five | f1 ------+----------------------- | 0 | -34.84 | -1004.3 | -1.2345678901234e+200 | -1.2345678901234e-200 (5 rows) -- test for over and under flow INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); ERROR: Input '10e400' is out of range for float8 INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); ERROR: Input '-10e400' is out of range for float8 INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); -- maintain external table consistency across platforms -- delete all values and reinsert well-behaved ones DELETE FROM FLOAT8_TBL; INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); SELECT '' AS five, FLOAT8_TBL.*; five | f1 ------+----------------------- | 0 | -34.84 | -1004.3 | -1.2345678901234e+200 | -1.2345678901234e-200 (5 rows) -- -- GEOMETRY -- -- -- Points -- SELECT '' AS four, center(f1) AS center FROM BOX_TBL; four | center ------+--------- | (1,1) | (2,2) | (2.5,3) | (3,3) (4 rows) SELECT '' AS four, (@@ f1) AS center FROM BOX_TBL; four | center ------+--------- | (1,1) | (2,2) | (2.5,3) | (3,3) (4 rows) SELECT '' AS six, point(f1) AS center FROM CIRCLE_TBL; six | center -----+----------- | (0,0) | (1,2) | (1,3) | (1,2) | (100,200) | (100,0) (6 rows) SELECT '' AS six, (@@ f1) AS center FROM CIRCLE_TBL; six | center -----+----------- | (0,0) | (1,2) | (1,3) | (1,2) | (100,200) | (100,0) (6 rows) SELECT '' AS two, (@@ f1) AS center FROM POLYGON_TBL WHERE (# f1) > 2; two | center -----+------------------------------------- | (1.33333333333333,1.33333333333333) | (2.33333333333333,1.33333333333333) (2 rows) -- "is horizontal" function SELECT '' AS two, p1.f1 FROM POINT_TBL p1 WHERE ishorizontal(p1.f1, point '(0,0)'); two | f1 -----+--------- | (0,0) | (-10,0) (2 rows) -- "is horizontal" operator SELECT '' AS two, p1.f1 FROM POINT_TBL p1 WHERE p1.f1 ?- point '(0,0)'; two | f1 -----+--------- | (0,0) | (-10,0) (2 rows) -- "is vertical" function SELECT '' AS one, p1.f1 FROM POINT_TBL p1 WHERE isvertical(p1.f1, point '(5.1,34.5)'); one | f1 -----+------------ | (5.1,34.5) (1 row) -- "is vertical" operator SELECT '' AS one, p1.f1 FROM POINT_TBL p1 WHERE p1.f1 ?| point '(5.1,34.5)'; one | f1 -----+------------ | (5.1,34.5) (1 row) -- -- Line segments -- -- intersection SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection FROM LSEG_TBL l, POINT_TBL p; ERROR: Unable to identify an operator '#' for types 'lseg' and 'point' You will have to retype this query using an explicit cast -- closest point SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest FROM LSEG_TBL l, POINT_TBL p; thirty | f1 | s | closest --------+------------+-------------------------------+--------------------------------------- | (0,0) | [(1,2),(3,4)] | (1,2) | (-10,0) | [(1,2),(3,4)] | (1,2) | (-3,4) | [(1,2),(3,4)] | (1,2) | (5.1,34.5) | [(1,2),(3,4)] | (3,4) | (-5,-12) | [(1,2),(3,4)] | (1,2) | (10,10) | [(1,2),(3,4)] | (3,4) | (0,0) | [(0,0),(6,6)] | (0,0) | (-10,0) | [(0,0),(6,6)] | (0,0) | (-3,4) | [(0,0),(6,6)] | (0.5,0.5) | (5.1,34.5) | [(0,0),(6,6)] | (6,6) | (-5,-12) | [(0,0),(6,6)] | (0,0) | (10,10) | [(0,0),(6,6)] | (6,6) | (0,0) | [(10,-10),(-3,-4)] | (-2.04878048780488,-4.4390243902439) | (-10,0) | [(10,-10),(-3,-4)] | (-3,-4) | (-3,4) | [(10,-10),(-3,-4)] | (-3,-4) | (5.1,34.5) | [(10,-10),(-3,-4)] | (-3,-4) | (-5,-12) | [(10,-10),(-3,-4)] | (-1.60487804878049,-4.64390243902439) | (10,10) | [(10,-10),(-3,-4)] | (2.39024390243902,-6.48780487804878) | (0,0) | [(-1000000,200),(300000,-40)] | (0.0028402365895872,15.384614860264) | (-10,0) | [(-1000000,200),(300000,-40)] | (-9.99715942258202,15.3864610140472) | (-3,4) | [(-1000000,200),(300000,-40)] | (-2.99789812267519,15.3851688427303) | (5.1,34.5) | [(-1000000,200),(300000,-40)] | (5.09647083221496,15.3836744976925) | (-5,-12) | [(-1000000,200),(300000,-40)] | (-4.99494420845634,15.3855375281616) | (10,10) | [(-1000000,200),(300000,-40)] | (10.000993741978,15.3827690473092) | (0,0) | [(11,22),(33,44)] | (11,22) | (-10,0) | [(11,22),(33,44)] | (11,22) | (-3,4) | [(11,22),(33,44)] | (11,22) | (5.1,34.5) | [(11,22),(33,44)] | (14.3,25.3) | (-5,-12) | [(11,22),(33,44)] | (11,22) | (10,10) | [(11,22),(33,44)] | (11,22) (30 rows) -- -- Lines -- -- -- Boxes -- SELECT '' as six, box(f1) AS box FROM CIRCLE_TBL; six | box -----+---------------------------------------------------------------------------- | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) (6 rows) -- translation SELECT '' AS twentyfour, b.f1 + p.f1 AS translation FROM BOX_TBL b, POINT_TBL p; twentyfour | translation ------------+------------------------- | (2,2),(0,0) | (3,3),(1,1) | (2.5,3.5),(2.5,2.5) | (3,3),(3,3) | (-8,2),(-10,0) | (-7,3),(-9,1) | (-7.5,3.5),(-7.5,2.5) | (-7,3),(-7,3) | (-1,6),(-3,4) | (0,7),(-2,5) | (-0.5,7.5),(-0.5,6.5) | (0,7),(0,7) | (7.1,36.5),(5.1,34.5) | (8.1,37.5),(6.1,35.5) | (7.6,38),(7.6,37) | (8.1,37.5),(8.1,37.5) | (-3,-10),(-5,-12) | (-2,-9),(-4,-11) | (-2.5,-8.5),(-2.5,-9.5) | (-2,-9),(-2,-9) | (12,12),(10,10) | (13,13),(11,11) | (12.5,13.5),(12.5,12.5) | (13,13),(13,13) (24 rows) SELECT '' AS twentyfour, b.f1 - p.f1 AS translation FROM BOX_TBL b, POINT_TBL p; twentyfour | translation ------------+--------------------------- | (2,2),(0,0) | (3,3),(1,1) | (2.5,3.5),(2.5,2.5) | (3,3),(3,3) | (12,2),(10,0) | (13,3),(11,1) | (12.5,3.5),(12.5,2.5) | (13,3),(13,3) | (5,-2),(3,-4) | (6,-1),(4,-3) | (5.5,-0.5),(5.5,-1.5) | (6,-1),(6,-1) | (-3.1,-32.5),(-5.1,-34.5) | (-2.1,-31.5),(-4.1,-33.5) | (-2.6,-31),(-2.6,-32) | (-2.1,-31.5),(-2.1,-31.5) | (7,14),(5,12) | (8,15),(6,13) | (7.5,15.5),(7.5,14.5) | (8,15),(8,15) | (-8,-8),(-10,-10) | (-7,-7),(-9,-9) | (-7.5,-6.5),(-7.5,-7.5) | (-7,-7),(-7,-7) (24 rows) -- scaling and rotation SELECT '' AS twentyfour, b.f1 * p.f1 AS rotation FROM BOX_TBL b, POINT_TBL p; twentyfour | rotation ------------+----------------------------- | (0,0),(0,0) | (0,0),(0,0) | (0,0),(0,0) | (0,0),(0,0) | (0,0),(-20,-20) | (-10,-10),(-30,-30) | (-25,-25),(-25,-35) | (-30,-30),(-30,-30) | (0,2),(-14,0) | (-7,3),(-21,1) | (-17.5,2.5),(-21.5,-0.5) | (-21,3),(-21,3) | (0,79.2),(-58.8,0) | (-29.4,118.8),(-88.2,39.6) | (-73.5,104.1),(-108,99) | (-88.2,118.8),(-88.2,118.8) | (14,0),(0,-34) | (21,-17),(7,-51) | (29.5,-42.5),(17.5,-47.5) | (21,-51),(21,-51) | (0,40),(0,0) | (0,60),(0,20) | (0,60),(-10,50) | (0,60),(0,60) (24 rows) SELECT '' AS twenty, b.f1 / p.f1 AS rotation FROM BOX_TBL b, POINT_TBL p WHERE (p.f1 <-> point '(0,0)') >= 1; twenty | rotation --------+----------------------------------------------------------------------------------- | (0,0),(-0.2,-0.2) | (-0.1,-0.1),(-0.3,-0.3) | (-0.25,-0.25),(-0.25,-0.35) | (-0.3,-0.3),(-0.3,-0.3) | (0.08,0),(0,-0.56) | (0.12,-0.28),(0.04,-0.84) | (0.26,-0.7),(0.1,-0.82) | (0.12,-0.84),(0.12,-0.84) | (0.0651176557643925,0),(0,-0.0483449262493217) | (0.0976764836465887,-0.0241724631246608),(0.0325588278821962,-0.0725173893739825) | (0.109762715208919,-0.0562379754328844),(0.0813970697054906,-0.0604311578116521) | (0.0976764836465887,-0.0725173893739825),(0.0976764836465887,-0.0725173893739825) | (0,0.0828402366863905),(-0.201183431952663,0) | (-0.100591715976331,0.124260355029586),(-0.301775147928994,0.0414201183431953) | (-0.251479289940828,0.103550295857988),(-0.322485207100592,0.0739644970414201) | (-0.301775147928994,0.124260355029586),(-0.301775147928994,0.124260355029586) | (0.2,0),(0,0) | (0.3,0),(0.1,0) | (0.3,0.05),(0.25,0) | (0.3,0),(0.3,0) (20 rows) -- -- Paths -- SET geqo TO 'off'; SELECT '' AS eight, points(f1) AS npoints, f1 AS path FROM PATH_TBL; eight | npoints | path -------+---------+--------------------------- | 2 | [(1,2),(3,4)] | 2 | ((1,2),(3,4)) | 4 | [(0,0),(3,0),(4,5),(1,6)] | 2 | ((1,2),(3,4)) | 2 | ((1,2),(3,4)) | 2 | [(1,2),(3,4)] | 2 | [(11,12),(13,14)] | 2 | ((11,12),(13,14)) (8 rows) SELECT '' AS four, path(f1) FROM POLYGON_TBL; four | path ------+--------------------- | ((2,0),(2,4),(0,0)) | ((3,1),(3,3),(1,0)) | ((0,0)) | ((0,1),(0,1)) (4 rows) -- translation SELECT '' AS eight, p1.f1 + point '(10,10)' AS dist_add FROM PATH_TBL p1; eight | dist_add -------+----------------------------------- | [(11,12),(13,14)] | ((11,12),(13,14)) | [(10,10),(13,10),(14,15),(11,16)] | ((11,12),(13,14)) | ((11,12),(13,14)) | [(11,12),(13,14)] | [(21,22),(23,24)] | ((21,22),(23,24)) (8 rows) -- scaling and rotation SELECT '' AS eight, p1.f1 * point '(2,-1)' AS dist_mul FROM PATH_TBL p1; eight | dist_mul -------+------------------------------ | [(4,3),(10,5)] | ((4,3),(10,5)) | [(0,0),(6,-3),(13,6),(8,11)] | ((4,3),(10,5)) | ((4,3),(10,5)) | [(4,3),(10,5)] | [(34,13),(40,15)] | ((34,13),(40,15)) (8 rows) RESET geqo; -- -- Polygons -- -- containment SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 ~ p.f1 AS contains FROM POLYGON_TBL poly, POINT_TBL p; twentyfour | f1 | f1 | contains ------------+------------+---------------------+---------- | (0,0) | ((2,0),(2,4),(0,0)) | t | (-10,0) | ((2,0),(2,4),(0,0)) | f | (-3,4) | ((2,0),(2,4),(0,0)) | f | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f | (-5,-12) | ((2,0),(2,4),(0,0)) | f | (10,10) | ((2,0),(2,4),(0,0)) | f | (0,0) | ((3,1),(3,3),(1,0)) | f | (-10,0) | ((3,1),(3,3),(1,0)) | f | (-3,4) | ((3,1),(3,3),(1,0)) | f | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f | (-5,-12) | ((3,1),(3,3),(1,0)) | f | (10,10) | ((3,1),(3,3),(1,0)) | f | (0,0) | ((0,0)) | t | (-10,0) | ((0,0)) | f | (-3,4) | ((0,0)) | f | (5.1,34.5) | ((0,0)) | f | (-5,-12) | ((0,0)) | f | (10,10) | ((0,0)) | f | (0,0) | ((0,1),(0,1)) | f | (-10,0) | ((0,1),(0,1)) | f | (-3,4) | ((0,1),(0,1)) | f | (5.1,34.5) | ((0,1),(0,1)) | f | (-5,-12) | ((0,1),(0,1)) | f | (10,10) | ((0,1),(0,1)) | f (24 rows) SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 @ poly.f1 AS contained FROM POLYGON_TBL poly, POINT_TBL p; twentyfour | f1 | f1 | contained ------------+------------+---------------------+----------- | (0,0) | ((2,0),(2,4),(0,0)) | t | (-10,0) | ((2,0),(2,4),(0,0)) | f | (-3,4) | ((2,0),(2,4),(0,0)) | f | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f | (-5,-12) | ((2,0),(2,4),(0,0)) | f | (10,10) | ((2,0),(2,4),(0,0)) | f | (0,0) | ((3,1),(3,3),(1,0)) | f | (-10,0) | ((3,1),(3,3),(1,0)) | f | (-3,4) | ((3,1),(3,3),(1,0)) | f | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f | (-5,-12) | ((3,1),(3,3),(1,0)) | f | (10,10) | ((3,1),(3,3),(1,0)) | f | (0,0) | ((0,0)) | t | (-10,0) | ((0,0)) | f | (-3,4) | ((0,0)) | f | (5.1,34.5) | ((0,0)) | f | (-5,-12) | ((0,0)) | f | (10,10) | ((0,0)) | f | (0,0) | ((0,1),(0,1)) | f | (-10,0) | ((0,1),(0,1)) | f | (-3,4) | ((0,1),(0,1)) | f | (5.1,34.5) | ((0,1),(0,1)) | f | (-5,-12) | ((0,1),(0,1)) | f | (10,10) | ((0,1),(0,1)) | f (24 rows) SELECT '' AS four, points(f1) AS npoints, f1 AS polygon FROM POLYGON_TBL; four | npoints | polygon ------+---------+--------------------- | 3 | ((2,0),(2,4),(0,0)) | 3 | ((3,1),(3,3),(1,0)) | 1 | ((0,0)) | 2 | ((0,1),(0,1)) (4 rows) SELECT '' AS four, polygon(f1) FROM BOX_TBL; four | polygon ------+------------------------------------------- | ((0,0),(0,2),(2,2),(2,0)) | ((1,1),(1,3),(3,3),(3,1)) | ((2.5,2.5),(2.5,3.5),(2.5,3.5),(2.5,2.5)) | ((3,3),(3,3),(3,3),(3,3)) (4 rows) SELECT '' AS four, polygon(f1) FROM PATH_TBL WHERE isclosed(f1); four | polygon ------+------------------- | ((1,2),(3,4)) | ((1,2),(3,4)) | ((1,2),(3,4)) | ((11,12),(13,14)) (4 rows) SELECT '' AS four, f1 AS open_path, polygon( pclose(f1)) AS polygon FROM PATH_TBL WHERE isopen(f1); four | open_path | polygon ------+---------------------------+--------------------------- | [(1,2),(3,4)] | ((1,2),(3,4)) | [(0,0),(3,0),(4,5),(1,6)] | ((0,0),(3,0),(4,5),(1,6)) | [(1,2),(3,4)] | ((1,2),(3,4)) | [(11,12),(13,14)] | ((11,12),(13,14)) (4 rows) -- convert circles to polygons using the default number of points SELECT '' AS six, polygon(f1) FROM CIRCLE_TBL; six | polygon -----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359078377e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718156754e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077235131e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138)) | ((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795)) | ((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887967),(-3.33012701896897,0.500000000081028)) | ((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.598076211373729),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048616)) | ((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162)) | ((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239385585e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795)) (6 rows) -- convert the circle to an 8-point polygon SELECT '' AS six, polygon(8, f1) FROM CIRCLE_TBL; six | polygon -----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359078377e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718156754e-11),(2.12132034353258,-2.12132034358671),(-4.59307077235131e-11,-3),(-2.12132034359753,-2.12132034352175)) | ((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181134),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917)) | ((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586)) | ((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752)) | ((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261)) | ((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181134),(200,-1.02068239385585e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917)) (6 rows) -- -- Circles -- SELECT '' AS six, circle(f1, 50.0) FROM POINT_TBL; six | circle -----+----------------- | <(0,0),50> | <(-10,0),50> | <(-3,4),50> | <(5.1,34.5),50> | <(-5,-12),50> | <(10,10),50> (6 rows) SELECT '' AS four, circle(f1) FROM BOX_TBL; four | circle ------+------------------------- | <(1,1),1.4142135623731> | <(2,2),1.4142135623731> | <(2.5,3),0.5> | <(3,3),0> (4 rows) SELECT '' AS two, circle(f1) FROM POLYGON_TBL WHERE (# f1) >= 3; two | circle -----+-------------------------------------------------------- | <(1.33333333333333,1.33333333333333),2.04168905063636> | <(2.33333333333333,1.33333333333333),1.47534300379185> (2 rows) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 ORDER BY distance, circle, point using <<; twentyfour | circle | point | distance ------------+----------------+------------+------------------ | <(100,0),100> | (5.1,34.5) | 0.97653192697797 | <(1,2),3> | (-3,4) | 1.47213595499958 | <(0,0),3> | (-3,4) | 2 | <(100,0),100> | (-3,4) | 3.07764064044152 | <(100,0),100> | (-5,-12) | 5.68348972285122 | <(1,3),5> | (-10,0) | 6.40175425099138 | <(1,3),5> | (10,10) | 6.40175425099138 | <(0,0),3> | (-10,0) | 7 | <(1,2),3> | (-10,0) | 8.18033988749895 | <(1,2),3> | (10,10) | 9.0415945787923 | <(0,0),3> | (-5,-12) | 10 | <(100,0),100> | (-10,0) | 10 | <(0,0),3> | (10,10) | 11.142135623731 | <(1,3),5> | (-5,-12) | 11.1554944214035 | <(1,2),3> | (-5,-12) | 12.2315462117278 | <(1,3),5> | (5.1,34.5) | 26.7657047773223 | <(1,2),3> | (5.1,34.5) | 29.757594539282 | <(0,0),3> | (5.1,34.5) | 31.8749193547455 | <(100,200),10> | (5.1,34.5) | 180.778038568384 | <(100,200),10> | (10,10) | 200.237960416286 | <(100,200),10> | (-3,4) | 211.415898254845 | <(100,200),10> | (0,0) | 213.606797749979 | <(100,200),10> | (-10,0) | 218.254244210267 | <(100,200),10> | (-5,-12) | 226.577682802077 (24 rows)
Do these go into the 7.0 tree? Content-Description: message body text > Hi, > > I wrote > > > [...] but I don't know > > whether the latter two are actually a bug in postgres or a bug in the > > OS or even allowed difference. I'm including my results for reference. > > Looks like I forgot to include the other two files, sorry. Here they are. > > Bye, Rolf > -- > -- FLOAT8 > -- > CREATE TABLE FLOAT8_TBL(f1 float8); > INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); > SELECT '' AS five, FLOAT8_TBL.*; > five | f1 > ------+---------------------- > | 0 > | 1004.3 > | -34.84 > | 1.2345678901234e+200 > | 1.2345678901234e-200 > (5 rows) > > SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; > four | f1 > ------+---------------------- > | 0 > | -34.84 > | 1.2345678901234e+200 > | 1.2345678901234e-200 > (4 rows) > > SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; > one | f1 > -----+-------- > | 1004.3 > (1 row) > > SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; > three | f1 > -------+---------------------- > | 0 > | -34.84 > | 1.2345678901234e-200 > (3 rows) > > SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; > three | f1 > -------+---------------------- > | 0 > | -34.84 > | 1.2345678901234e-200 > (3 rows) > > SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; > four | f1 > ------+---------------------- > | 0 > | 1004.3 > | -34.84 > | 1.2345678901234e-200 > (4 rows) > > SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; > four | f1 > ------+---------------------- > | 0 > | 1004.3 > | -34.84 > | 1.2345678901234e-200 > (4 rows) > > SELECT '' AS three, f.f1, f.f1 * '-10' AS x > FROM FLOAT8_TBL f > WHERE f.f1 > '0.0'; > three | f1 | x > -------+----------------------+----------------------- > | 1004.3 | -10043 > | 1.2345678901234e+200 | -1.2345678901234e+201 > | 1.2345678901234e-200 | -1.2345678901234e-199 > (3 rows) > > SELECT '' AS three, f.f1, f.f1 + '-10' AS x > FROM FLOAT8_TBL f > WHERE f.f1 > '0.0'; > three | f1 | x > -------+----------------------+---------------------- > | 1004.3 | 994.3 > | 1.2345678901234e+200 | 1.2345678901234e+200 > | 1.2345678901234e-200 | -10 > (3 rows) > > SELECT '' AS three, f.f1, f.f1 / '-10' AS x > FROM FLOAT8_TBL f > WHERE f.f1 > '0.0'; > three | f1 | x > -------+----------------------+----------------------- > | 1004.3 | -100.43 > | 1.2345678901234e+200 | -1.2345678901234e+199 > | 1.2345678901234e-200 | -1.2345678901234e-201 > (3 rows) > > SELECT '' AS three, f.f1, f.f1 - '-10' AS x > FROM FLOAT8_TBL f > WHERE f.f1 > '0.0'; > three | f1 | x > -------+----------------------+---------------------- > | 1004.3 | 1014.3 > | 1.2345678901234e+200 | 1.2345678901234e+200 > | 1.2345678901234e-200 | 10 > (3 rows) > > SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 > FROM FLOAT8_TBL f where f.f1 = '1004.3'; > one | square_f1 > -----+------------ > | 1008618.49 > (1 row) > > -- absolute value > SELECT '' AS five, f.f1, @f.f1 AS abs_f1 > FROM FLOAT8_TBL f; > five | f1 | abs_f1 > ------+----------------------+---------------------- > | 0 | 0 > | 1004.3 | 1004.3 > | -34.84 | 34.84 > | 1.2345678901234e+200 | 1.2345678901234e+200 > | 1.2345678901234e-200 | 1.2345678901234e-200 > (5 rows) > > -- truncate > SELECT '' AS five, f.f1, %f.f1 AS trunc_f1 > FROM FLOAT8_TBL f; > five | f1 | trunc_f1 > ------+----------------------+---------------------- > | 0 | 0 > | 1004.3 | 1004 > | -34.84 | -34 > | 1.2345678901234e+200 | 1.2345678901234e+200 > | 1.2345678901234e-200 | 0 > (5 rows) > > -- round > SELECT '' AS five, f.f1, f.f1 % AS round_f1 > FROM FLOAT8_TBL f; > five | f1 | round_f1 > ------+----------------------+---------------------- > | 0 | 0 > | 1004.3 | 1004 > | -34.84 | -35 > | 1.2345678901234e+200 | 1.2345678901234e+200 > | 1.2345678901234e-200 | 0 > (5 rows) > > -- square root > SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 > FROM FLOAT8_TBL f > WHERE f.f1 > '0.0'; > three | f1 | sqrt_f1 > -------+----------------------+----------------------- > | 1004.3 | 31.6906926399535 > | 1.2345678901234e+200 | 1.11111110611109e+100 > | 1.2345678901234e-200 | 1.11111110611109e-100 > (3 rows) > > -- take exp of ln(f.f1) > SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1 > FROM FLOAT8_TBL f > WHERE f.f1 > '0.0'; > three | f1 | exp_ln_f1 > -------+----------------------+----------------------- > | 1004.3 | 1004.3 > | 1.2345678901234e+200 | 1.23456789012338e+200 > | 1.2345678901234e-200 | 1.23456789012339e-200 > (3 rows) > > -- cube root > SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; > five | f1 | cbrt_f1 > ------+----------------------+---------------------- > | 0 | 0 > | 1004.3 | 10.014312837827 > | -34.84 | -3.26607421344208 > | 1.2345678901234e+200 | 4.97933859234765e+66 > | 1.2345678901234e-200 | 2.3112042409018e-67 > (5 rows) > > SELECT '' AS five, FLOAT8_TBL.*; > five | f1 > ------+---------------------- > | 0 > | 1004.3 > | -34.84 > | 1.2345678901234e+200 > | 1.2345678901234e-200 > (5 rows) > > UPDATE FLOAT8_TBL > SET f1 = FLOAT8_TBL.f1 * '-1' > WHERE FLOAT8_TBL.f1 > '0.0'; > SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; > ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by zero > SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; > ERROR: pow() result is out of range > SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ; > ERROR: can't take log of zero > SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ; > ERROR: can't take log of a negative number > SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f; > ERROR: exp() result is out of range > SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; > ERROR: float8div: divide by zero error > SELECT '' AS five, FLOAT8_TBL.*; > five | f1 > ------+----------------------- > | 0 > | -34.84 > | -1004.3 > | -1.2345678901234e+200 > | -1.2345678901234e-200 > (5 rows) > > -- test for over and under flow > INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); > ERROR: Input '10e400' is out of range for float8 > INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); > ERROR: Input '-10e400' is out of range for float8 > INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); > -- maintain external table consistency across platforms > -- delete all values and reinsert well-behaved ones > DELETE FROM FLOAT8_TBL; > INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); > INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); > SELECT '' AS five, FLOAT8_TBL.*; > five | f1 > ------+----------------------- > | 0 > | -34.84 > | -1004.3 > | -1.2345678901234e+200 > | -1.2345678901234e-200 > (5 rows) > > -- > -- GEOMETRY > -- > -- > -- Points > -- > SELECT '' AS four, center(f1) AS center > FROM BOX_TBL; > four | center > ------+--------- > | (1,1) > | (2,2) > | (2.5,3) > | (3,3) > (4 rows) > > SELECT '' AS four, (@@ f1) AS center > FROM BOX_TBL; > four | center > ------+--------- > | (1,1) > | (2,2) > | (2.5,3) > | (3,3) > (4 rows) > > SELECT '' AS six, point(f1) AS center > FROM CIRCLE_TBL; > six | center > -----+----------- > | (0,0) > | (1,2) > | (1,3) > | (1,2) > | (100,200) > | (100,0) > (6 rows) > > SELECT '' AS six, (@@ f1) AS center > FROM CIRCLE_TBL; > six | center > -----+----------- > | (0,0) > | (1,2) > | (1,3) > | (1,2) > | (100,200) > | (100,0) > (6 rows) > > SELECT '' AS two, (@@ f1) AS center > FROM POLYGON_TBL > WHERE (# f1) > 2; > two | center > -----+------------------------------------- > | (1.33333333333333,1.33333333333333) > | (2.33333333333333,1.33333333333333) > (2 rows) > > -- "is horizontal" function > SELECT '' AS two, p1.f1 > FROM POINT_TBL p1 > WHERE ishorizontal(p1.f1, point '(0,0)'); > two | f1 > -----+--------- > | (0,0) > | (-10,0) > (2 rows) > > -- "is horizontal" operator > SELECT '' AS two, p1.f1 > FROM POINT_TBL p1 > WHERE p1.f1 ?- point '(0,0)'; > two | f1 > -----+--------- > | (0,0) > | (-10,0) > (2 rows) > > -- "is vertical" function > SELECT '' AS one, p1.f1 > FROM POINT_TBL p1 > WHERE isvertical(p1.f1, point '(5.1,34.5)'); > one | f1 > -----+------------ > | (5.1,34.5) > (1 row) > > -- "is vertical" operator > SELECT '' AS one, p1.f1 > FROM POINT_TBL p1 > WHERE p1.f1 ?| point '(5.1,34.5)'; > one | f1 > -----+------------ > | (5.1,34.5) > (1 row) > > -- > -- Line segments > -- > -- intersection > SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection > FROM LSEG_TBL l, POINT_TBL p; > ERROR: Unable to identify an operator '#' for types 'lseg' and 'point' > You will have to retype this query using an explicit cast > -- closest point > SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest > FROM LSEG_TBL l, POINT_TBL p; > thirty | f1 | s | closest > --------+------------+-------------------------------+--------------------------------------- > | (0,0) | [(1,2),(3,4)] | (1,2) > | (-10,0) | [(1,2),(3,4)] | (1,2) > | (-3,4) | [(1,2),(3,4)] | (1,2) > | (5.1,34.5) | [(1,2),(3,4)] | (3,4) > | (-5,-12) | [(1,2),(3,4)] | (1,2) > | (10,10) | [(1,2),(3,4)] | (3,4) > | (0,0) | [(0,0),(6,6)] | (0,0) > | (-10,0) | [(0,0),(6,6)] | (0,0) > | (-3,4) | [(0,0),(6,6)] | (0.5,0.5) > | (5.1,34.5) | [(0,0),(6,6)] | (6,6) > | (-5,-12) | [(0,0),(6,6)] | (0,0) > | (10,10) | [(0,0),(6,6)] | (6,6) > | (0,0) | [(10,-10),(-3,-4)] | (-2.04878048780488,-4.4390243902439) > | (-10,0) | [(10,-10),(-3,-4)] | (-3,-4) > | (-3,4) | [(10,-10),(-3,-4)] | (-3,-4) > | (5.1,34.5) | [(10,-10),(-3,-4)] | (-3,-4) > | (-5,-12) | [(10,-10),(-3,-4)] | (-1.60487804878049,-4.64390243902439) > | (10,10) | [(10,-10),(-3,-4)] | (2.39024390243902,-6.48780487804878) > | (0,0) | [(-1000000,200),(300000,-40)] | (0.0028402365895872,15.384614860264) > | (-10,0) | [(-1000000,200),(300000,-40)] | (-9.99715942258202,15.3864610140472) > | (-3,4) | [(-1000000,200),(300000,-40)] | (-2.99789812267519,15.3851688427303) > | (5.1,34.5) | [(-1000000,200),(300000,-40)] | (5.09647083221496,15.3836744976925) > | (-5,-12) | [(-1000000,200),(300000,-40)] | (-4.99494420845634,15.3855375281616) > | (10,10) | [(-1000000,200),(300000,-40)] | (10.000993741978,15.3827690473092) > | (0,0) | [(11,22),(33,44)] | (11,22) > | (-10,0) | [(11,22),(33,44)] | (11,22) > | (-3,4) | [(11,22),(33,44)] | (11,22) > | (5.1,34.5) | [(11,22),(33,44)] | (14.3,25.3) > | (-5,-12) | [(11,22),(33,44)] | (11,22) > | (10,10) | [(11,22),(33,44)] | (11,22) > (30 rows) > > -- > -- Lines > -- > -- > -- Boxes > -- > SELECT '' as six, box(f1) AS box FROM CIRCLE_TBL; > six | box > -----+---------------------------------------------------------------------------- > | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) > | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) > | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) > | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) > | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) > | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) > (6 rows) > > -- translation > SELECT '' AS twentyfour, b.f1 + p.f1 AS translation > FROM BOX_TBL b, POINT_TBL p; > twentyfour | translation > ------------+------------------------- > | (2,2),(0,0) > | (3,3),(1,1) > | (2.5,3.5),(2.5,2.5) > | (3,3),(3,3) > | (-8,2),(-10,0) > | (-7,3),(-9,1) > | (-7.5,3.5),(-7.5,2.5) > | (-7,3),(-7,3) > | (-1,6),(-3,4) > | (0,7),(-2,5) > | (-0.5,7.5),(-0.5,6.5) > | (0,7),(0,7) > | (7.1,36.5),(5.1,34.5) > | (8.1,37.5),(6.1,35.5) > | (7.6,38),(7.6,37) > | (8.1,37.5),(8.1,37.5) > | (-3,-10),(-5,-12) > | (-2,-9),(-4,-11) > | (-2.5,-8.5),(-2.5,-9.5) > | (-2,-9),(-2,-9) > | (12,12),(10,10) > | (13,13),(11,11) > | (12.5,13.5),(12.5,12.5) > | (13,13),(13,13) > (24 rows) > > SELECT '' AS twentyfour, b.f1 - p.f1 AS translation > FROM BOX_TBL b, POINT_TBL p; > twentyfour | translation > ------------+--------------------------- > | (2,2),(0,0) > | (3,3),(1,1) > | (2.5,3.5),(2.5,2.5) > | (3,3),(3,3) > | (12,2),(10,0) > | (13,3),(11,1) > | (12.5,3.5),(12.5,2.5) > | (13,3),(13,3) > | (5,-2),(3,-4) > | (6,-1),(4,-3) > | (5.5,-0.5),(5.5,-1.5) > | (6,-1),(6,-1) > | (-3.1,-32.5),(-5.1,-34.5) > | (-2.1,-31.5),(-4.1,-33.5) > | (-2.6,-31),(-2.6,-32) > | (-2.1,-31.5),(-2.1,-31.5) > | (7,14),(5,12) > | (8,15),(6,13) > | (7.5,15.5),(7.5,14.5) > | (8,15),(8,15) > | (-8,-8),(-10,-10) > | (-7,-7),(-9,-9) > | (-7.5,-6.5),(-7.5,-7.5) > | (-7,-7),(-7,-7) > (24 rows) > > -- scaling and rotation > SELECT '' AS twentyfour, b.f1 * p.f1 AS rotation > FROM BOX_TBL b, POINT_TBL p; > twentyfour | rotation > ------------+----------------------------- > | (0,0),(0,0) > | (0,0),(0,0) > | (0,0),(0,0) > | (0,0),(0,0) > | (0,0),(-20,-20) > | (-10,-10),(-30,-30) > | (-25,-25),(-25,-35) > | (-30,-30),(-30,-30) > | (0,2),(-14,0) > | (-7,3),(-21,1) > | (-17.5,2.5),(-21.5,-0.5) > | (-21,3),(-21,3) > | (0,79.2),(-58.8,0) > | (-29.4,118.8),(-88.2,39.6) > | (-73.5,104.1),(-108,99) > | (-88.2,118.8),(-88.2,118.8) > | (14,0),(0,-34) > | (21,-17),(7,-51) > | (29.5,-42.5),(17.5,-47.5) > | (21,-51),(21,-51) > | (0,40),(0,0) > | (0,60),(0,20) > | (0,60),(-10,50) > | (0,60),(0,60) > (24 rows) > > SELECT '' AS twenty, b.f1 / p.f1 AS rotation > FROM BOX_TBL b, POINT_TBL p > WHERE (p.f1 <-> point '(0,0)') >= 1; > twenty | rotation > --------+----------------------------------------------------------------------------------- > | (0,0),(-0.2,-0.2) > | (-0.1,-0.1),(-0.3,-0.3) > | (-0.25,-0.25),(-0.25,-0.35) > | (-0.3,-0.3),(-0.3,-0.3) > | (0.08,0),(0,-0.56) > | (0.12,-0.28),(0.04,-0.84) > | (0.26,-0.7),(0.1,-0.82) > | (0.12,-0.84),(0.12,-0.84) > | (0.0651176557643925,0),(0,-0.0483449262493217) > | (0.0976764836465887,-0.0241724631246608),(0.0325588278821962,-0.0725173893739825) > | (0.109762715208919,-0.0562379754328844),(0.0813970697054906,-0.0604311578116521) > | (0.0976764836465887,-0.0725173893739825),(0.0976764836465887,-0.0725173893739825) > | (0,0.0828402366863905),(-0.201183431952663,0) > | (-0.100591715976331,0.124260355029586),(-0.301775147928994,0.0414201183431953) > | (-0.251479289940828,0.103550295857988),(-0.322485207100592,0.0739644970414201) > | (-0.301775147928994,0.124260355029586),(-0.301775147928994,0.124260355029586) > | (0.2,0),(0,0) > | (0.3,0),(0.1,0) > | (0.3,0.05),(0.25,0) > | (0.3,0),(0.3,0) > (20 rows) > > -- > -- Paths > -- > SET geqo TO 'off'; > SELECT '' AS eight, points(f1) AS npoints, f1 AS path FROM PATH_TBL; > eight | npoints | path > -------+---------+--------------------------- > | 2 | [(1,2),(3,4)] > | 2 | ((1,2),(3,4)) > | 4 | [(0,0),(3,0),(4,5),(1,6)] > | 2 | ((1,2),(3,4)) > | 2 | ((1,2),(3,4)) > | 2 | [(1,2),(3,4)] > | 2 | [(11,12),(13,14)] > | 2 | ((11,12),(13,14)) > (8 rows) > > SELECT '' AS four, path(f1) FROM POLYGON_TBL; > four | path > ------+--------------------- > | ((2,0),(2,4),(0,0)) > | ((3,1),(3,3),(1,0)) > | ((0,0)) > | ((0,1),(0,1)) > (4 rows) > > -- translation > SELECT '' AS eight, p1.f1 + point '(10,10)' AS dist_add > FROM PATH_TBL p1; > eight | dist_add > -------+----------------------------------- > | [(11,12),(13,14)] > | ((11,12),(13,14)) > | [(10,10),(13,10),(14,15),(11,16)] > | ((11,12),(13,14)) > | ((11,12),(13,14)) > | [(11,12),(13,14)] > | [(21,22),(23,24)] > | ((21,22),(23,24)) > (8 rows) > > -- scaling and rotation > SELECT '' AS eight, p1.f1 * point '(2,-1)' AS dist_mul > FROM PATH_TBL p1; > eight | dist_mul > -------+------------------------------ > | [(4,3),(10,5)] > | ((4,3),(10,5)) > | [(0,0),(6,-3),(13,6),(8,11)] > | ((4,3),(10,5)) > | ((4,3),(10,5)) > | [(4,3),(10,5)] > | [(34,13),(40,15)] > | ((34,13),(40,15)) > (8 rows) > > RESET geqo; > -- > -- Polygons > -- > -- containment > SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 ~ p.f1 AS contains > FROM POLYGON_TBL poly, POINT_TBL p; > twentyfour | f1 | f1 | contains > ------------+------------+---------------------+---------- > | (0,0) | ((2,0),(2,4),(0,0)) | t > | (-10,0) | ((2,0),(2,4),(0,0)) | f > | (-3,4) | ((2,0),(2,4),(0,0)) | f > | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f > | (-5,-12) | ((2,0),(2,4),(0,0)) | f > | (10,10) | ((2,0),(2,4),(0,0)) | f > | (0,0) | ((3,1),(3,3),(1,0)) | f > | (-10,0) | ((3,1),(3,3),(1,0)) | f > | (-3,4) | ((3,1),(3,3),(1,0)) | f > | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f > | (-5,-12) | ((3,1),(3,3),(1,0)) | f > | (10,10) | ((3,1),(3,3),(1,0)) | f > | (0,0) | ((0,0)) | t > | (-10,0) | ((0,0)) | f > | (-3,4) | ((0,0)) | f > | (5.1,34.5) | ((0,0)) | f > | (-5,-12) | ((0,0)) | f > | (10,10) | ((0,0)) | f > | (0,0) | ((0,1),(0,1)) | f > | (-10,0) | ((0,1),(0,1)) | f > | (-3,4) | ((0,1),(0,1)) | f > | (5.1,34.5) | ((0,1),(0,1)) | f > | (-5,-12) | ((0,1),(0,1)) | f > | (10,10) | ((0,1),(0,1)) | f > (24 rows) > > SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 @ poly.f1 AS contained > FROM POLYGON_TBL poly, POINT_TBL p; > twentyfour | f1 | f1 | contained > ------------+------------+---------------------+----------- > | (0,0) | ((2,0),(2,4),(0,0)) | t > | (-10,0) | ((2,0),(2,4),(0,0)) | f > | (-3,4) | ((2,0),(2,4),(0,0)) | f > | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f > | (-5,-12) | ((2,0),(2,4),(0,0)) | f > | (10,10) | ((2,0),(2,4),(0,0)) | f > | (0,0) | ((3,1),(3,3),(1,0)) | f > | (-10,0) | ((3,1),(3,3),(1,0)) | f > | (-3,4) | ((3,1),(3,3),(1,0)) | f > | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f > | (-5,-12) | ((3,1),(3,3),(1,0)) | f > | (10,10) | ((3,1),(3,3),(1,0)) | f > | (0,0) | ((0,0)) | t > | (-10,0) | ((0,0)) | f > | (-3,4) | ((0,0)) | f > | (5.1,34.5) | ((0,0)) | f > | (-5,-12) | ((0,0)) | f > | (10,10) | ((0,0)) | f > | (0,0) | ((0,1),(0,1)) | f > | (-10,0) | ((0,1),(0,1)) | f > | (-3,4) | ((0,1),(0,1)) | f > | (5.1,34.5) | ((0,1),(0,1)) | f > | (-5,-12) | ((0,1),(0,1)) | f > | (10,10) | ((0,1),(0,1)) | f > (24 rows) > > SELECT '' AS four, points(f1) AS npoints, f1 AS polygon > FROM POLYGON_TBL; > four | npoints | polygon > ------+---------+--------------------- > | 3 | ((2,0),(2,4),(0,0)) > | 3 | ((3,1),(3,3),(1,0)) > | 1 | ((0,0)) > | 2 | ((0,1),(0,1)) > (4 rows) > > SELECT '' AS four, polygon(f1) > FROM BOX_TBL; > four | polygon > ------+------------------------------------------- > | ((0,0),(0,2),(2,2),(2,0)) > | ((1,1),(1,3),(3,3),(3,1)) > | ((2.5,2.5),(2.5,3.5),(2.5,3.5),(2.5,2.5)) > | ((3,3),(3,3),(3,3),(3,3)) > (4 rows) > > SELECT '' AS four, polygon(f1) > FROM PATH_TBL WHERE isclosed(f1); > four | polygon > ------+------------------- > | ((1,2),(3,4)) > | ((1,2),(3,4)) > | ((1,2),(3,4)) > | ((11,12),(13,14)) > (4 rows) > > SELECT '' AS four, f1 AS open_path, polygon( pclose(f1)) AS polygon > FROM PATH_TBL > WHERE isopen(f1); > four | open_path | polygon > ------+---------------------------+--------------------------- > | [(1,2),(3,4)] | ((1,2),(3,4)) > | [(0,0),(3,0),(4,5),(1,6)] | ((0,0),(3,0),(4,5),(1,6)) > | [(1,2),(3,4)] | ((1,2),(3,4)) > | [(11,12),(13,14)] | ((11,12),(13,14)) > (4 rows) > > -- convert circles to polygons using the default number of points > SELECT '' AS six, polygon(f1) > FROM CIRCLE_TBL; > six | polygon > -----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > | ((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359078377e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718156754e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077235131e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138)) > | ((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795)) > | ((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887967),(-3.33012701896897,0.500000000081028)) > | ((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.598076211373729),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048616)) > | ((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162)) > | ((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239385585e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795)) > (6 rows) > > -- convert the circle to an 8-point polygon > SELECT '' AS six, polygon(8, f1) > FROM CIRCLE_TBL; > six | polygon > -----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > | ((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359078377e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718156754e-11),(2.12132034353258,-2.12132034358671),(-4.59307077235131e-11,-3),(-2.12132034359753,-2.12132034352175)) > | ((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181134),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917)) > | ((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586)) > | ((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752)) > | ((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261)) > | ((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181134),(200,-1.02068239385585e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917)) > (6 rows) > > -- > -- Circles > -- > SELECT '' AS six, circle(f1, 50.0) > FROM POINT_TBL; > six | circle > -----+----------------- > | <(0,0),50> > | <(-10,0),50> > | <(-3,4),50> > | <(5.1,34.5),50> > | <(-5,-12),50> > | <(10,10),50> > (6 rows) > > SELECT '' AS four, circle(f1) > FROM BOX_TBL; > four | circle > ------+------------------------- > | <(1,1),1.4142135623731> > | <(2,2),1.4142135623731> > | <(2.5,3),0.5> > | <(3,3),0> > (4 rows) > > SELECT '' AS two, circle(f1) > FROM POLYGON_TBL > WHERE (# f1) >= 3; > two | circle > -----+-------------------------------------------------------- > | <(1.33333333333333,1.33333333333333),2.04168905063636> > | <(2.33333333333333,1.33333333333333),1.47534300379185> > (2 rows) > > SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance > FROM CIRCLE_TBL c1, POINT_TBL p1 > WHERE (p1.f1 <-> c1.f1) > 0 > ORDER BY distance, circle, point using <<; > twentyfour | circle | point | distance > ------------+----------------+------------+------------------ > | <(100,0),100> | (5.1,34.5) | 0.97653192697797 > | <(1,2),3> | (-3,4) | 1.47213595499958 > | <(0,0),3> | (-3,4) | 2 > | <(100,0),100> | (-3,4) | 3.07764064044152 > | <(100,0),100> | (-5,-12) | 5.68348972285122 > | <(1,3),5> | (-10,0) | 6.40175425099138 > | <(1,3),5> | (10,10) | 6.40175425099138 > | <(0,0),3> | (-10,0) | 7 > | <(1,2),3> | (-10,0) | 8.18033988749895 > | <(1,2),3> | (10,10) | 9.0415945787923 > | <(0,0),3> | (-5,-12) | 10 > | <(100,0),100> | (-10,0) | 10 > | <(0,0),3> | (10,10) | 11.142135623731 > | <(1,3),5> | (-5,-12) | 11.1554944214035 > | <(1,2),3> | (-5,-12) | 12.2315462117278 > | <(1,3),5> | (5.1,34.5) | 26.7657047773223 > | <(1,2),3> | (5.1,34.5) | 29.757594539282 > | <(0,0),3> | (5.1,34.5) | 31.8749193547455 > | <(100,200),10> | (5.1,34.5) | 180.778038568384 > | <(100,200),10> | (10,10) | 200.237960416286 > | <(100,200),10> | (-3,4) | 211.415898254845 > | <(100,200),10> | (0,0) | 213.606797749979 > | <(100,200),10> | (-10,0) | 218.254244210267 > | <(100,200),10> | (-5,-12) | 226.577682802077 > (24 rows) > -- 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