Hi.
Horiguchi-san pointed out [1] on a nearby thread that the partitioning
syntax (the FOR VALUES clause) doesn't accept true and false as valid
partition bound datums, which seems to me like an oversight. Attached a
patch to fix that.
create table bools (a bool) partition by list (a);
Before patch:
create table bools_t partition of bools for values in (true);
ERROR: syntax error at or near "true"
LINE 1: ...reate table bools_t partition of bools for values in (true);
After:
create table bools_t partition of bools for values in (true);
CREATE TABLE
\d bools_t
Table "public.bools_t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | boolean | | |
Partition of: bools FOR VALUES IN (true)
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/20171128.203915.26713586.horiguchi.kyotaro%40lab.ntt.co.jp
From 85d51048ee147c819a9ed0648557c7f05f3802c5 Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Tue, 12 Dec 2017 10:33:11 +0900
Subject: [PATCH] Allow Boolean values in partition FOR VALUES clause
---
doc/src/sgml/ref/create_table.sgml | 6 +++---
src/backend/parser/gram.y | 18 ++++++++++++++++++
src/test/regress/expected/create_table.out | 14 ++++++++++++++
src/test/regress/sql/create_table.sql | 7 +++++++
4 files changed, 42 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index a0c9a6d257..eaa79ae333 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -86,9 +86,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ]
TABLE [ IF NOT EXI
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable>
is:</phrase>
-IN ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | NULL } [, ...] ) |
-FROM ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | MINVALUE |
MAXVALUE } [, ...] )
- TO ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | MINVALUE |
MAXVALUE } [, ...] ) |
+IN ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
NULL } [, ...] ) |
+FROM ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
MINVALUE | MAXVALUE } [, ...] )
+ TO ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>,
REMAINDER <replaceable class="parameter">numeric_literal</replaceable> )
<phrase><replaceable class="parameter">index_parameters</replaceable> in
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
<literal>EXCLUDE</literal> constraints are:</phrase>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ebfc94f896..c6cbf9b844 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -153,6 +153,7 @@ static Node *makeBitStringConst(char *str, int location);
static Node *makeNullAConst(int location);
static Node *makeAConst(Value *v, int location);
static Node *makeBoolAConst(bool state, int location);
+static Node *makeBoolAConstNoCast(bool state, int location);
static RoleSpec *makeRoleSpec(RoleSpecType type, int location);
static void check_qualified_name(List *names, core_yyscan_t yyscanner);
static List *check_func_name(List *names, core_yyscan_t yyscanner);
@@ -2768,6 +2769,8 @@ partbound_datum:
Sconst { $$ = makeStringConst($1, @1);
}
| NumericOnly { $$ = makeAConst($1, @1); }
| NULL_P { $$ = makeNullAConst(@1); }
+ | TRUE_P { $$ =
makeBoolAConstNoCast(true, @1); }
+ | FALSE_P { $$ =
makeBoolAConstNoCast(false, @1); }
;
partbound_datum_list:
@@ -15590,6 +15593,21 @@ makeBoolAConst(bool state, int location)
return makeTypeCast((Node *)n, SystemTypeName("bool"), -1);
}
+/* makeBoolAConstNoCast()
+ * Create an A_Const string node containing valid bool type values.
+ */
+static Node *
+makeBoolAConstNoCast(bool state, int location)
+{
+ A_Const *n = makeNode(A_Const);
+
+ n->val.type = T_String;
+ n->val.val.str = (state ? "t" : "f");
+ n->location = location;
+
+ return (Node *) n;
+}
+
/* makeRoleSpec
* Create a RoleSpec with the given type
*/
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index 8e745402ae..c541a652c4 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -863,3 +863,17 @@ Partition key: LIST (a)
Number of partitions: 0
DROP TABLE parted_col_comment;
+-- boolean partitions
+create table boolspart (a bool) partition by list (a);
+create table boolspart_t partition of boolspart for values in (true);
+create table boolspart_f partition of boolspart for values in (false);
+\d+ boolspart
+ Table "public.boolspart"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | boolean | | | | plain | |
+Partition key: LIST (a)
+Partitions: boolspart_f FOR VALUES IN (false),
+ boolspart_t FOR VALUES IN (true)
+
+drop table boolspart;
diff --git a/src/test/regress/sql/create_table.sql
b/src/test/regress/sql/create_table.sql
index 8f9991ef18..c71e9f938e 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -707,3 +707,10 @@ COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
SELECT obj_description('parted_col_comment'::regclass);
\d+ parted_col_comment
DROP TABLE parted_col_comment;
+
+-- boolean partitions
+create table boolspart (a bool) partition by list (a);
+create table boolspart_t partition of boolspart for values in (true);
+create table boolspart_f partition of boolspart for values in (false);
+\d+ boolspart
+drop table boolspart;
--
2.11.0
