{"action":"created","issue":{"url":"https://api.github.com/repos/dimitri/pgloader/issues/377","repository_url":"https://api.github.com/repos/dimitri/pgloader","labels_url":"https://api.github.com/repos/dimitri/pgloader/issues/377/labels{/name}","comments_url":"https://api.github.com/repos/dimitri/pgloader/issues/377/comments","events_url":"https://api.github.com/repos/dimitri/pgloader/issues/377/events","html_url":"https://github.com/dimitri/pgloader/issues/377","id":143674476,"number":377,"title":"Type error when loading SQLite database","user":{"login":"pylaligand","id":1115379,"avatar_url":"https://avatars.githubusercontent.com/u/1115379?v=3","gravatar_id":"","url":"https://api.github.com/users/pylaligand","html_url":"https://github.com/pylaligand","followers_url":"https://api.github.com/users/pylaligand/followers","following_url":"https://api.github.com/users/pylaligand/following{/other_user}","gists_url":"https://api.github.com/users/pylaligand/gists{/gist_id}","starred_url":"https://api.github.com/users/pylaligand/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pylaligand/subscriptions","organizations_url":"https://api.github.com/users/pylaligand/orgs","repos_url":"https://api.github.com/users/pylaligand/repos","events_url":"https://api.github.com/users/pylaligand/events{/privacy}","received_events_url":"https://api.github.com/users/pylaligand/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2016-03-26T07:40:26Z","updated_at":"2016-03-26T20:23:21Z","closed_at":"2016-03-26T17:55:22Z","body":"I created a simple SQLite db with:\r\n```sh\r\n$ sqlite3 test.db\r\nsqlite> CREATE TABLE [TestTable] ( [id] INTEGER PRIMARY KEY NOT NULL, [json] BLOB DEFAULT (null)  );\r\n```\r\n\r\nI then installed pgloader:\r\n```sh\r\n$ pgloader --version\r\npgloader version \"3.2.2\"\r\ncompiled with SBCL 1.3.0\r\n```\r\n\r\nI attempted to load the database into PostgreSQL:\r\n```sh\r\n$ pgloader --debug --verbose --type sqlite test.db postgresql:///test-sqlite \r\nsb-impl::*default-external-format* :UTF-8\r\ntmpdir: #P\"/var/folders/02/qnf40nln40g10sktc1lfxxpw0000gn/T/\"\r\n2016-03-26T00:24:16.015000-07:00 NOTICE Starting pgloader, log system is ready.\r\n2016-03-26T00:24:16.027000-07:00 INFO Starting monitor\r\n2016-03-26T00:24:16.031000-07:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log'\r\n2016-03-26T00:24:16.031000-07:00 LOG Data errors in '/private/tmp/pgloader/'\r\nAn unhandled error condition has been signalled:\r\n   The value \"null\"\r\n   is not of type\r\n     (OR NULL (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (*))).\r\n\r\nDate/time: 2016-03-26-00:24An unhandled error condition has been signalled:\r\n                              The value \"null\"\r\n                              is not of type\r\n                                (OR NULL (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (*))).\r\n\r\n\r\nBacktrace for: #<SB-THREAD:THREAD \"main thread\" RUNNING {1007634903}>\r\n0: (PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA \"null\") [tl,external]\r\n1: (PGLOADER.SOURCES::FORMAT-PGSQL-DEFAULT-VALUE \"null\" PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA)\r\n2: (PGLOADER.SOURCES::FORMAT-PGSQL-TYPE (:TABLE-NAME \"TestTable\" :COLUMN-NAME \"json\" :TYPE \"blob\" :CTYPE \"blob\" :DEFAULT \"null\" :NOT-NULL NIL :AUTO-INCREMENT NIL) (:TYPE \"bytea\") PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA)\r\n3: (PGLOADER.SOURCES::APPLY-CASTING-RULES \"blob\" \"blob\" \"null\" NIL NIL :TABLE-NAME \"TestTable\" :COLUMN-NAME \"json\" :RULES NIL)\r\n4: (PGLOADER.SOURCES:CAST \"TestTable\" \"json\" \"blob\" \"blob\" \"null\" NIL NIL)\r\n5: ((:METHOD PGLOADER.PGSQL:FORMAT-PGSQL-COLUMN (PGLOADER.SQLITE::COLDEF)) #S(PGLOADER.SQLITE::COLDEF :TABLE-NAME \"TestTable\" :SEQ 1 :NAME \"json\" :DTYPE \"blob\" :CTYPE \"blob\" :NULLABLE NIL :DEFAULT \"null\" :PK-ID 0)) [fast-method]\r\n6: (PGLOADER.PGSQL::CREATE-TABLE-SQL \"TestTable\" (#S(PGLOADER.SQLITE::COLDEF :TABLE-NAME #1=\"TestTable\" :SEQ 0 :NAME \"id\" :DTYPE \"integer\" :CTYPE \"integer\" :NULLABLE T :DEFAULT NIL :PK-ID 1) #S(PGLOADER.SQLITE::COLDEF :TABLE-NAME #1# :SEQ 1 :NAME \"json\" :DTYPE \"blob\" :CTYPE \"blob\" :NULLABLE NIL :DEFAULT \"null\" :PK-ID 0)) :IF-NOT-EXISTS NIL)\r\n7: (PGLOADER.PGSQL::CREATE-TABLE-SQL-LIST ((#1=\"TestTable\" #S(PGLOADER.SQLITE::COLDEF :TABLE-NAME #1# :SEQ 0 :NAME \"id\" :DTYPE \"integer\" :CTYPE \"integer\" :NULLABLE T :DEFAULT NIL :PK-ID 1) #S(PGLOADER.SQLITE::COLDEF :TABLE-NAME #1# :SEQ 1 :NAME \"json\" :DTYPE \"blob\" :CTYPE \"blob\" :NULLABLE NIL :DEFAULT \"null\" :PK-ID 0))) :IF-NOT-EXISTS NIL :INCLUDE-DROP T)\r\n8: (PGLOADER.PGSQL:CREATE-TABLES ((#1=\"TestTable\" #S(PGLOADER.SQLITE::COLDEF :TABLE-NAME #1# :SEQ 0 :NAME \"id\" :DTYPE \"integer\" :CTYPE \"integer\" :NULLABLE T :DEFAULT NIL :PK-ID 1) #S(PGLOADER.SQLITE::COLDEF :TABLE-NAME #1# :SEQ 1 :NAME \"json\" :DTYPE \"blob\" :CTYPE \"blob\" :NULLABLE NIL :DEFAULT \"null\" :PK-ID 0))) :IF-NOT-EXISTS NIL :INCLUDE-DROP T :CLIENT-MIN-MESSAGES :NOTICE)\r\n9: (POSTMODERN::CALL-WITH-TRANSACTION #<CLOSURE (LAMBDA (#:|anonymous-transaction645|) :IN PGLOADER.SOURCES:COPY-DATABASE) {100881558B}>)\r\n10: ((:METHOD PGLOADER.SOURCES:COPY-DATABASE (PGLOADER.SQLITE:COPY-SQLITE)) #<PGLOADER.SQLITE:COPY-SQLITE {1008487713}> :STATE-BEFORE #S(PGLOADER.UTILS::PGSTATE :TABLES #<HASH-TABLE :TEST EQUAL :COUNT 3 {1007EBA5C3}> :TABNAMES (\"create, truncate\" \"fetch meta data\" \"fetch\") :READ 1 :ROWS 1 :ERRS 0 :SECS 0.015) :DATA-ONLY NIL :SCHEMA-ONLY NIL :TRUNCATE NIL :DISABLE-TRIGGERS NIL :CREATE-TABLES T :INCLUDE-DROP T :CREATE-INDEXES T :RESET-SEQUENCES T :ONLY-TABLES #<unused argument> :INCLUDING NIL :EXCLUDING NIL :ENCODING :UTF-8) [fast-method]\r\n11: ((LAMBDA ()))\r\n12: (PGLOADER:RUN-COMMANDS (LAMBDA NIL (LET* ((PGLOADER.PARSER::STATE-BEFORE (PGLOADER.UTILS:MAKE-PGSTATE)) (PGLOADER.PARAMS:*STATE* (PGLOADER.UTILS:MAKE-PGSTATE)) (PGLOADER.SOURCES:*DEFAULT-CAST-RULES* (QUOTE ((:SOURCE (:TYPE \"character\") :TARGET (:TYPE #1=\"text\" :DROP-TYPEMOD T)) (:SOURCE (:TYPE \"varchar\") :TARGET (:TYPE #1# :DROP-TYPEMOD T)) (:SOURCE (:TYPE \"nvarchar\") :TARGET (:TYPE #1# :DROP-TYPEMOD T)) (:SOURCE (:TYPE \"char\") :TARGET (:TYPE #1# :DROP-TYPEMOD T)) (:SOURCE (:TYPE \"nchar\") :TARGET (:TYPE #1# :DROP-TYPEMOD T)) (:SOURCE (:TYPE \"clob\") :TARGET (:TYPE #1# :DROP-TYPEMOD T)) (:SOURCE (:TYPE \"tinyint\") :TARGET (:TYPE \"smallint\")) (:SOURCE (:TYPE \"integer\") :TARGET (:TYPE \"bigint\")) (:SOURCE (:TYPE #2=\"float\") :TARGET (:TYPE #2#) :USING PGLOADER.TRANSFORMS::FLOAT-TO-STRING) (:SOURCE (:TYPE #3=\"real\") :TARGET (:TYPE #3#) :USING PGLOADER.TRANSFORMS::FLOAT-TO-STRING) (:SOURCE (:TYPE \"double\") :TARGET (:TYPE \"double precision\") :USING PGLOADER.TRANSFORMS::FLOAT-TO-STRING) (:SOURCE (:TYPE #4=\"numeric\") :TARGET (:TYPE #4#) :USING PGLOADER.TRANSFORMS::FLOAT-TO-STRING) (:SOURCE (:TYPE \"blob\") :TARGET (:TYPE \"bytea\") :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE \"datetime\") :TARGET (:TYPE #5=\"timestamptz\") :USING PGLOADER.TRANSFORMS::SQLITE-TIMESTAMP-TO-TIMESTAMP) (:SOURCE (:TYPE #6=\"timestamp\") :TARGET (:TYPE #6#) :USING PGLOADER.TRANSFORMS::SQLITE-TIMESTAMP-TO-TIMESTAMP) (:SOURCE (:TYPE #5#) :TARGET (:TYPE #5#) :USING PGLOADER.TRANSFORMS::SQLITE-TIMESTAMP-TO-TIMESTAMP)))) (PGLOADER.SOURCES:*CAST-RULES* (QUOTE NIL)) (PGLOADER.PARAMS:*PG-SETTINGS* (QUOTE NIL)) (PGLOADER.PGSQL::*PGSQL-RESERVED-KEYWORDS* (PGLOADER.PGSQL:LIST-RESERVED-KEYWORDS #7=#<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://pylaligand@UNIX:5432/test-sqlite {1007E49CD3}>)) (PGLOADER.PARAMS:*COPY-BATCH-ROWS* (OR NIL PGLOADER.PARAMS:*COPY-BATCH-ROWS*)) (PGLOADER.PARAMS:*COPY-BATCH-SIZE* (OR NIL PGLOADER.PARAMS:*COPY-BATCH-SIZE*)) (PGLOADER.PARAMS:*CONCURRENT-BATCHES* (OR NIL PGLOADER.PARAMS:*CONCURRENT-BATCHES*)) (PGLOADER.PARSER::SOURCE-DB (PGLOADER.UTILS:WITH-STATS-COLLECTION (\"fetch\" :STATE PGLOADER.PARSER::STATE-BEFORE) (PGLOADER.CONNECTION:EXPAND (PGLOADER.CONNECTION:FETCH-FILE #<PGLOADER.SQLITE:SQLITE-CONNECTION sqlite:///Users/pylaligand/Work/destiny/test.db {1007D0D043}>)))) (PGLOADER.PARSER::SOURCE (MAKE-INSTANCE (QUOTE PGLOADER.SQLITE:COPY-SQLITE) :TARGET-DB #7# :SOURCE-DB PGLOADER.PARSER::SOURCE-DB))) (PGLOADER.SOURCES:COPY-DATABASE PGLOADER.PARSER::SOURCE :STATE-BEFORE PGLOADER.PARSER::STATE-BEFORE :INCLUDING (QUOTE NIL) :EXCLUDING (QUOTE NIL)))) :START-LOGGER NIL :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)\r\n13: ((FLET #:CLEANUP-FUN-264 :IN PGLOADER::MAIN)) [cleanup]\r\n14: (PGLOADER::MAIN (\"pgloader\" \"--debug\" \"--verbose\" \"--type\" \"sqlite\" \"test.db\" \"postgresql:///test-sqlite\"))\r\n15: ((LAMBDA NIL :IN \"/private/tmp/pgloader20151116-11229-1yr3sf7/pgloader-3.2.2/dumper-2SKVI5f7.lisp\"))\r\n16: ((FLET #:WITHOUT-INTERRUPTS-BODY-82 :IN SAVE-LISP-AND-DIE))\r\n17: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))\r\n\r\n\r\ndebugger invoked on a TYPE-ERROR in thread\r\n#<THREAD \"main thread\" RUNNING {1007634903}>:\r\n  The value \"null\"\r\n  is not of type\r\n    (OR NULL (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (*))).\r\n\r\nType HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.\r\n```\r\n\r\nIt looks like using `null` instead of `NULL` makes pgloader think it's a string?"},"comment":{"url":"https://api.github.com/repos/dimitri/pgloader/issues/comments/201926126","html_url":"https://github.com/dimitri/pgloader/issues/377#issuecomment-201926126","issue_url":"https://api.github.com/repos/dimitri/pgloader/issues/377","id":201926126,"user":{"login":"dimitri","id":102900,"avatar_url":"https://avatars.githubusercontent.com/u/102900?v=3","gravatar_id":"","url":"https://api.github.com/users/dimitri","html_url":"https://github.com/dimitri","followers_url":"https://api.github.com/users/dimitri/followers","following_url":"https://api.github.com/users/dimitri/following{/other_user}","gists_url":"https://api.github.com/users/dimitri/gists{/gist_id}","starred_url":"https://api.github.com/users/dimitri/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimitri/subscriptions","organizations_url":"https://api.github.com/users/dimitri/orgs","repos_url":"https://api.github.com/users/dimitri/repos","events_url":"https://api.github.com/users/dimitri/events{/privacy}","received_events_url":"https://api.github.com/users/dimitri/received_events","type":"User","site_admin":false},"created_at":"2016-03-26T20:23:21Z","updated_at":"2016-03-26T20:23:21Z","body":"As I'm not using homebrew for pgloader I don't feel like I'm the one to do it, I wouldn't see breakages..."}}