{"action":"opened","issue":{"url":"https://api.github.com/repos/dimitri/pgloader/issues/871","repository_url":"https://api.github.com/repos/dimitri/pgloader","labels_url":"https://api.github.com/repos/dimitri/pgloader/issues/871/labels{/name}","comments_url":"https://api.github.com/repos/dimitri/pgloader/issues/871/comments","events_url":"https://api.github.com/repos/dimitri/pgloader/issues/871/events","html_url":"https://github.com/dimitri/pgloader/issues/871","id":385423255,"node_id":"MDU6SXNzdWUzODU0MjMyNTU=","number":871,"title":"Numeric column type","user":{"login":"wiquan","id":8964681,"node_id":"MDQ6VXNlcjg5NjQ2ODE=","avatar_url":"https://avatars3.githubusercontent.com/u/8964681?v=4","gravatar_id":"","url":"https://api.github.com/users/wiquan","html_url":"https://github.com/wiquan","followers_url":"https://api.github.com/users/wiquan/followers","following_url":"https://api.github.com/users/wiquan/following{/other_user}","gists_url":"https://api.github.com/users/wiquan/gists{/gist_id}","starred_url":"https://api.github.com/users/wiquan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wiquan/subscriptions","organizations_url":"https://api.github.com/users/wiquan/orgs","repos_url":"https://api.github.com/users/wiquan/repos","events_url":"https://api.github.com/users/wiquan/events{/privacy}","received_events_url":"https://api.github.com/users/wiquan/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-11-28T19:31:29Z","updated_at":"2018-11-28T19:31:29Z","closed_at":null,"author_association":"NONE","body":"## Scenario\r\nI am trying to use pgloader to migrate from RedShift to PostgreSQL 10.5.\r\nI have a table that is storing lat and long as decimal coordinate as numeric(10,6).\r\nThere is an error that occurs while processing this table containing the numeric columns.\r\n\r\n```\r\nERROR Database error 42601: syntax error at or near \")\"\r\n```\r\n\r\n ## Details\r\n\r\n - [x] pgloader --version\r\n\r\n```bash\r\n$ docker run --rm --name pgloader -v $PWD:/data --env-file env.docker dimitri/pgloader:latest pgloader --debug --version\r\n\r\npgloader version \"3.5.2~devel\"\r\ncompiled with SBCL 1.3.14.debian\r\n```\r\n  \r\n  - [ ] did you test a fresh compile from the source tree ?\r\n        I used LATEST docker image. Same thing?\r\n\r\n  - [x] did you search for other similar issues?\r\n    \r\n  - [x] how can I reproduce the bug?\r\n\r\n\r\nDatabase Table with numeric type for GPS coordinate \r\n```\r\n--- lat and long stored as decimal degrees (DD)\r\nCREATE TABLE geolocations\r\n(\r\n  latitude             numeric(10,6),\r\n  longitude            numeric(10,6),\r\n  city             varchar(255),\r\n  state            varchar(255),\r\n  zip              varchar(255),\r\n  country          varchar(255)\r\n);\r\n```\r\n\r\nLOAD FILE\r\n```\r\nLOAD DATABASE\r\n        FROM postgresql://{{PGLOADER_SRC_USER}}:{{PGLOADER_SRC_PASSWORD}}@{{PGLOADER_SRC_HOST}}:{{PGLOADER_SRC_PORT}}/{{PGLOADER_SRC_DBNAME}}\r\n        INTO postgresql://{{PGLOADER_DST_USER}}:{{PGLOADER_DST_PASSWORD}}@{{PGLOADER_DST_HOST}}:{{PGLOADER_DST_PORT}}/{{PGLOADER_SRC_DBNAME}}\r\n\r\nWITH on error stop,\r\n        concurrency = {{PGLOADER_CONCURRENCY}},\r\n        workers = {{PGLOADER_WORKERS}},\r\n        prefetch rows = {{PGLOADER_PREFETCH_ROWS}}\r\n\r\nSET PostgreSQL PARAMETERS\r\nsearch_path to '{{PGLOADER_SCHEMA}}'\r\n\r\nBEFORE LOAD DO\r\n$$ create schema if not exists {{PGLOADER_SCHEMA}}; $$\r\n;\r\n```\r\n\r\n  - [x] pgloader output you obtain\r\n  \r\n```\r\n2018-11-28T17:41:21.561000Z SQL DROP TABLE IF EXISTS \"stage\".\"geolocations\" CASCADE;\r\n2018-11-28T17:41:21.563000Z SQL CREATE TABLE \"stage\".\"geolocations\" \r\n(\r\n  \"latitude\"  numeric() default NULL,\r\n  \"longitude\" numeric() default NULL,\r\n  \"city\"      text default NULL,\r\n  \"state\"     text default NULL,\r\n  \"zip\"       text default NULL,\r\n  \"country\"   text default NULL\r\n);\r\n2018-11-28T17:41:21.563000Z ERROR Database error 42601: syntax error at or near \")\"\r\nQUERY: CREATE TABLE \"stage\".\"geolocations\" \r\n(\r\n  \"latitude\"  numeric() default NULL,\r\n  \"longitude\" numeric() default NULL,\r\n  \"city\"      text default NULL,\r\n  \"state\"     text default NULL,\r\n  \"zip\"       text default NULL,\r\n  \"country\"   text default NULL\r\n);\r\n2018-11-28T17:41:21.571000Z FATAL Failed to create the schema, see above.\r\n2018-11-28T17:41:21.574000Z LOG report summary reset\r\n       table name     errors       read   imported      bytes      total time       read      write\r\n-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------\r\n      before load          0          1          1                     0.011s    \r\n  fetch meta data          0        253        253                     0.216s    \r\n   Create Schemas          0          0          0                     0.006s    \r\n Create SQL Types          0          0          0                     0.008s    \r\n    Create tables          0          0          0                     0.000s    \r\n-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------\r\n-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------\r\n2018-11-28T17:41:21.604000Z INFO Stopping monitor\r\n```\r\n\r\n  - [ ] data that is being loaded, if relevant\r\n  \r\n```\r\nPASTE HERE THE DATA THAT HAS BEEN LOADED\r\n```\r\n\r\n  - [x] How the data is different from what you expected, if relevant\r\nI was expecting the table to be created without this error.\r\n"}}
{"actor":{"display_login":"wiquan"}}