5 -- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
8 WHERE onek.unique1 < 10
10 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
11 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
12 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
13 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
14 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
15 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
16 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
17 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
18 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
19 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
20 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
21 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
25 -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
27 SELECT onek.unique1, onek.stringu1 FROM onek
28 WHERE onek.unique1 < 20
29 ORDER BY unique1 using >;
55 -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
57 SELECT onek.unique1, onek.stringu1 FROM onek
58 WHERE onek.unique1 > 980
59 ORDER BY stringu1 using <;
85 -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
86 -- sort +1d -2 +0nr -1
88 SELECT onek.unique1, onek.string4 FROM onek
89 WHERE onek.unique1 > 980
90 ORDER BY string4 using <, unique1 using >;
116 -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
117 -- sort +1dr -2 +0n -1
119 SELECT onek.unique1, onek.string4 FROM onek
120 WHERE onek.unique1 > 980
121 ORDER BY string4 using >, unique1 using <;
147 -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
148 -- sort +0nr -1 +1d -2
150 SELECT onek.unique1, onek.string4 FROM onek
151 WHERE onek.unique1 < 20
152 ORDER BY unique1 using >, string4 using <;
178 -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
179 -- sort +0n -1 +1dr -2
181 SELECT onek.unique1, onek.string4 FROM onek
182 WHERE onek.unique1 < 20
183 ORDER BY unique1 using <, string4 using >;
209 -- test partial btree indexes
211 -- As of 7.2, planner probably won't pick an indexscan without stats,
212 -- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan
213 -- followed by sort, because that could hide index ordering problems.
216 SET enable_seqscan TO off;
217 SET enable_bitmapscan TO off;
218 SET enable_sort TO off;
220 -- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
222 SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;
223 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
224 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
225 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
226 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
227 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
228 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
229 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
230 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
231 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
232 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
233 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
234 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
238 -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
240 SELECT onek2.unique1, onek2.stringu1 FROM onek2
241 WHERE onek2.unique1 < 20
242 ORDER BY unique1 using >;
268 -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
270 SELECT onek2.unique1, onek2.stringu1 FROM onek2
271 WHERE onek2.unique1 > 980;
295 RESET enable_seqscan;
296 RESET enable_bitmapscan;
298 SELECT two, stringu1, ten, string4
302 -- awk '{print $1,$2;}' person.data |
303 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
304 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
305 -- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data
307 -- SELECT name, age FROM person*; ??? check if different
308 SELECT p.name, p.age FROM person* p;
372 -- awk '{print $1,$2;}' person.data |
373 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
374 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
375 -- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |
378 SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
442 -- Test some cases involving whole-row Var referencing a subquery
444 select foo from (select 1) as foo;
450 select foo from (select null) as foo;
456 select foo from (select 'xyzzy',1,null) as foo;
465 select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)
466 WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
467 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j
468 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------
469 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA
470 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA
473 -- a more complex case
474 -- looks like we're coding lisp :-)
476 (values ((select i from
477 (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
478 order by i asc limit 1))) bar (i)
479 where onek.unique1 = bar.i;
480 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i
481 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
482 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2
485 -- try VALUES in a subquery
487 where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
489 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
490 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
491 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
492 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx
493 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx
496 -- VALUES is also legal as a standalone query or a set-operation member
497 VALUES (1,2), (3,4+4), (7,77.7);
505 VALUES (1,2), (3,4+4), (7,77.7)
509 SELECT * FROM int8_tbl;
511 ------------------+-------------------
517 123 | 4567890123456789
518 4567890123456789 | 123
519 4567890123456789 | 4567890123456789
520 4567890123456789 | -4567890123456789
524 -- Test ORDER BY options
526 CREATE TEMP TABLE foo (f1 int);
527 INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
528 SELECT * FROM foo ORDER BY f1;
540 SELECT * FROM foo ORDER BY f1 ASC; -- same thing
552 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
564 SELECT * FROM foo ORDER BY f1 DESC;
576 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
588 -- check if indexscans do the right things
589 CREATE INDEX fooi ON foo (f1);
590 SET enable_sort = false;
591 SELECT * FROM foo ORDER BY f1;
603 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
615 SELECT * FROM foo ORDER BY f1 DESC;
627 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
640 CREATE INDEX fooi ON foo (f1 DESC);
641 SELECT * FROM foo ORDER BY f1;
653 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
665 SELECT * FROM foo ORDER BY f1 DESC;
677 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
690 CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
691 SELECT * FROM foo ORDER BY f1;
703 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
715 SELECT * FROM foo ORDER BY f1 DESC;
727 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
740 -- Test some corner cases that have been known to confuse the planner
742 -- ORDER BY on a constant doesn't really need any sorting
743 SELECT 1 AS x ORDER BY x;
749 -- But ORDER BY on a set-valued expression does
750 create function sillysrf(int) returns setof int as
751 'values (1),(10),(2),($1)' language sql immutable;
761 select sillysrf(-1) order by 1;
770 drop function sillysrf(int);
projects / postgresql / blob