shuttle-hq / synth

The Declarative Data Generator
https://www.getsynth.com/
Apache License 2.0
1.36k stars 105 forks source link

Check constraint error while generating data in PostgreSQL #446

Open Mohsem35 opened 8 months ago

Mohsem35 commented 8 months ago

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

  1. Create table with this DDL in targeted database
    
    -- asus.region definition

-- Drop table

-- DROP TABLE asus.region;

CREATE TABLE asus.region ( "oid" varchar(128) NOT NULL, region_id varchar(256) NOT NULL, region_code varchar(256) NOT NULL, region_name_en varchar(256) NOT NULL, region_name_bn varchar(256) NOT NULL, status varchar(32) NULL, created_by varchar(128) NOT NULL DEFAULT 'System'::character varying, created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_by varchar(128) NULL, updated_on timestamp NULL, CONSTRAINT ck_status_region CHECK ((((status)::text = 'Active'::text) OR ((status)::text = 'Inactive'::text))), CONSTRAINT region_pkey PRIMARY KEY (oid), CONSTRAINT region_region_id_key UNIQUE (region_id) );

2. Installation
3. Building a data model(table) from an existing database which has a table with "check" constraint
```shell
synth import template/ --from postgres://mraims:mraims@172.16.6.89:5432/mraims --schema asus
  1. Try to generate data
    synth generate asus --to postgres://ibprod:ibprod@192.168.191.33:5432/ibprod --schema asus
  2. See error
    
    Error: At namespace "asus"

Caused by: 0: Failed to insert data for collection region 1: One or more database inserts failed: error returned from database: new row for relation "region" violates check constraint "ck_status_region": new row for relation "region" violates check constraint "ck_status_region"


**Expected behavior**
Datas should be inserted in the region table in "ibprod" database "asus" schema

**Screenshots**

**Environment (please complete the following information):**
 - OS: Rockylinux 9.3
 - Version: PostgreSQL 14

**Additional context**
generated data model by synth
```json
{
  "type": "array",
  "length": {
    "type": "number",
    "range": {
      "low": 0,
      "high": 2,
      "step": 1
    },
    "subtype": "u64"
  },
  "content": {
    "type": "object",
    "created_by": {
      "type": "string",
      "pattern": "[a-zA-Z0-9]{0, 128}"
    },
    "created_on": {
      "type": "date_time",
      "format": "%Y-%m-%dT%H:%M:%S",
      "subtype": "naive_date_time"
    },
    "oid": {
      "type": "unique",
      "algorithm": {
        "Hash": {
          "retries": null
        }
      },
      "content": {
        "type": "string",
        "pattern": "[a-zA-Z0-9]{0, 128}"
      }
    },
    "region_code": {
      "type": "string",
      "pattern": "[a-zA-Z0-9]{0, 256}"
    },
    "region_id": {
      "type": "string",
      "pattern": "[a-zA-Z0-9]{0, 256}"
    },
    "region_name_bn": {
      "type": "string",
      "pattern": "[a-zA-Z0-9]{0, 256}"
    },
    "region_name_en": {
      "type": "string",
      "pattern": "[a-zA-Z0-9]{0, 256}"
    },
    "status": {
      "type": "one_of",
      "variants": [
        {
          "weight": 1.0,
          "type": "string",
          "pattern": "[a-zA-Z0-9]{0, 32}"
        },
        {
          "weight": 1.0,
          "type": "null"
        }
      ]
    },
    "updated_by": {
      "type": "one_of",
      "variants": [
        {
          "weight": 1.0,
          "type": "string",
          "pattern": "[a-zA-Z0-9]{0, 128}"
        },
        {
          "weight": 1.0,
          "type": "null"
        }
      ]
    },
    "updated_on": {
      "type": "one_of",
      "variants": [
        {
          "weight": 1.0,
          "type": "date_time",
          "format": "%Y-%m-%dT%H:%M:%S",
          "subtype": "naive_date_time"
        },
        {
          "weight": 1.0,
          "type": "null"
        }
      ]
    }
  }
}