Are schemas supported?

[arkanoid87]

`~/s/pg_to_sqlite_convert ❯❯❯ java -jar postgresql-to-sqlite/pg2sqlite.jar -d 54039.dump -o 54039.db
19:55:36.574 [main] WARN c.github.caiiiycuk.pg2sqlite.Config$ - You should set SQLITE_TMPDIR environment variable to control where sqlite stores temp files
19:55:36.612 [main] INFO com.github.caiiiycuk.pg2sqlite.Boot$ - '54039.dump' (27 Mb) -> '54039.db'
19:55:37.875 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ -
Create Table - Exception:
unknown database schema
[SQL] 'CREATE TABLE schema.risorsa ([id], [macrorisorsa], [idinterno], [descrizione], [idtipo], [peso], [disponibile], [idstruttura], [annotaz], [dataultimamod]);'
[LINE #225] CREATE TABLE schema.risorsa ( "ID" integer NOT NULL, "macroRisorsa" smallint NOT NULL, "IDinterno" integer NOT NULL, descrizione text, "IDtipo" smallint NOT NULL,
peso smallint, disponibile boolean, "IDstruttura" smallint NOT NULL, annotaz text, "dataUltimaMod" date DEFAULT ('now'::text)::date NOT NULL );,

19:55:37.876 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ - Task failed...
~/s/pg_to_sqlite_convert ❯❯❯`

[caiiiycuk]

Looks no you can drop schema with regex, or maybe there is such option for pg_dump

[joshiga]

Hi,

I'm getting following error while converting a dump created from postgressql 9.6 db to sqlite,

CREATE TABLE - Unable to find TABLE NAME or COLUMNS in 'CREATE TABLE "mytable_name "( column names type) '  Exception:
        head of empty list
        [SQL] 'CREATE TABLE "mytable_name" (     id integer NOT NULL,  );,

Could you please help fixing this issue?

[pklapperich]

All of my tables for this dump file are in the public schema. I was able to do the conversion with:

$ pg_dump postgres://{USER}:{PASS}@localhost/{DATABASE} -f output.dump
$ sed 's/public\.//' -i  output.dump
$ pg2sqlite -d output.dump -o sqlite.db

It would definitely be nice if pg2sqlite could just handle striping off the schema names on its own.

---

@joshiga you should open a new ticket for your issue.

[caiiiycuk]

@pklapperich Yeahh, it will be great.

[michaelmior]

Here's a simple example that fails for me.

CREATE TABLE public.foo(a INT);
COPY public.foo(a) FROM stdin;
1
2
3
\.

With no schema, it works fine.

[michaelmior]

It might be worth noting in the README that while the sed solution will probably work to get the data loaded, it has the potential to corrupt your data. If you have the string public. stored in a database column, it may be unexpectedly removed.

[caiiiycuk]

Yeah you are right.