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
