diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/test/regress/expected/collate_2.out | 596 | ||||
| -rw-r--r-- | src/test/regress/expected/tsearch_2.out | 1107 | ||||
| -rw-r--r-- | src/test/regress/expected/xc_distkey_1.out | 628 |
3 files changed, 2331 insertions, 0 deletions
diff --git a/src/test/regress/expected/collate_2.out b/src/test/regress/expected/collate_2.out new file mode 100644 index 0000000000..0924c2b609 --- /dev/null +++ b/src/test/regress/expected/collate_2.out @@ -0,0 +1,596 @@ +/* + * This test is intended to pass on all platforms supported by Postgres. + * We can therefore only assume that the default, C, and POSIX collations + * are available --- and since the regression tests are often run in a + * C-locale database, these may well all have the same behavior. But + * fortunately, the system doesn't know that and will treat them as + * incompatible collations. It is therefore at least possible to test + * parser behaviors such as collation conflict resolution. This test will, + * however, be more revealing when run in a database with non-C locale, + * since any departure from C sorting behavior will show as a failure. + */ +CREATE SCHEMA collate_tests; +SET search_path = collate_tests; +CREATE TABLE collate_test1 ( + a int, + b text COLLATE "C" NOT NULL +); +\d collate_test1 + Table "collate_tests.collate_test1" + Column | Type | Modifiers +--------+---------+-------------------- + a | integer | + b | text | collate C not null + +CREATE TABLE collate_test_fail ( + a int COLLATE "C", + b text +); +ERROR: collations are not supported by type integer +LINE 2: a int COLLATE "C", + ^ +CREATE TABLE collate_test_like ( + LIKE collate_test1 +); +\d collate_test_like +Table "collate_tests.collate_test_like" + Column | Type | Modifiers +--------+---------+-------------------- + a | integer | + b | text | collate C not null + +CREATE TABLE collate_test2 ( + a int, + b text COLLATE "POSIX" +); +INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'Abc'), (3, 'bbc'), (4, 'ABD'); +INSERT INTO collate_test2 SELECT * FROM collate_test1; +SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' ORDER BY a; + a | b +---+----- + 1 | abc + 3 | bbc +(2 rows) + +SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C" ORDER BY a; + a | b +---+----- + 1 | abc + 3 | bbc +(2 rows) + +SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C" ORDER BY a; + a | b +---+----- + 1 | abc + 3 | bbc +(2 rows) + +SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX" ORDER BY a; -- fail +ERROR: collation mismatch between explicit collations "C" and "POSIX" +LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "P... + ^ +CREATE DOMAIN testdomain_p AS text COLLATE "POSIX"; +CREATE DOMAIN testdomain_i AS int COLLATE "POSIX"; -- fail +ERROR: collations are not supported by type integer +CREATE TABLE collate_test4 ( + a int, + b testdomain_p +); +INSERT INTO collate_test4 SELECT * FROM collate_test1; +SELECT a, b FROM collate_test4 ORDER BY b; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +CREATE TABLE collate_test5 ( + a int, + b testdomain_p COLLATE "C" +); +INSERT INTO collate_test5 SELECT * FROM collate_test1; +SELECT a, b FROM collate_test5 ORDER BY b; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b FROM collate_test1 ORDER BY b; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b FROM collate_test2 ORDER BY b; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +-- star expansion +SELECT * FROM collate_test1 ORDER BY b; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT * FROM collate_test2 ORDER BY b; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +-- constant expression folding +SELECT 'bbc' COLLATE "C" > 'Abc' COLLATE "C" AS "true"; + true +------ + t +(1 row) + +SELECT 'bbc' COLLATE "POSIX" < 'Abc' COLLATE "POSIX" AS "false"; + false +------- + f +(1 row) + +-- upper/lower +CREATE TABLE collate_test10 ( + a int, + x text COLLATE "C", + y text COLLATE "POSIX" +); +INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ'); +SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10 ORDER BY a; + a | lower | lower | upper | upper | initcap | initcap +---+-------+-------+-------+-------+---------+--------- + 1 | hij | hij | HIJ | HIJ | Hij | Hij + 2 | hij | hij | HIJ | HIJ | Hij | Hij +(2 rows) + +SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10 ORDER BY a; + a | lower | lower +---+-------+------- + 1 | hij | hij + 2 | hij | hij +(2 rows) + +SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a; + a | x | y +---+-----+----- + 1 | hij | hij + 2 | HIJ | HIJ +(2 rows) + +-- backwards parsing +CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; +CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; +CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10; +SELECT table_name, view_definition FROM information_schema.views + WHERE table_name LIKE 'collview%' ORDER BY 1; + table_name | view_definition +------------+------------------------------------------------------------------------------------------------------------------------ + collview1 | SELECT collate_test1.a, collate_test1.b FROM collate_test1 WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); + collview2 | SELECT collate_test1.a, collate_test1.b FROM collate_test1 ORDER BY (collate_test1.b COLLATE "C"); + collview3 | SELECT collate_test10.a, lower(((collate_test10.x || collate_test10.x) COLLATE "POSIX")) AS lower FROM collate_test10; +(3 rows) + +-- collation propagation in various expression type +SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; + a | coalesce +---+---------- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2; + a | coalesce +---+---------- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10 ORDER BY a; + a | lower | lower +---+-------+------- + 1 | hij | hij + 2 | hij | hij +(2 rows) + +SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3,1; + a | b | greatest +---+-----+---------- + 2 | Abc | CCC + 4 | ABD | CCC + 1 | abc | abc + 3 | bbc | bbc +(4 rows) + +SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3,1; + a | b | greatest +---+-----+---------- + 2 | Abc | CCC + 4 | ABD | CCC + 1 | abc | abc + 3 | bbc | bbc +(4 rows) + +SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10 ORDER BY a; + a | x | y | lower | lower +---+-----+-----+-------+------- + 1 | hij | hij | hij | hij + 2 | HIJ | HIJ | foo | foo +(2 rows) + +SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; + a | nullif +---+-------- + 4 | ABD + 2 | Abc + 3 | bbc + 1 | +(4 rows) + +SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2; + a | nullif +---+-------- + 4 | ABD + 2 | Abc + 3 | bbc + 1 | +(4 rows) + +SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10 ORDER BY a; + a | lower | lower +---+-------+------- + 1 | hij | hij + 2 | hij | hij +(2 rows) + +SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; + a | b +---+------ + 4 | ABD + 2 | Abc + 1 | abcd + 3 | bbc +(4 rows) + +SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2; + a | b +---+------ + 4 | ABD + 2 | Abc + 1 | abcd + 3 | bbc +(4 rows) + +CREATE DOMAIN testdomain AS text; +SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b::testdomain_p FROM collate_test2 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10 ORDER BY 2; + a | lower | lower +---+-------+------- + 1 | hij | hij + 2 | hij | hij +(2 rows) + +SELECT min(b), max(b) FROM collate_test1; + min | max +-----+----- + ABD | bbc +(1 row) + +SELECT min(b), max(b) FROM collate_test2; + min | max +-----+----- + ABD | bbc +(1 row) + +SELECT array_agg(b ORDER BY b) FROM collate_test1; + array_agg +------------------- + {ABD,Abc,abc,bbc} +(1 row) + +SELECT array_agg(b ORDER BY b) FROM collate_test2; + array_agg +------------------- + {ABD,Abc,abc,bbc} +(1 row) + +SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2; + a | b +---+----- + 4 | ABD + 4 | ABD + 2 | Abc + 2 | Abc + 1 | abc + 1 | abc + 3 | bbc + 3 | bbc +(8 rows) + +SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b FROM collate_test2 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test2 WHERE a > 1 ORDER BY 2; + a | b +---+----- + 2 | Abc + 3 | bbc +(2 rows) + +SELECT a, b FROM collate_test2 EXCEPT SELECT a, b FROM collate_test2 WHERE a < 2 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 3 | bbc +(3 rows) + +SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2 ORDER BY 2; -- fail +ERROR: could not determine which collation to use for string comparison +HINT: Use the COLLATE clause to set the collation explicitly. +(SELECT a, b FROM collate_test1 ORDER BY 1) UNION ALL (SELECT a, b FROM collate_test2 ORDER BY 1); -- ok + a | b +---+----- + 1 | abc + 2 | Abc + 3 | bbc + 4 | ABD + 1 | abc + 2 | Abc + 3 | bbc + 4 | ABD +(8 rows) + +SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- fail +ERROR: collation mismatch between implicit collations "C" and "POSIX" +LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat... + ^ +HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. +SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- ok + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail +ERROR: collation mismatch between implicit collations "C" and "POSIX" +LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col... + ^ +HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. +SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail +ERROR: collation mismatch between implicit collations "C" and "POSIX" +LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla... + ^ +HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. +CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- fail +-- ideally this would be a parse-time error, but for now it must be run-time: +select x < y from collate_test10; -- fail +ERROR: could not determine which collation to use for string comparison +select x || y from collate_test10; -- ok, because || is not collation aware + ?column? +---------- + hijhij + HIJHIJ +(2 rows) + +select x, y from collate_test10 order by x || y; -- not so ok +ERROR: collation mismatch between implicit collations "C" and "POSIX" +LINE 1: select x, y from collate_test10 order by x || y; + ^ +HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. +-- collation mismatch between recursive and non-recursive term +WITH RECURSIVE foo(x) AS + (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x) + UNION ALL + SELECT (x || 'c') COLLATE "POSIX" FROM foo WHERE length(x) < 10) +SELECT * FROM foo; +ERROR: recursive query "foo" column 1 has collation "C" in non-recursive term but collation "POSIX" overall +LINE 2: (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x) + ^ +HINT: Use the COLLATE clause to set the collation of the non-recursive term. +SELECT a, b, a < b as lt FROM + (VALUES ('a', 'B'), ('A', 'b' COLLATE "C")) v(a,b) ORDER BY a; + a | b | lt +---+---+---- + A | b | t + a | B | f +(2 rows) + +-- casting +SELECT CAST('42' AS text COLLATE "C"); +ERROR: syntax error at or near "COLLATE" +LINE 1: SELECT CAST('42' AS text COLLATE "C"); + ^ +SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2; + a | b +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +-- polymorphism +SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1; + unnest +-------- + ABD + Abc + abc + bbc +(4 rows) + +SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1; + unnest +-------- + ABD + Abc + abc + bbc +(4 rows) + +CREATE FUNCTION dup (anyelement) RETURNS anyelement + AS 'select $1' LANGUAGE sql; +SELECT a, dup(b) FROM collate_test1 ORDER BY 2; + a | dup +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +SELECT a, dup(b) FROM collate_test2 ORDER BY 2; + a | dup +---+----- + 4 | ABD + 2 | Abc + 1 | abc + 3 | bbc +(4 rows) + +-- indexes +CREATE INDEX collate_test1_idx1 ON collate_test1 (b); +CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "POSIX"); +CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "POSIX")); -- this is different grammatically +CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX")); +CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "POSIX"); -- fail +ERROR: collations are not supported by type integer +CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "POSIX")); -- fail +ERROR: collations are not supported by type integer +LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "P... + ^ +SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1; + relname | pg_get_indexdef +--------------------+----------------------------------------------------------------------------------------------------- + collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_test1 USING btree (b) + collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_test1 USING btree (b COLLATE "POSIX") + collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_test1 USING btree (b COLLATE "POSIX") + collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX") +(4 rows) + +-- foreign keys +-- force indexes and mergejoins to be used for FK checking queries, +-- else they might not exercise collation-dependent operators +SET enable_seqscan TO 0; +SET enable_hashjoin TO 0; +SET enable_nestloop TO 0; +CREATE TABLE collate_test20 (f1 text COLLATE "C" PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "collate_test20_pkey" for table "collate_test20" +INSERT INTO collate_test20 VALUES ('foo'), ('bar'); +CREATE TABLE collate_test21 (f2 text COLLATE "POSIX" REFERENCES collate_test20); +INSERT INTO collate_test21 VALUES ('foo'), ('bar'); +INSERT INTO collate_test21 VALUES ('baz'); -- fail +ERROR: insert or update on table "collate_test21" violates foreign key constraint "collate_test21_f2_fkey" +DETAIL: Key (f2)=(baz) is not present in table "collate_test20". +CREATE TABLE collate_test22 (f2 text COLLATE "POSIX"); +INSERT INTO collate_test22 VALUES ('foo'), ('bar'), ('baz'); +ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; -- fail +ERROR: insert or update on table "collate_test22" violates foreign key constraint "collate_test22_f2_fkey" +DETAIL: Key (f2)=(baz) is not present in table "collate_test20". +DELETE FROM collate_test22 WHERE f2 = 'baz'; +ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; +RESET enable_seqscan; +RESET enable_hashjoin; +RESET enable_nestloop; +-- +-- Clean up. Many of these table names will be re-used if the user is +-- trying to run any platform-specific collation tests later, so we +-- must get rid of them. +-- +DROP SCHEMA collate_tests CASCADE; +NOTICE: drop cascades to 16 other objects +DETAIL: drop cascades to table collate_test1 +drop cascades to table collate_test_like +drop cascades to table collate_test2 +drop cascades to type testdomain_p +drop cascades to table collate_test4 +drop cascades to table collate_test5 +drop cascades to table collate_test10 +drop cascades to view collview1 +drop cascades to view collview2 +drop cascades to view collview3 +drop cascades to type testdomain +drop cascades to table test_u +drop cascades to function dup(anyelement) +drop cascades to table collate_test20 +drop cascades to table collate_test21 +drop cascades to table collate_test22 diff --git a/src/test/regress/expected/tsearch_2.out b/src/test/regress/expected/tsearch_2.out new file mode 100644 index 0000000000..1df0e37ec6 --- /dev/null +++ b/src/test/regress/expected/tsearch_2.out @@ -0,0 +1,1107 @@ +-- +-- Sanity checks for text search catalogs +-- +-- NB: we assume the oidjoins test will have caught any dangling links, +-- that is OID or REGPROC fields that are not zero and do not match some +-- row in the linked-to table. However, if we want to enforce that a link +-- field can't be 0, we have to check it here. +-- Find unexpected zero link entries +SELECT oid, prsname +FROM pg_ts_parser +WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR + -- prsheadline is optional + prslextype = 0; + oid | prsname +-----+--------- +(0 rows) + +SELECT oid, dictname +FROM pg_ts_dict +WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0; + oid | dictname +-----+---------- +(0 rows) + +SELECT oid, tmplname +FROM pg_ts_template +WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional + oid | tmplname +-----+---------- +(0 rows) + +SELECT oid, cfgname +FROM pg_ts_config +WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0; + oid | cfgname +-----+--------- +(0 rows) + +SELECT mapcfg, maptokentype, mapseqno +FROM pg_ts_config_map +WHERE mapcfg = 0 OR mapdict = 0; + mapcfg | maptokentype | mapseqno +--------+--------------+---------- +(0 rows) + +-- Look for pg_ts_config_map entries that aren't one of parser's token types +SELECT * FROM + ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid + FROM pg_ts_config ) AS tt +RIGHT JOIN pg_ts_config_map AS m + ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype) +WHERE + tt.cfgid IS NULL OR tt.tokid IS NULL; + cfgid | tokid | mapcfg | maptokentype | mapseqno | mapdict +-------+-------+--------+--------------+----------+--------- +(0 rows) + +-- test basic text search behavior without indexes, then with +SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; + count +------- + 158 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; + count +------- + 17 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; + count +------- + 6 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; + count +------- + 98 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; + count +------- + 23 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; + count +------- + 39 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; + count +------- + 494 +(1 row) + +create index wowidx on test_tsvector using gist (a); +SET enable_seqscan=OFF; +SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; + count +------- + 158 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; + count +------- + 17 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; + count +------- + 6 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; + count +------- + 98 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; + count +------- + 23 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; + count +------- + 39 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; + count +------- + 494 +(1 row) + +RESET enable_seqscan; +DROP INDEX wowidx; +CREATE INDEX wowidx ON test_tsvector USING gin (a); +SET enable_seqscan=OFF; +SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; + count +------- + 158 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; + count +------- + 17 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; + count +------- + 6 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; + count +------- + 98 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; + count +------- + 23 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; + count +------- + 39 +(1 row) + +SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; + count +------- + 494 +(1 row) + +RESET enable_seqscan; +INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH'); +SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; + word | ndoc | nentry +------+------+-------- + qq | 108 | 108 + qt | 102 | 102 + qe | 100 | 100 + qh | 98 | 98 + qw | 98 | 98 + qa | 97 | 97 + ql | 94 | 94 + qs | 94 | 94 + qi | 92 | 92 + qr | 92 | 92 +(10 rows) + +SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word; + word | ndoc | nentry +------+------+-------- + DFG | 1 | 2 +(1 row) + +--dictionaries and to_tsvector +SELECT ts_lexize('english_stem', 'skies'); + ts_lexize +----------- + {sky} +(1 row) + +SELECT ts_lexize('english_stem', 'identity'); + ts_lexize +----------- + {ident} +(1 row) + +SELECT * FROM ts_token_type('default') ORDER BY tokid; + tokid | alias | description +-------+-----------------+------------------------------------------ + 1 | asciiword | Word, all ASCII + 2 | word | Word, all letters + 3 | numword | Word, letters and digits + 4 | email | Email address + 5 | url | URL + 6 | host | Host + 7 | sfloat | Scientific notation + 8 | version | Version number + 9 | hword_numpart | Hyphenated word part, letters and digits + 10 | hword_part | Hyphenated word part, all letters + 11 | hword_asciipart | Hyphenated word part, all ASCII + 12 | blank | Space symbols + 13 | tag | XML tag + 14 | protocol | Protocol head + 15 | numhword | Hyphenated word, letters and digits + 16 | asciihword | Hyphenated word, all ASCII + 17 | hword | Hyphenated word, all letters + 18 | url_path | URL path + 19 | file | File or path name + 20 | float | Decimal notation + 21 | int | Signed integer + 22 | uint | Unsigned integer + 23 | entity | XML entity +(23 rows) + +SELECT * FROM ts_parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> +/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 +<i <b> wow < jqw <> qwerty') ORDER BY tokid,token; + tokid | token +-------+-------------------------------------- + 1 | ad + 1 | asdf + 1 | dw + 1 | hjwer + 1 | i + 1 | jf + 1 | jqw + 1 | qwe + 1 | qwe + 1 | qwe + 1 | qwe + 1 | qwer + 1 | qwerty + 1 | qwqwe + 1 | readline + 1 | readline + 1 | readline + 1 | sdjk + 1 | we + 1 | wefjn + 1 | wow + 3 | ewr1 + 3 | ewri2 + 4 | teodor@stack.net + 5 | 1aew.werc.ewr/?ad=qwe&dw + 5 | 3aew.werc.ewr/?ad=qwe&dw + 5 | 5aew.werc.ewr:8100/? + 5 | 6aew.werc.ewr:8100/?ad=qwe&dw + 5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 + 5 | aew.werc.ewr/?ad=qwe&dw + 6 | 1aew.werc.ewr + 6 | 2aew.werc.ewr + 6 | 3aew.werc.ewr + 6 | 4aew.werc.ewr + 6 | 5aew.werc.ewr:8100 + 6 | 6aew.werc.ewr:8100 + 6 | 7aew.werc.ewr:8100 + 6 | aew.werc.ewr + 6 | www.com + 7 | +4.0e-10 + 11 | qwe + 11 | wer + 12 | + + | + 12 | + + | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | + 12 | ' + 12 | & + 12 | , + 12 | - + 12 | . + 12 | . + 12 | . + 12 | / + 12 | < + 12 | < + 12 | < + 12 | <> + 12 | = + 12 | > + 12 | @ + 13 | <a href="qwe<qwe>"> + 13 | <b> + 13 | <fr> + 13 | <werrwe> + 14 | http:// + 14 | http:// + 14 | http:// + 14 | http:// + 14 | http:// + 16 | qwe-wer + 18 | /? + 18 | /?ad=qwe&dw + 18 | /?ad=qwe&dw + 18 | /?ad=qwe&dw + 18 | /?ad=qwe&dw + 18 | /?ad=qwe&dw=%20%32 + 19 | /awdf/dwqe/4325 + 19 | /usr/local/fff + 19 | /wqe-324/ewr + 19 | efd.r + 19 | gist.c + 19 | gist.h + 19 | gist.h.c + 19 | rewt/ewr + 20 | -4.2 + 20 | -4.2 + 20 | 234.435 + 20 | 4.2 + 20 | 4.2 + 20 | 4.2 + 20 | 5.005 + 22 | 234 + 22 | 345 + 22 | 455 +(133 rows) + +SELECT to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> +/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 +<i <b> wow < jqw <> qwerty'); + to_tsvector +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + '+4.0e-10':28 '-4.2':60,62 '/?':18 '/?ad=qwe&dw':7,10,14,24 '/?ad=qwe&dw=%20%32':27 '/awdf/dwqe/4325':48 '/usr/local/fff':47 '/wqe-324/ewr':51 '1aew.werc.ewr':9 '1aew.werc.ewr/?ad=qwe&dw':8 '234':63 '234.435':32 '2aew.werc.ewr':11 '345':1 '3aew.werc.ewr':13 '3aew.werc.ewr/?ad=qwe&dw':12 '4.2':56,57,58 '455':33 '4aew.werc.ewr':15 '5.005':34 '5aew.werc.ewr:8100':17 '5aew.werc.ewr:8100/?':16 '6aew.werc.ewr:8100':23 '6aew.werc.ewr:8100/?ad=qwe&dw':22 '7aew.werc.ewr:8100':26 '7aew.werc.ewr:8100/?ad=qwe&dw=%20%32':25 'ad':19 'aew.werc.ewr':6 'aew.werc.ewr/?ad=qwe&dw':5 'asdf':39 'dw':21 'efd.r':3 'ewr1':45 'ewri2':46 'gist.c':54 'gist.h':52 'gist.h.c':53 'hjwer':44 'jf':41 'jqw':66 'qwe':2,20,29,30,37 'qwe-wer':36 'qwer':40 'qwerti':67 'qwqwe':31 'readlin':55,59,61 'rewt/ewr':49 'sdjk':42 'teodor@stack.net':35 'wefjn':50 'wer':38 'wow':65 'www.com':4 +(1 row) + +SELECT length(to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> +/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 +<i <b> wow < jqw <> qwerty')); + length +-------- + 53 +(1 row) + +-- ts_debug +SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>') ORDER BY alias, description, token; + alias | description | token | dictionaries | dictionary | lexemes +-----------+-----------------+----------------------------+----------------+--------------+--------- + asciiword | Word, all ASCII | abc | {english_stem} | english_stem | {abc} + asciiword | Word, all ASCII | def | {english_stem} | english_stem | {def} + asciiword | Word, all ASCII | ghi | {english_stem} | english_stem | {ghi} + asciiword | Word, all ASCII | jkl | {english_stem} | english_stem | {jkl} + entity | XML entity | õ | {} | | + entity | XML entity | © | {} | | + entity | XML entity | &nm1; | {} | | + tag | XML tag | </myns:foo-bar_baz.blurfl> | {} | | + tag | XML tag | <myns:foo-bar_baz.blurfl> | {} | | +(9 rows) + +-- check parsing of URLs +SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>'); + alias | description | token | dictionaries | dictionary | lexemes +----------+---------------+----------------------------------------+--------------+------------+------------------------------------------ + protocol | Protocol head | http:// | {} | | + url | URL | www.harewoodsolutions.co.uk/press.aspx | {simple} | simple | {www.harewoodsolutions.co.uk/press.aspx} + host | Host | www.harewoodsolutions.co.uk | {simple} | simple | {www.harewoodsolutions.co.uk} + url_path | URL path | /press.aspx | {simple} | simple | {/press.aspx} + tag | XML tag | </span> | {} | | +(5 rows) + +SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>'); + alias | description | token | dictionaries | dictionary | lexemes +----------+---------------+----------------------------+--------------+------------+------------------------------ + protocol | Protocol head | http:// | {} | | + url | URL | aew.wer0c.ewr/id?ad=qwe&dw | {simple} | simple | {aew.wer0c.ewr/id?ad=qwe&dw} + host | Host | aew.wer0c.ewr | {simple} | simple | {aew.wer0c.ewr} + url_path | URL path | /id?ad=qwe&dw | {simple} | simple | {/id?ad=qwe&dw} + tag | XML tag | <span> | {} | | +(5 rows) + +SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?'); + alias | description | token | dictionaries | dictionary | lexemes +----------+---------------+----------------------+--------------+------------+------------------------ + protocol | Protocol head | http:// | {} | | + url | URL | 5aew.werc.ewr:8100/? | {simple} | simple | {5aew.werc.ewr:8100/?} + host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100} + url_path | URL path | /? | {simple} | simple | {/?} +(4 rows) + +SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx'); + alias | description | token | dictionaries | dictionary | lexemes +----------+-------------+------------------------+--------------+------------+-------------------------- + url | URL | 5aew.werc.ewr:8100/?xx | {simple} | simple | {5aew.werc.ewr:8100/?xx} + host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100} + url_path | URL path | /?xx | {simple} | simple | {/?xx} +(3 rows) + +-- to_tsquery +SELECT to_tsquery('english', 'qwe & sKies '); + to_tsquery +--------------- + 'qwe' & 'sky' +(1 row) + +SELECT to_tsquery('simple', 'qwe & sKies '); + to_tsquery +----------------- + 'qwe' & 'skies' +(1 row) + +SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC '); + to_tsquery +------------------------ + 'wether':CD & 'sky':BC +(1 row) + +SELECT to_tsquery('english', 'asd&(and|fghj)'); + to_tsquery +---------------- + 'asd' & 'fghj' +(1 row) + +SELECT to_tsquery('english', '(asd&and)|fghj'); + to_tsquery +---------------- + 'asd' | 'fghj' +(1 row) + +SELECT to_tsquery('english', '(asd&!and)|fghj'); + to_tsquery +---------------- + 'asd' | 'fghj' +(1 row) + +SELECT to_tsquery('english', '(the|and&(i&1))&fghj'); + to_tsquery +-------------- + '1' & 'fghj' +(1 row) + +SELECT plainto_tsquery('english', 'the and z 1))& fghj'); + plainto_tsquery +-------------------- + 'z' & '1' & 'fghj' +(1 row) + +SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd'); + ?column? +----------------------- + 'foo' & 'bar' & 'asd' +(1 row) + +SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); + ?column? +------------------------------ + 'foo' & 'bar' | 'asd' & 'fg' +(1 row) + +SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); + ?column? +----------------------------------- + 'foo' & 'bar' | !( 'asd' & 'fg' ) +(1 row) + +SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; + ?column? +---------------------------------- + 'foo' & 'bar' & ( 'asd' | 'fg' ) +(1 row) + +SELECT ts_rank_cd(to_tsvector('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +'), to_tsquery('english', 'paint&water')); + ts_rank_cd +------------ + 0.05 +(1 row) + +SELECT ts_rank_cd(to_tsvector('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +'), to_tsquery('english', 'breath&motion&water')); + ts_rank_cd +------------ + 0.00833333 +(1 row) + +SELECT ts_rank_cd(to_tsvector('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +'), to_tsquery('english', 'ocean')); + ts_rank_cd +------------ + 0.1 +(1 row) + +--headline tests +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'paint&water')); + ts_headline +----------------------------------------- + <b>painted</b> Ocean. + + <b>Water</b>, <b>water</b>, every where+ + And all the boards did shrink; + + <b>Water</b>, <b>water</b>, every +(1 row) + +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'breath&motion&water')); + ts_headline +---------------------------------- + <b>breath</b> nor <b>motion</b>,+ + As idle as a painted Ship + + Upon a painted Ocean. + + <b>Water</b>, <b>water</b> +(1 row) + +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'ocean')); + ts_headline +---------------------------------- + <b>Ocean</b>. + + Water, water, every where + + And all the boards did shrink;+ + Water, water, every where +(1 row) + +SELECT ts_headline('english', ' +<html> +<!-- some comment --> +<body> +Sea view wow <u>foo bar</u> <i>qq</i> +<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> +ff-bg +<script> + document.write(15); +</script> +</body> +</html>', +to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); + ts_headline +----------------------------------------------------------------------------- + + + <html> + + <!-- some comment --> + + <body> + + <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> + + <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>+ + ff-bg + + <script> + + document.write(15); + + </script> + + </body> + + </html> +(1 row) + +--Check if headline fragments work +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'ocean'), 'MaxFragments=1'); + ts_headline +------------------------------------ + after day, + + We stuck, nor breath nor motion,+ + As idle as a painted Ship + + Upon a painted <b>Ocean</b>. + + Water, water, every where + + And all the boards did shrink; + + Water, water, every where, + + Nor any drop +(1 row) + +--Check if more than one fragments are displayed +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); + ts_headline +---------------------------------------------- + after day, day after day, + + We <b>stuck</b>, nor breath nor motion, + + As idle as a painted Ship + + Upon a painted Ocean. + + Water, water, every where + + And all the boards did shrink; + + Water, water, every where ... drop to drink.+ + S. T. <b>Coleridge</b> +(1 row) + +--Fragments when there all query words are not in the document +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); + ts_headline +------------------------------------ + + + Day after day, day after day, + + We stuck, nor breath nor motion,+ + As idle as +(1 row) + +--FragmentDelimiter option +SELECT ts_headline('english', ' +Day after day, day after day, + We stuck, nor breath nor motion, +As idle as a painted Ship + Upon a painted Ocean. +Water, water, every where + And all the boards did shrink; +Water, water, every where, + Nor any drop to drink. +S. T. Coleridge (1772-1834) +', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); + ts_headline +-------------------------------------------- + after day, day after day, + + We <b>stuck</b>, nor breath nor motion, + + As idle as a painted Ship + + Upon a painted Ocean. + + Water, water, every where + + And all the boards did shrink; + + Water, water, every where***drop to drink.+ + S. T. <b>Coleridge</b> +(1 row) + +--Rewrite sub system +CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT); +\set ECHO none +ALTER TABLE test_tsquery ADD COLUMN keyword tsquery; +UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword); +ALTER TABLE test_tsquery ADD COLUMN sample tsquery; +UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text); +SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; + count +------- + 2 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; + count +------- + 3 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; + count +------- + 2 +(1 row) + +CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. +SET enable_seqscan=OFF; +SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; + count +------- + 2 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; + count +------- + 3 +(1 row) + +SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; + count +------- + 2 +(1 row) + +RESET enable_seqscan; +SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); + ts_rewrite +---------------------------------------------------------------------------------- + 'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | ( 'nyc' | 'big' & 'apple' ) ) +(1 row) + +SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); + ts_rewrite +--------------------- + 'moskva' | 'moscow' +(1 row) + +SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); + ts_rewrite +----------------------------------- + 'hotel' & ( 'moskva' | 'moscow' ) +(1 row) + +SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text ); + ts_rewrite +------------------------------------------------------------------------------------- + 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) ) +(1 row) + +SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); + ts_rewrite +--------------------- + 'moskva' | 'moscow' +(1 row) + +SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); + ts_rewrite +----------------------------------- + 'hotel' & ( 'moskva' | 'moscow' ) +(1 row) + +SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'); + ts_rewrite +------------------------------------------------------------------------------------- + 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) ) +(1 row) + +SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; + keyword +---------------- + 'new' & 'york' +(1 row) + +SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; + keyword +---------- + 'moscow' +(1 row) + +SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; + keyword +--------- +(0 rows) + +SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; + keyword +---------- + 'moscow' +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; + ts_rewrite +--------------------- + 'moskva' | 'moscow' +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; + ts_rewrite +----------------------------------- + 'hotel' & ( 'moskva' | 'moscow' ) +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; + ts_rewrite +------------------------------------------------------------------------------------- + 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) ) +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; + ts_rewrite +--------------------- + 'moskva' | 'moscow' +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; + ts_rewrite +----------------------------------- + 'hotel' & ( 'moskva' | 'moscow' ) +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; + ts_rewrite +------------------------------------------------------------------------------------- + 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) ) +(1 row) + +CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops); +SET enable_seqscan=OFF; +SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; + keyword +---------------- + 'new' & 'york' +(1 row) + +SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; + keyword +---------- + 'moscow' +(1 row) + +SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; + keyword +--------- +(0 rows) + +SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; + keyword +---------- + 'moscow' +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; + ts_rewrite +--------------------- + 'moskva' | 'moscow' +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; + ts_rewrite +----------------------------------- + 'hotel' & ( 'moskva' | 'moscow' ) +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; + ts_rewrite +------------------------------------------------------------------------------------- + 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) ) +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; + ts_rewrite +--------------------- + 'moskva' | 'moscow' +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; + ts_rewrite +----------------------------------- + 'hotel' & ( 'moskva' | 'moscow' ) +(1 row) + +SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; + ts_rewrite +------------------------------------------------------------------------------------- + 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) ) +(1 row) + +RESET enable_seqscan; +--test GUC +SET default_text_search_config=simple; +SELECT to_tsvector('SKIES My booKs'); + to_tsvector +---------------------------- + 'books':3 'my':2 'skies':1 +(1 row) + +SELECT plainto_tsquery('SKIES My booKs'); + plainto_tsquery +-------------------------- + 'skies' & 'my' & 'books' +(1 row) + +SELECT to_tsquery('SKIES & My | booKs'); + to_tsquery +-------------------------- + 'skies' & 'my' | 'books' +(1 row) + +SET default_text_search_config=english; +SELECT to_tsvector('SKIES My booKs'); + to_tsvector +------------------ + 'book':3 'sky':1 +(1 row) + +SELECT plainto_tsquery('SKIES My booKs'); + plainto_tsquery +----------------- + 'sky' & 'book' +(1 row) + +SELECT to_tsquery('SKIES & My | booKs'); + to_tsquery +---------------- + 'sky' | 'book' +(1 row) + +--trigger +CREATE TRIGGER tsvectorupdate +BEFORE UPDATE OR INSERT ON test_tsvector +FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t); +ERROR: Postgres-XC does not support TRIGGER yet +DETAIL: The feature is not currently supported +SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); + count +------- + 0 +(1 row) + +INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); +SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); + count +------- + 0 +(1 row) + +UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; +ERROR: Partition column can't be updated in current version +SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); + count +------- + 0 +(1 row) + +INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); +SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); + count +------- + 0 +(1 row) + +-- test finding items in GIN's pending list +create table pendtest (ts tsvector); +create index pendtest_idx on pendtest using gin(ts); +insert into pendtest values (to_tsvector('Lore ipsam')); +insert into pendtest values (to_tsvector('Lore ipsum')); +select * from pendtest where 'ipsu:*'::tsquery @@ ts; + ts +-------------------- + 'ipsum':2 'lore':1 +(1 row) + +select * from pendtest where 'ipsa:*'::tsquery @@ ts; + ts +-------------------- + 'ipsam':2 'lore':1 +(1 row) + +select * from pendtest where 'ips:*'::tsquery @@ ts ORDER BY 1; + ts +-------------------- + 'ipsam':2 'lore':1 + 'ipsum':2 'lore':1 +(2 rows) + +select * from pendtest where 'ipt:*'::tsquery @@ ts; + ts +---- +(0 rows) + +select * from pendtest where 'ipi:*'::tsquery @@ ts; + ts +---- +(0 rows) + +drop table pendtest; diff --git a/src/test/regress/expected/xc_distkey_1.out b/src/test/regress/expected/xc_distkey_1.out new file mode 100644 index 0000000000..25ecf4e341 --- /dev/null +++ b/src/test/regress/expected/xc_distkey_1.out @@ -0,0 +1,628 @@ +-- XC Test cases to verify that all supported data types are working as distribution key +-- Also verifies that the comaparison with a constant for equality is optimized. +create table ch_tab(a char) distribute by modulo(a); +insert into ch_tab values('a'); +select hashchar('a'); + hashchar +----------- + 463612535 +(1 row) + +create table nm_tab(a name) distribute by modulo(a); +insert into nm_tab values('abbas'); +select hashname('abbas'); + hashname +----------- + 605752656 +(1 row) + +create table nu_tab(a numeric(10,5)) distribute by modulo(a); +insert into nu_tab values(123.456); +insert into nu_tab values(789.412); +select * from nu_tab order by a; + a +----------- + 123.45600 + 789.41200 +(2 rows) + +select * from nu_tab where a = 123.456; + a +----------- + 123.45600 +(1 row) + +select * from nu_tab where 789.412 = a; + a +----------- + 789.41200 +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from nu_tab where a = 123.456; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from nu_tab where 789.412 = a; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +create table tx_tab(a text) distribute by modulo(a); +insert into tx_tab values('hello world'); +insert into tx_tab values('Did the quick brown fox jump over the lazy dog?'); +select * from tx_tab order by a; + a +------------------------------------------------- + Did the quick brown fox jump over the lazy dog? + hello world +(2 rows) + +select * from tx_tab where a = 'hello world'; + a +------------- + hello world +(1 row) + +select * from tx_tab where a = 'Did the quick brown fox jump over the lazy dog?'; + a +------------------------------------------------- + Did the quick brown fox jump over the lazy dog? +(1 row) + +select * from tx_tab where 'hello world' = a; + a +------------- + hello world +(1 row) + +select * from tx_tab where 'Did the quick brown fox jump over the lazy dog?' = a; + a +------------------------------------------------- + Did the quick brown fox jump over the lazy dog? +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from tx_tab where a = 'hello world'; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from tx_tab where a = 'Did the quick brown fox jump over the lazy dog?'; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +create table vc_tab(a varchar(255)) distribute by modulo(a); +insert into vc_tab values('abcdefghijklmnopqrstuvwxyz'); +insert into vc_tab values('A quick brown fox'); +insert into vc_tab values(NULL); +select * from vc_tab order by a; + a +---------------------------- + A quick brown fox + abcdefghijklmnopqrstuvwxyz + +(3 rows) + +select * from vc_tab where a = 'abcdefghijklmnopqrstuvwxyz'; + a +---------------------------- + abcdefghijklmnopqrstuvwxyz +(1 row) + +select * from vc_tab where a = 'A quick brown fox'; + a +------------------- + A quick brown fox +(1 row) + +-- This test a bug in examine_conditions_walker where a = constant is optimized but constant = a was not +select * from vc_tab where 'A quick brown fox' = a; + a +------------------- + A quick brown fox +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from vc_tab where a = 'abcdefghijklmnopqrstuvwxyz'; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from vc_tab where a = 'A quick brown fox'; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +-- This test a bug in examine_conditions_walker where a = constant is optimized but constant = a was not +explain (costs false, num_nodes true, nodes false) select * from vc_tab where 'A quick brown fox' = a; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +create table f8_tab(a float8) distribute by modulo(a); +insert into f8_tab values(123.456); +insert into f8_tab values(10.987654); +select * from f8_tab order by a; + a +----------- + 10.987654 + 123.456 +(2 rows) + +select * from f8_tab where a = 123.456; + a +--------- + 123.456 +(1 row) + +select * from f8_tab where a = 10.987654; + a +----------- + 10.987654 +(1 row) + +select * from f8_tab where a = 123.456::float8; + a +--------- + 123.456 +(1 row) + +select * from f8_tab where a = 10.987654::float8; + a +----------- + 10.987654 +(1 row) + +create table f4_tab(a float4) distribute by modulo(a); +insert into f4_tab values(123.456); +insert into f4_tab values(10.987654); +insert into f4_tab values(NULL); +select * from f4_tab order by a; + a +--------- + 10.9877 + 123.456 + +(3 rows) + +select * from f4_tab where a = 123.456; + a +--- +(0 rows) + +select * from f4_tab where a = 10.987654; + a +--- +(0 rows) + +select * from f4_tab where a = 123.456::float4; + a +--------- + 123.456 +(1 row) + +select * from f4_tab where a = 10.987654::float4; + a +--------- + 10.9877 +(1 row) + +create table i8_tab(a int8) distribute by modulo(a); +insert into i8_tab values(8446744073709551359); +insert into i8_tab values(78902); +insert into i8_tab values(NULL); +select * from i8_tab order by a; + a +--------------------- + 78902 + 8446744073709551359 + +(3 rows) + +select * from i8_tab where a = 8446744073709551359::int8; + a +--------------------- + 8446744073709551359 +(1 row) + +select * from i8_tab where a = 8446744073709551359; + a +--------------------- + 8446744073709551359 +(1 row) + +select * from i8_tab where a = 78902::int8; + a +------- + 78902 +(1 row) + +select * from i8_tab where a = 78902; + a +------- + 78902 +(1 row) + +create table i2_tab(a int2) distribute by modulo(a); +insert into i2_tab values(123); +insert into i2_tab values(456); +select * from i2_tab order by a; + a +----- + 123 + 456 +(2 rows) + +select * from i2_tab where a = 123; + a +----- + 123 +(1 row) + +select * from i2_tab where a = 456; + a +----- + 456 +(1 row) + +create table oid_tab(a oid) distribute by modulo(a); +insert into oid_tab values(23445); +insert into oid_tab values(45662); +select * from oid_tab order by a; + a +------- + 23445 + 45662 +(2 rows) + +select * from oid_tab where a = 23445; + a +------- + 23445 +(1 row) + +select * from oid_tab where a = 45662; + a +------- + 45662 +(1 row) + +create table i4_tab(a int4) distribute by modulo(a); +insert into i4_tab values(65530); +insert into i4_tab values(2147483647); +select * from i4_tab order by a; + a +------------ + 65530 + 2147483647 +(2 rows) + +select * from i4_tab where a = 65530; + a +------- + 65530 +(1 row) + +select * from i4_tab where a = 2147483647; + a +------------ + 2147483647 +(1 row) + +select * from i4_tab where 65530 = a; + a +------- + 65530 +(1 row) + +select * from i4_tab where 2147483647 = a; + a +------------ + 2147483647 +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from i4_tab where 65530 = a; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +explain (costs false, num_nodes true, nodes false) select * from i4_tab where a = 2147483647; + QUERY PLAN +------------------------------------------------------------------------------- + Data Node Scan (primary node count=0, node count=1) on "__REMOTE_FQS_QUERY__" +(1 row) + +create table bo_tab(a bool) distribute by modulo(a); +insert into bo_tab values(true); +insert into bo_tab values(false); +select * from bo_tab order by a; + a +--- + f + t +(2 rows) + +select * from bo_tab where a = true; + a +--- + t +(1 row) + +select * from bo_tab where a = false; + a +--- + f +(1 row) + +create table bpc_tab(a char(35)) distribute by modulo(a); +insert into bpc_tab values('Hello World'); +insert into bpc_tab values('The quick brown fox'); +select * from bpc_tab order by a; + a +------------------------------------- + Hello World + The quick brown fox +(2 rows) + +select * from bpc_tab where a = 'Hello World'; + a +------------------------------------- + Hello World +(1 row) + +select * from bpc_tab where a = 'The quick brown fox'; + a +------------------------------------- + The quick brown fox +(1 row) + +create table byta_tab(a bytea) distribute by modulo(a); +insert into byta_tab values(E'\\000\\001\\002\\003\\004\\005\\006\\007\\010'); +insert into byta_tab values(E'\\010\\011\\012\\013\\014\\015\\016\\017\\020'); +select * from byta_tab order by a; + a +---------------------- + \x000102030405060708 + \x08090a0b0c0d0e0f10 +(2 rows) + +select * from byta_tab where a = E'\\000\\001\\002\\003\\004\\005\\006\\007\\010'; + a +---------------------- + \x000102030405060708 +(1 row) + +select * from byta_tab where a = E'\\010\\011\\012\\013\\014\\015\\016\\017\\020'; + a +---------------------- + \x08090a0b0c0d0e0f10 +(1 row) + +create table tim_tab(a time) distribute by modulo(a); +insert into tim_tab values('00:01:02.03'); +insert into tim_tab values('23:59:59.99'); +select * from tim_tab order by a; + a +------------- + 00:01:02.03 + 23:59:59.99 +(2 rows) + +delete from tim_tab where a = '00:01:02.03'; +delete from tim_tab where a = '23:59:59.99'; +create table timtz_tab(a time with time zone) distribute by modulo(a); +insert into timtz_tab values('00:01:02.03 PST'); +insert into timtz_tab values('23:59:59.99 PST'); +select * from timtz_tab order by a; + a +---------------- + 00:01:02.03-08 + 23:59:59.99-08 +(2 rows) + +select * from timtz_tab where a = '00:01:02.03 PST'; + a +---------------- + 00:01:02.03-08 +(1 row) + +select * from timtz_tab where a = '23:59:59.99 PST'; + a +---------------- + 23:59:59.99-08 +(1 row) + +create table ts_tab(a timestamp) distribute by modulo(a); +insert into ts_tab values('May 10, 2011 00:01:02.03'); +insert into ts_tab values('August 14, 2001 23:59:59.99'); +select * from ts_tab order by a; + a +----------------------------- + Tue Aug 14 23:59:59.99 2001 + Tue May 10 00:01:02.03 2011 +(2 rows) + +select * from ts_tab where a = 'May 10, 2011 00:01:02.03'; + a +----------------------------- + Tue May 10 00:01:02.03 2011 +(1 row) + +select * from ts_tab where a = 'August 14, 2001 23:59:59.99'; + a +----------------------------- + Tue Aug 14 23:59:59.99 2001 +(1 row) + +create table in_tab(a interval) distribute by modulo(a); +insert into in_tab values('1 day 12 hours 59 min 10 sec'); +insert into in_tab values('0 day 4 hours 32 min 23 sec'); +select * from in_tab order by a; + a +---------------------------------- + @ 4 hours 32 mins 23 secs + @ 1 day 12 hours 59 mins 10 secs +(2 rows) + +select * from in_tab where a = '1 day 12 hours 59 min 10 sec'; + a +---------------------------------- + @ 1 day 12 hours 59 mins 10 secs +(1 row) + +select * from in_tab where a = '0 day 4 hours 32 min 23 sec'; + a +--------------------------- + @ 4 hours 32 mins 23 secs +(1 row) + +create table cash_tab(a money) distribute by modulo(a); +insert into cash_tab values('231.54'); +insert into cash_tab values('14011.50'); +select * from cash_tab order by a; + a +------------ + $231.54 + $14,011.50 +(2 rows) + +select * from cash_tab where a = '231.54'; + a +--------- + $231.54 +(1 row) + +select * from cash_tab where a = '14011.50'; + a +------------ + $14,011.50 +(1 row) + +create table atim_tab(a abstime) distribute by modulo(a); +insert into atim_tab values(abstime('May 10, 2011 00:01:02.03')); +insert into atim_tab values(abstime('Jun 23, 2001 23:59:59.99')); +select * from atim_tab order by a; + a +------------------------------ + Sat Jun 23 23:59:59 2001 PDT + Tue May 10 00:01:02 2011 PDT +(2 rows) + +select * from atim_tab where a = abstime('May 10, 2011 00:01:02.03'); + a +------------------------------ + Tue May 10 00:01:02 2011 PDT +(1 row) + +select * from atim_tab where a = abstime('Jun 23, 2001 23:59:59.99'); + a +------------------------------ + Sat Jun 23 23:59:59 2001 PDT +(1 row) + +create table rtim_tab(a reltime) distribute by modulo(a); +insert into rtim_tab values(reltime('1 day 12 hours 59 min 10 sec')); +insert into rtim_tab values(reltime('0 day 5 hours 32 min 23 sec')); +select * from rtim_tab order by a; + a +---------------------------------- + @ 5 hours 32 mins 23 secs + @ 1 day 12 hours 59 mins 10 secs +(2 rows) + +select * from rtim_tab where a = reltime('1 day 12 hours 59 min 10 sec'); + a +---------------------------------- + @ 1 day 12 hours 59 mins 10 secs +(1 row) + +select * from rtim_tab where a = reltime('0 day 5 hours 32 min 23 sec'); + a +--------------------------- + @ 5 hours 32 mins 23 secs +(1 row) + +create table date_tab(a date) distribute by modulo(a); +insert into date_tab values('May 10, 2011'); +insert into date_tab values('August 23, 2001'); +select * from date_tab order by a; + a +------------ + 08-23-2001 + 05-10-2011 +(2 rows) + +select * from date_tab where a = 'May 10, 2011'; + a +------------ + 05-10-2011 +(1 row) + +select * from date_tab where a = 'August 23, 2001'; + a +------------ + 08-23-2001 +(1 row) + +create table tstz_tab(a timestamp with time zone) distribute by modulo(a); +insert into tstz_tab values('May 10, 2011 00:01:02.03 PST'); +insert into tstz_tab values('Jun 23, 2001 23:59:59.99 PST'); +select * from tstz_tab order by a; + a +--------------------------------- + Sun Jun 24 00:59:59.99 2001 PDT + Tue May 10 01:01:02.03 2011 PDT +(2 rows) + +select * from tstz_tab where a = 'May 10, 2011 00:01:02.03 PST'; + a +--------------------------------- + Tue May 10 01:01:02.03 2011 PDT +(1 row) + +select * from tstz_tab where a = 'Jun 23, 2001 23:59:59.99 PST'; + a +--------------------------------- + Sun Jun 24 00:59:59.99 2001 PDT +(1 row) + +create table tstz_tab_h(a timestamp with time zone) distribute by hash(a); +insert into tstz_tab_h values('May 10, 2011 00:01:02.03 PST'); +insert into tstz_tab_h values('Jun 23, 2001 23:59:59.99 PST'); +select * from tstz_tab_h order by a; + a +--------------------------------- + Sun Jun 24 00:59:59.99 2001 PDT + Tue May 10 01:01:02.03 2011 PDT +(2 rows) + +select * from tstz_tab_h where a = 'May 10, 2011 00:01:02.03 PST'; + a +--------------------------------- + Tue May 10 01:01:02.03 2011 PDT +(1 row) + +select * from tstz_tab_h where a = 'Jun 23, 2001 23:59:59.99 PST'; + a +--------------------------------- + Sun Jun 24 00:59:59.99 2001 PDT +(1 row) + +create table my_rr_tab(a integer, b varchar(100)) distribute by round robin; +insert into my_rr_tab values(1 , 'One'); +insert into my_rr_tab values(2, 'Two'); +select * from my_rr_tab order by a; + a | b +---+----- + 1 | One + 2 | Two +(2 rows) + |