Importing data from Postgres

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Importing data from Postgres

Robert Leftwich
Is it still the case that 'the text format used is the same as used by
PostgreSQL' as described at http://sqlite.org/sqlite.html? I'm trying to import
some data from Postgres using the approach described there i.e.

     sqlite ex3 <schema.sql
     pg_dump -a ex2 | sqlite ex3

and it is failing with a lot of errors, such as SQL error: unrecognized token: "\".

I've tried with both sqlite v2.8.15 and 3.0.8 against Postgres 8.01 w/o success.

Robert

Reply | Threaded
Open this post in threaded view
|

Re: Importing data from Postgres

Christian Smith
On Thu, 17 Nov 2005, Robert Leftwich wrote:

>Is it still the case that 'the text format used is the same as used by
>PostgreSQL' as described at http://sqlite.org/sqlite.html? I'm trying to import
>some data from Postgres using the approach described there i.e.
>
>     sqlite ex3 <schema.sql
>     pg_dump -a ex2 | sqlite ex3
>
>and it is failing with a lot of errors, such as SQL error: unrecognized token: "\".
>
>I've tried with both sqlite v2.8.15 and 3.0.8 against Postgres 8.01 w/o success.


The above is really not enough to go on.

We need more information on your:
- Schema
- Data
- Sample output from pg_dump


>
>Robert
>
>

--
    /"\
    \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
     X                           - AGAINST MS ATTACHMENTS
    / \
Reply | Threaded
Open this post in threaded view
|

Re: Importing data from Postgres

Robert Leftwich
Christian Smith wrote:

> On Thu, 17 Nov 2005, Robert Leftwich wrote:
>>
>>Is it still the case that 'the text format used is the same as used by
>>PostgreSQL' as described at http://sqlite.org/sqlite.html? I'm trying to import
>>some data from Postgres using the approach described there i.e.
>>
>>    sqlite ex3 <schema.sql
>>    pg_dump -a ex2 | sqlite ex3
>>
>>and it is failing with a lot of errors, such as SQL error: unrecognized token: "\".
>>
>>I've tried with both sqlite v2.8.15 and 3.0.8 against Postgres 8.01 w/o success.
>
> The above is really not enough to go on.
>
> We need more information on your:
> - Schema
> - Data
> - Sample output from pg_dump
>

Well, the question was intended to find out if the documentation was accurate,
i.e. should it work as described. If not, then I wouldn't waste any more of the
lists or my time on it. That said, it doesn't seem to matter what data I throw
at it, it fails every time. Here is the unedited output from 'pg_dump -a' for a
test database:

--
-- PostgreSQL database dump
--

SET client_encoding = 'UNICODE';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY customer (code, balance) FROM stdin;
10      0
11      0
12      345.66
13      536.75
14      0
15      0
16      221.19
17      768.92999
18      216.55
19      0
\.


--
-- Data for Name: invoice; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY invoice (num, code, date) FROM stdin;
1       14      2004-01-16
2       11      2004-01-15
3       12      2004-01-16
4       11      2004-01-17
5       18      2004-01-17
6       14      2004-01-17
7       15      2004-01-17
8       11      2004-01-17
\.


--
-- Data for Name: line; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY line (num, line_num, p_code, units, price) FROM stdin;
1       1       123     1       14.9
1       2       \N      1       9.9499998
2       1       \N      2       4.9899998
3       1       \N      1       38.950001
3       2       \N      1       39.950001
3       3       \N      5       14.99
4       1       \N      3       4.9899998
4       2       \N      2       9.9499998
5       1       \N      12      5.8699999
\.


--
-- PostgreSQL database dump complete
--

And the sql used to create the tables:

CREATE TABLE customer
(
   code int4 NOT NULL,
   balance float4,
   CONSTRAINT customer_pkey PRIMARY KEY (code)
);

CREATE TABLE invoice
(
   num int4 NOT NULL,
   code int4 NOT NULL,
   date date,
   CONSTRAINT invoice_pkey PRIMARY KEY (num),
   CONSTRAINT invoice_code_fkey FOREIGN KEY (code) REFERENCES customer (code) ON
UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE TABLE line
(
   num int4 NOT NULL,
   line_num int4 NOT NULL,
   p_code int4,
   units int4,
   price float4,
   CONSTRAINT line_pkey PRIMARY KEY (num, line_num),
   CONSTRAINT line_num_fkey FOREIGN KEY (num) REFERENCES invoice (num) ON UPDATE
RESTRICT ON DELETE RESTRICT
);


and here is the output of  pg_dump -a tst | sqlite tst

SET client_encoding = 'UNICODE';
SQL error: near "SET": syntax error
SET check_function_bodies = false;
SQL error: near "SET": syntax error
SET client_min_messages = warning;
SQL error: near "SET": syntax error
SET search_path = public, pg_catalog;
SQL error: near "SET": syntax error
COPY customer (code, balance) FROM stdin;
SQL error: near "(": syntax error
10      0
11      0
12      345.66
13      536.75
14      0
15      0
16      221.19
17      768.92999
18      216.55
19      0
\.


--
-- Data for Name: invoice; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY invoice (num, code, date) FROM stdin;
SQL error: unrecognized token: "\"
1       14      2004-01-16
2       11      2004-01-15
3       12      2004-01-16
4       11      2004-01-17
5       18      2004-01-17
6       14      2004-01-17
7       15      2004-01-17
8       11      2004-01-17
\.


--
-- Data for Name: line; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY line (num, line_num, p_code, units, price) FROM stdin;
SQL error: unrecognized token: "\"
Incomplete SQL: 1       1       123     1       14.9
1       2       \N      1       9.9499998
2       1       \N      2       4.9899998
3       1       \N      1       38.950001
3       2       \N      1       39.950001
3       3       \N      5       14.99
4       1       \N      3       4.9899998
4       2       \N      2       9.9499998
5       1       \N      12      5.8699999
\.


--
-- PostgreSQL database dump complete
--

Robert

Reply | Threaded
Open this post in threaded view
|

Re: Importing data from Postgres

Dennis Cote
Robert Leftwich wrote:

> Christian Smith wrote:
>
>> On Thu, 17 Nov 2005, Robert Leftwich wrote:
>>
>>>
>>> Is it still the case that 'the text format used is the same as used by
>>> PostgreSQL' as described at http://sqlite.org/sqlite.html? I'm
>>> trying to import
>>> some data from Postgres using the approach described there i.e.
>>>
>>>    sqlite ex3 <schema.sql
>>>    pg_dump -a ex2 | sqlite ex3
>>>
>>> and it is failing with a lot of errors, such as SQL error:
>>> unrecognized token: "\".
>>>
>>> I've tried with both sqlite v2.8.15 and 3.0.8 against Postgres 8.01
>>> w/o success.
>>
>>
>> The above is really not enough to go on.
>>
>> We need more information on your:
>> - Schema
>> - Data
>> - Sample output from pg_dump
>>
>
> Well, the question was intended to find out if the documentation was
> accurate, i.e. should it work as described. If not, then I wouldn't
> waste any more of the lists or my time on it. That said, it doesn't
> seem to matter what data I throw at it, it fails every time. Here is
> the unedited output from 'pg_dump -a' for a test database:
>
> --
> -- PostgreSQL database dump
> --
>
> SET client_encoding = 'UNICODE';
> SET check_function_bodies = false;
> SET client_min_messages = warning;
>
> SET search_path = public, pg_catalog;
>
> --
> -- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner:
> postgres
> --
>
> COPY customer (code, balance) FROM stdin;
> 10      0
> 11      0
> 12      345.66
> 13      536.75
> 14      0
> 15      0
> 16      221.19
> 17      768.92999
> 18      216.55
> 19      0
> \.
>
>
> --
> -- Data for Name: invoice; Type: TABLE DATA; Schema: public; Owner:
> postgres
> --
>
> COPY invoice (num, code, date) FROM stdin;
> 1       14      2004-01-16
> 2       11      2004-01-15
> 3       12      2004-01-16
> 4       11      2004-01-17
> 5       18      2004-01-17
> 6       14      2004-01-17
> 7       15      2004-01-17
> 8       11      2004-01-17
> \.
>
>
> --
> -- Data for Name: line; Type: TABLE DATA; Schema: public; Owner: postgres
> --
>
> COPY line (num, line_num, p_code, units, price) FROM stdin;
> 1       1       123     1       14.9
> 1       2       \N      1       9.9499998
> 2       1       \N      2       4.9899998
> 3       1       \N      1       38.950001
> 3       2       \N      1       39.950001
> 3       3       \N      5       14.99
> 4       1       \N      3       4.9899998
> 4       2       \N      2       9.9499998
> 5       1       \N      12      5.8699999
> \.
>
>
> --
> -- PostgreSQL database dump complete
> --
>
> And the sql used to create the tables:
>
> CREATE TABLE customer
> (
>   code int4 NOT NULL,
>   balance float4,
>   CONSTRAINT customer_pkey PRIMARY KEY (code)
> );
>
> CREATE TABLE invoice
> (
>   num int4 NOT NULL,
>   code int4 NOT NULL,
>   date date,
>   CONSTRAINT invoice_pkey PRIMARY KEY (num),
>   CONSTRAINT invoice_code_fkey FOREIGN KEY (code) REFERENCES customer
> (code) ON UPDATE RESTRICT ON DELETE RESTRICT
> );
>
> CREATE TABLE line
> (
>   num int4 NOT NULL,
>   line_num int4 NOT NULL,
>   p_code int4,
>   units int4,
>   price float4,
>   CONSTRAINT line_pkey PRIMARY KEY (num, line_num),
>   CONSTRAINT line_num_fkey FOREIGN KEY (num) REFERENCES invoice (num)
> ON UPDATE RESTRICT ON DELETE RESTRICT
> );
>
>
> and here is the output of  pg_dump -a tst | sqlite tst
>
> SET client_encoding = 'UNICODE';
> SQL error: near "SET": syntax error
> SET check_function_bodies = false;
> SQL error: near "SET": syntax error
> SET client_min_messages = warning;
> SQL error: near "SET": syntax error
> SET search_path = public, pg_catalog;
> SQL error: near "SET": syntax error
> COPY customer (code, balance) FROM stdin;
> SQL error: near "(": syntax error
> 10      0
> 11      0
> 12      345.66
> 13      536.75
> 14      0
> 15      0
> 16      221.19
> 17      768.92999
> 18      216.55
> 19      0
> \.
>
>
> --
> -- Data for Name: invoice; Type: TABLE DATA; Schema: public; Owner:
> postgres
> --
>
> COPY invoice (num, code, date) FROM stdin;
> SQL error: unrecognized token: "\"
> 1       14      2004-01-16
> 2       11      2004-01-15
> 3       12      2004-01-16
> 4       11      2004-01-17
> 5       18      2004-01-17
> 6       14      2004-01-17
> 7       15      2004-01-17
> 8       11      2004-01-17
> \.
>
>
> --
> -- Data for Name: line; Type: TABLE DATA; Schema: public; Owner: postgres
> --
>
> COPY line (num, line_num, p_code, units, price) FROM stdin;
> SQL error: unrecognized token: "\"
> Incomplete SQL: 1       1       123     1       14.9
> 1       2       \N      1       9.9499998
> 2       1       \N      2       4.9899998
> 3       1       \N      1       38.950001
> 3       2       \N      1       39.950001
> 3       3       \N      5       14.99
> 4       1       \N      3       4.9899998
> 4       2       \N      2       9.9499998
> 5       1       \N      12      5.8699999
> \.
>
>
> --
> -- PostgreSQL database dump complete
> --
>
> Robert
>
>
Robert,

I think you are out of luck going directly. The SQLite documentation at
http://www.sqlite.org/lang_copy.html says that the COPY command does not
work at all in SQLite since version 3.0. Also, it looks like the
PostgreSQL dump output is sending the table creation SQL commands after
the COPY commands so it won't work with version 2.8 either.  I suspect
that the dump output format was probably changed in PostgreSQL version
8, and SQLite doesn't say what version it was supposed to be compatible
with.

As the documents you referred to originally state, with version 2.8 you
should be able to copy the final CREATE statements to another file, and
edit them so they are acceptable to SQLite (primarily removing the ON
UPDATE RESTRICT... clauses I think). Then feed those commands into
SQLite to create the tables. Then you should be able to process the COPY
commands after you strip out the initial SET statements (and remove the
CREATE statements at the end). This should give you a SQLite version 2.8
database. You can then dump that to a text file using the SQLite shell's
.dump command (using version 2.8), and load that into a SQLite version
3.0 database using the latest version of the SQLite shell.

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Importing data from Postgres

John DeSoi
In reply to this post by Robert Leftwich

On Nov 17, 2005, at 3:53 PM, Robert Leftwich wrote:

> Well, the question was intended to find out if the documentation  
> was accurate, i.e. should it work as described. If not, then I  
> wouldn't waste any more of the lists or my time on it. That said,  
> it doesn't seem to matter what data I throw at it, it fails every  
> time. Here is the unedited output from 'pg_dump -a' for a test  
> database:

If you use the -d (or -D) option to pg_dump you'll get INSERT  
statements rather than COPY. This is more likely to work with SQLite.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Reply | Threaded
Open this post in threaded view
|

Re: Importing data from Postgres

Robert Leftwich
John DeSoi wrote:

>
> On Nov 17, 2005, at 3:53 PM, Robert Leftwich wrote:
>
>> Well, the question was intended to find out if the documentation  was
>> accurate, i.e. should it work as described. If not, then I  wouldn't
>> waste any more of the lists or my time on it. That said,  it doesn't
>> seem to matter what data I throw at it, it fails every  time. Here is
>> the unedited output from 'pg_dump -a' for a test  database:
>
>
> If you use the -d (or -D) option to pg_dump you'll get INSERT  
> statements rather than COPY. This is more likely to work with SQLite.
>

Thanks for that - I will try that approach next and report back.

I managed to import using the COPY on v2.8, after manually removing the column
list(s) in the pg_dump generated COPY commands. It was fairly slow, but it is a
large data set (2 of the tables have around a million rows - FWIW I'm looking at
using sqlite as the backend for demos/laptops).

Robert

Reply | Threaded
Open this post in threaded view
|

Re: Importing data from Postgres

jreidthompson
In reply to this post by John DeSoi
John DeSoi wrote:

>
> On Nov 17, 2005, at 3:53 PM, Robert Leftwich wrote:
>
>> Well, the question was intended to find out if the documentation  was
>> accurate, i.e. should it work as described. If not, then I  wouldn't
>> waste any more of the lists or my time on it. That said,  it doesn't
>> seem to matter what data I throw at it, it fails every  time. Here is
>> the unedited output from 'pg_dump -a' for a test  database:
>
>
> If you use the -d (or -D) option to pg_dump you'll get INSERT  
> statements rather than COPY. This is more likely to work with SQLite.
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
a quick pg_dump --help will show that you can dump the schema by itself
also, you could make whatever minor changes you need, load the schema to
sqlite, then load data from the pg_dump -d/D .

if you're interested,,,
RUBY and Og can also be setup to dynamically load the schema/data from
one DB to the other..