COPY FROM DATABASE SQLite to DuckDB - Catalog Error

[serdardidan]

What happens?

Hi, It cannot be copied due to the CHECK constraint in a column of the SQLite table. I understand it. Because DuckDB does not contain a function called Datetime. When adding valid CHECK constraints to the SQLite table for DuckDB, the database is copied without CHECK constraints. CHECK constraints are not copied anyway. I think it might be a good idea to ignore CHECK restrictions when copying the SQLite database. The same applies to SQLite indexes.

To Reproduce

SQLite:

CREATE TABLE if not exists test(
    insertTime DATETIME CHECK(Datetime(insertTime ,'+0 days') IS substr(insertTime ,1,19)),
    anynteger INTEGER CHECK(anynteger>=0)
);

DuckDB:

attach 's8test.db' as du;
attach 's8.db' as sl (TYPE SQLITE);
COPY FROM DATABASE sl TO du;

Catalog Error: Scalar Function with name datetime does not exist!
Did you mean "make_time"?
LINE 1: COPY FROM DATABASE sl TO du (SCHEMA);

OS:

Windows

DuckDB Version:

v1.0.0

DuckDB Client:

.net (NuGet)

Full Name:

Serdar Didan

Affiliation:

--

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• [X] Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• [X] Yes, I have

[szarnyasg]

@serdardidan thanks for reporting this, we'll take a look and consider ignoring the checks.

I get a slightly different error when running the script:

Binder Error: SQLite databases do not support creating new schemas

[serdardidan]

Thanks for the reply, I tested it again. SQLite:

CREATE TABLE if not exists test(
    insertTime DATETIME CHECK(Datetime(insertTime ,'+0 days') IS substr(insertTime ,1,19)),
    anynteger INTEGER CHECK(anynteger>=0)
);

DuckDB;

attach 'C:\DbTest\\duck\newtest1.db' as du;
attach 'C:\DbTest\newtest1.db' as sl (TYPE SQLITE);
COPY FROM DATABASE sl TO du;

The example above works fine now. I think I copied and pasted the wrong sql before. I am sorry about that. Please try with the following SQL. Datetime is in Default value.

SQLite:

CREATE TABLE if not exists test(
    insertTime DATETIME CHECK(Datetime(insertTime ,'+0 days') IS substr(insertTime ,1,19)) default(Datetime('1900-01-01')),
    anynteger INTEGER CHECK(anynteger>=0)
);

DuckDB:

attach 'C:\DbTest\\duck\newtest2.db' as du;
attach 'C:\DbTest\newtest2.db' as sl (TYPE SQLITE);
COPY FROM DATABASE sl TO du;