PostgreSQL使用主键与范围分区创建分区表时报语法错误如何解决

错误原因 #

你的SQL语句存在基础语法错误：status字段的定义结束后缺少英文逗号分隔符，SQL解析器会将后续的PRIMARY KEY约束判定为status字段的属性，解析到约束后的括号时就会触发语法报错。
额外说明：你当前设置的PRIMARY KEY (txn_id, posting_date)符合PostgreSQL分区表主键要求——分区表的主键必须包含分区键，这里posting_date刚好是范围分区的分区键，无需调整主键构成。

解决方法 #

只需要在status字段的DEFAULT 'C'后面添加一个英文逗号即可，修正后的完整SQL如下：

CREATE TABLE transactionp(
    txn_id                      serial,             -- txn unique id
    ts_created                  u_datetime
                                DEFAULT now(),
    ts_updated                  u_datetime
                                DEFAULT now(),
    account_id                  u_hash
                                REFERENCES account(account_id), -- account unique identifier
    card_id                     integer                         -- card unique identifier
                                REFERENCES card(card_id),
    bank_txn_id                 varchar(200)                    -- txn unique identifier provided by the bank
                                UNIQUE,
    amount                      u_money,                        -- txn amount
    reference                   varchar(255),                    -- txn reference
    txn_serial                  varchar(10),                    -- txn serial
    posting_date                u_datetime,                     -- txn posting date
    value_date                  u_datetime,                     -- txn value date
    bank_desc                   varchar(255),                   -- txn description provided by the bank
    detailed_desc               varchar(255),                   -- txn detailed desc provided by the bank
    short_desc                  varchar(255),
    txn_code                    varchar(20),                    -- describes the transaction type - provide by the bank
    balance                     u_money,                        -- account balance after this txn
    currency                    char(3),                        -- 3-letter ISO currency code
    ts_synced                   u_datetime,                     -- txn last updated time
    latitude                    u_coordinate,                   -- location u_coordinates
    longitude                   u_coordinate,                   --         //
    country                     varchar(2),                     -- location country code
    merchant_name               varchar(50),                    -- merchant name of the txn if POS
    merchant_code               varchar(10),                    -- merhant iso code
    merchant_country            varchar(2),                     -- merchant alpha 2 country
    image_url                   varchar(1000),                  -- txn uploaded reciept
    category_id                 integer                          -- unique identifier of the user category( ex: food, ..)
                                REFERENCES category(category_id),
    note                        text,                           -- a note about the transaction that could be added by the customer
    settlement_amount           u_money,                        -- credit card txn settlement amount
    settlement_currency         char(3),                        -- 3-letter ISO currency code
    status                      varchar(1)                      -- 'C' Complete, 'D' Deleted, 'P' Pending
                                DEFAULT 'C',
    PRIMARY KEY (txn_id, posting_date)
)
PARTITION BY RANGE (posting_date);

内容的提问来源于stack exchange，提问作者John Nnamaka