============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Yi Ren
Your email address : yren@elastic.com
Category : runtime: back-end: SQL
Severity : serious
Summary: pg_dump generates schema with wrong SQL syntax in version 6.32
System Configuration
--------------------
Operating System : Linux(Redhat 5.2)
PostgreSQL version : 6.32
Compiler used : gcc 2.7
Hardware:
---------
Pentuim II 300MHZ, 128M RAM
Versions of other tools:
------------------------
--------------------------------------------------------------------------
Problem Description:
--------------------
In version 6.32, I use pg_dump to generate the schema of our database.
When I use the schema to re-create the tables, the DBMS complains the SQL systax
in the schema(my problem is with the CONSTRAINT, CHECK key words). I believe the
generated schema still has version 6.2 syntax, while version 6.3 is SQL92 compatible,
so it won't take the version 6.2 syntax. pg_dump works fine with version 6.2. pg_dump needs to
comply to the version 6.32 SQL syntax. We use pg-dump to backup our database, its bug prevents
us from doing that.
--------------------------------------------------------------------------
Test Case:
----------
original schema:
CREATE TABLE Privacy_Filter (
Filter_MAC_Address CHARACTER(17) PRIMARY KEY,
CO_MAC_Address CHARACTER(17) NOT NULL,
CPE_MAC_Address CHARACTER(17) NOT NULL,
Filter_Group INTEGER,
Filter_Enabled VARCHAR(8),
CONSTRAINT filter_status CHECK (Filter_Enabled = 'enabled' OR Filter_Enabled = 'disa
bled')
);
generated schema:
CREATE TABLE privacy_filter (
filter_mac_address char(17) NOT NULL,
co_mac_address char(17) NOT NULL,
cpe_mac_address char(17) NOT NULL,
filter_group int4,
filter_enabled varchar(8))
CONSTRAINT filter_status CHECK filter_enabled = 'enabled' OR filter_enabled = 'disabled';
--------------------------------------------------------------------------
Solution:
---------
--------------------------------------------------------------------------
