Exception: "Unable to find TABLE NAME or COLUMNS"

[ldesousa]

I would like to convert a Postgres database to SQLite with this tool. I created a dump and removed all schema names as instructed. When I run the programme it returns an exception: "Unable to find TABLE NAME or COLUMNS". Full log below.

Thank you for reading.

$ java -jar ~/bin/pg2sqlite/pg2sqlite.jar -d ./iso28258_v1.2.sql -o iso28258_v1.2.db -f true
10:01:48.993 [main] WARN  c.github.caiiiycuk.pg2sqlite.Config$ - You should set SQLITE_TMPDIR environment variable to control where sqlite stores temp files
10:01:49.002 [main] INFO  com.github.caiiiycuk.pg2sqlite.Boot$ - './iso28258_v1.2.sql' (0 Mb) -> 'iso28258_v1.2.db'
10:01:49.004 [main] INFO  c.g.c.pg2sqlite.LoggedIterator - Progress 1.7%, elapsed: 0m 0s 5ms / remaining: 0m 0s 275ms...	
10:01:49.031 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ -
CREATE TABLE - Unable to find TABLE NAME or COLUMNS in 'CREATE TABLE element (     profile_id integer NOT NULL,     order_element integer,     upper_depth integer NOT NULL,     lower_depth integer NOT NULL,     type element_type NOT NULL,     element_id integer NOT NULL,     CONSTRAINT element_check CHECK (((lower_depth > upper_depth) AND (upper_depth <= 200))),     CONSTRAINT element_order_element_check CHECK ((order_element > 0)),     CONSTRAINT element_upper_depth_check CHECK ((upper_depth >= 0)) );' - Exception:
	head of empty list
	[SQL] 'CREATE TABLE element (     profile_id integer NOT NULL,     order_element integer,     upper_depth integer NOT NULL,     lower_depth integer NOT NULL,     type element_type NOT NULL,     element_id integer NOT NULL,     CONSTRAINT element_check CHECK (((lower_depth > upper_depth) AND (upper_depth <= 200))),     CONSTRAINT element_order_element_check CHECK ((order_element > 0)),     CONSTRAINT element_upper_depth_check CHECK ((upper_depth >= 0)) );'
	[LINE #134] CREATE TABLE element (     profile_id integer NOT NULL,     order_element integer,     upper_depth integer NOT NULL,     lower_depth integer NOT NULL,     type element_type NOT NULL,     element_id integer NOT NULL,     CONSTRAINT element_check CHECK (((lower_depth > upper_depth) AND (upper_depth <= 200))),     CONSTRAINT element_order_element_check CHECK ((order_element > 0)),     CONSTRAINT element_upper_depth_check CHECK ((upper_depth >= 0)) );,

10:01:49.579 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ - Task failed...

$ apt policy sqlite3
sqlite3:
  Installed: 3.37.2-2ubuntu0.1
  Candidate: 3.37.2-2ubuntu0.1
  Version table:
 *** 3.37.2-2ubuntu0.1 500
        500 http://nl.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu jammy-security/main amd64 Packages
        100 /var/lib/dpkg/status
     3.37.2-2 500
        500 http://nl.archive.ubuntu.com/ubuntu jammy/main amd64 Packages

$ apt policy postgresql-15
postgresql-15:
  Installed: 15.1-1.pgdg22.04+1
  Candidate: 15.1-1.pgdg22.04+1
  Version table:
 *** 15.1-1.pgdg22.04+1 500
        500 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages
        100 /var/lib/dpkg/status

[caiiiycuk]

I think it's because of constraints, can you try to delete them from dump file?

I talking about constraints like this:
CONSTRAINT element_upper_depth_check CHECK

[ldesousa]

Thank you for the reply @caiiiycuk. Those constraints are structural to the data model, they must remain to guarantee data consistency. However I gave it a try and commented out those constraints. The result is exactly the same as reported above.

[caiiiycuk]

can you share part of your dump to test?

[ldesousa]

can you share part of your dump to test?

Unfortunately not. This is a closed model, my employer owns copyright. I will try to create a SQL file that reproduces this error with different entitties.

[ldesousa]

Hi again. Here below is an annonymised minimal example that reproduces this issue.

CREATE TYPE product_type AS ENUM (
    'Material',
    'Digital'
);

CREATE TABLE product (
    client_id integer NOT NULL,
    order_product integer,
    upper_price integer NOT NULL,
    lower_price integer NOT NULL,
    type product_type NOT NULL,
    product_id integer NOT NULL--,
    CONSTRAINT product_check CHECK (((lower_price > upper_price) AND (upper_price <= 200))),
    CONSTRAINT product_order_product_check CHECK ((order_product > 0)),
    CONSTRAINT product_upper_price_check CHECK ((upper_price >= 0))
);

[caiiiycuk]

@ldesousa The problem was in column name 'upper_price', 'lower_price'. pg2sqlite just check if string starts with 'upper' or 'lower' then tries to cut out function 'upper()' or 'lower()'. Now fixed, try version 1.0.1 (available on releases page).

However you should take in account that sqlite didn't support enums, most constraints, etc. sqlite is much much simplier then postgresql, even more pg2sqlite.jar is tool for creating read-only dump of database ...