Dump file from Sqlite3 to PostgreSQL: why do I always get errors when import it?

Question

I have many tables in Sqlite3 db and now I want to export it to PostgreSQL, but all the time I get errors. I've used different techniques to dump from sqlite:

.mode csv

.header on

.out ddd.sql

select * from my_table

and

.mode insert

.out ddd.sql

select * from my_table

And when I try to import it through phppgadmin I get errors like this:

ERROR: column "1" of relation "my_table" does not exist

LINE 1: INSERT INTO "public"."my_table" ("1", "1", "Vitas", "[email protected]", "..

How to avoid this error?

Thanks in advance!

Apparently the table is not there ("does not exist"), so you need to create the table my_table before you can insert into it. — user330315
– user330315, Commented Jan 27, 2012 at 9:22

filiprem · Accepted Answer · 2012-01-27 14:27:04Z

Rant

You get this "column ... does not exist" error with INSERT INTO "public"."my_table" ("1", ... - because quotes around the "1" mean this is an identifier, not literal.

Even if you fix this, the query still will give error, because of missing VAULES keyword, as Jan noticed in other answer.

The correct form would be:

INSERT INTO "public"."my_table" VALUES ('1', ...

If this SQL was autogenerated by sqlite, bad for sqlite.

This great chapter about SQL syntax is only about 20 pages in print. My advice to whoever generated this INSERT, is: read it :-) it will pay off.

Real solution

Now, to the point... To transfer table from sqlite to postgres, you should use COPY because it's way faster than INSERT.

Use CSV format as it's understood on both sides.

In sqlite3:

create table tbl1(one varchar(20), two smallint);
insert into tbl1 values('hello',10);
insert into tbl1 values('with,comma', 20);
insert into tbl1 values('with "quotes"', 30);
insert into tbl1 values('with
enter', 40);
.mode csv
.header on
.out tbl1.csv
select * from tbl1;

In PostgreSQL (psql client):

create table tbl1(one varchar(20), two smallint);
\copy tbl1 from 'tbl1.csv' with csv header delimiter ','
select * from tbl1;

See http://wiki.postgresql.org/wiki/COPY.

Jan Marek · Accepted Answer · 2012-01-27 11:57:30Z

2

Seems there is missing "VALUES" keyword:

INSERT INTO "public"."my_table" VALUES (...)

But! - You have to insert values with appropriate quotes - single quotes for text and without quotes for numbers.

answered Jan 27, 2012 at 11:57

Jan Marek

11.3k3 gold badges24 silver badges19 bronze badges

Collectives™ on Stack Overflow

Dump file from Sqlite3 to PostgreSQL: why do I always get errors when import it?

2 Answers 2

Rant

Real solution

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Rant

Real solution

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related