Sarit Sarit - 18 days ago 5
Python Question

How to restore APSchduelder sqlite to Postgres

I am migrating from sqlite to postgres.

Here is dumpfile.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE apscheduler_jobs (
id VARCHAR(191) NOT NULL,
next_run_time FLOAT,
job_state BLOB NOT NULL,
PRIMARY KEY (id)
);


I am trying to create the same schema like APScheduler does in the sqlite by following the migration from this repo https://github.com/jarekwg/django-apscheduler/blob/master/django_apscheduler/migrations/0001_initial.py

Then the sql command is:

CREATE TABLE "apscheduler_jobs" ("id" varchar(255) NOT NULL PRIMARY KEY, "next_run_time" NUMERIC(11,2) NOT NULL, "job_state" bytea NOT NULL);
CREATE INDEX "apscheduler_jobs_83d3412e" ON "apscheduler_jobs" ("next_run_time");
CREATE INDEX "apscheduler_jobs_id_9f0be75e_like" ON "apscheduler_jobs" ("id" varchar_pattern_ops);


I had prepared the database by the followings:

CREATE DATABASE apscheduler;
GRANT ALL PRIVILEGES ON database apscheduler to uih;


My question:
Refer to this url. I have tried casting the input to be a
bytea

https://www.postgresql.org/docs/9.4/static/functions-string.html

INSERT INTO "apscheduler_jobs"
VALUES('c891c2288a0f4585b169a335dd57b971',
1.51008480006619596482e+09,
decode(X'800495EA010000000000007D94288C126D6973666972655F67726163655F74696D65944B018C086578656375746F72948C0764656661756C74948C0D6D61785F696E7374616E636573944B018C0466756E63948C1E7363686564756C65722E747269676765723A747269676765725F66756E63948C08636F616C6573636594888C066B7761726773947D948C0774726967676572948C1961707363686564756C65722E74726967676572732E64617465948C0B44617465547269676765729493942981947D94288C0872756E5F64617465948C086461746574696D65948C086461746574696D65949394430A07E10B08030000010294948C0E646174657574696C2E747A2E747A948C08747A6F66667365749493942981947D94288C075F6F6666736574948C086461746574696D65948C0974696D6564656C74619493944B004D70624B00879452948C055F6E616D65944E7562869452948C0776657273696F6E944B01756268234B018C046E616D65948C206338393163323238386130663435383562313639613333356464353762393731948C0269649468258C0D6E6578745F72756E5F74696D659468228C04617267739468258C382F6170692F7363686564756C65732F747269676765722F63383931633232383861306634353835623136396133333564643537623937312F948694752E', 'base64')
)
[2016-11-17 17:16:47] [42883] ERROR: function decode(bit, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 117


What is the correct syntax to
restore
my
sqlite
dump file to
postgres
?

Update:
enter image description here
I have new question on it. 2nd line comes from manual
INSERT
. I am somewhat worry on it. I will try test and back to here again.

Update:
Decimal is not a problem

Answer

I'm not sure if I get you right. I truncated your long string and changed from binary to bytea and inserted value. Is it what you try to do?:

t=# create table b5 (bta bytea);
CREATE TABLE
t=# insert into b5 select X'800495EA0100';
ERROR:  column "bta" is of type bytea but expression is of type bit
LINE 1: insert into b5 select X'800495EA0100';
                              ^
HINT:  You will need to rewrite or cast the expression.
t=# insert into b5 select E'\\x800495EA0100';
INSERT 0 1
t=# select * from b5;
      bta
----------------
 \x800495ea0100
(1 row)

for your insert to work it should look like:

INSERT INTO "apscheduler_jobs"
VALUES('c891c2288a0f4585b169a335dd57b971',
       1.51008480006619596482e+09,
       E'\\x800495EA010000000000007D94288C126D6973666972655F67726163655F74696D65944B018C086578656375746F72948C0764656661756C74948C0D6D61785F696E7374616E636573944B018C0466756E63948C1E7363686564756C65722E747269676765723A747269676765725F66756E63948C08636F616C6573636594888C066B7761726773947D948C0774726967676572948C1961707363686564756C65722E74726967676572732E64617465948C0B44617465547269676765729493942981947D94288C0872756E5F64617465948C086461746574696D65948C086461746574696D65949394430A07E10B08030000010294948C0E646174657574696C2E747A2E747A948C08747A6F66667365749493942981947D94288C075F6F6666736574948C086461746574696D65948C0974696D6564656C74619493944B004D70624B00879452948C055F6E616D65944E7562869452948C0776657273696F6E944B01756268234B018C046E616D65948C206338393163323238386130663435383562313639613333356464353762393731948C0269649468258C0D6E6578745F72756E5F74696D659468228C04617267739468258C382F6170692F7363686564756C65732F747269676765722F63383931633232383861306634353835623136396133333564643537623937312F948694752E'
)
Comments