Issue
I'm trying to import a postgres dump into a sqlite3 database. Now pg_dump add the database name to the expressions and this is not good for sqlite3.
CREATE TABLE dbname.table
Is it possible to tell sqlite3 to ignore database name? The next solution is to try to write a regexp that modifies the sql file but I'm not a regexp magician, I've obtained something along the lines of:
printf "\nCREATE TABLE dbname.data;\nINSERT INTO dbname.data VALUES (\"the data in dbname.\")\n" | sed -e '/^CREATE TABLE/s/dbname.//g' -e '/^INSERT INTO/s/dbname.//g'
But this is incorrect cause I want to substitute only the first occurrence...
Can you give me some suggestion?
Solution
You actually don't have to change your file of SQL statements:
$ sqlite3
sqlite> ATTACH 'your_database.db' AS dbname;
sqlite> .read dump_file.sql
ATTACH will open a database using the schema name dbname
so that dbname.tablename
will refer to it.
Answered By - Shawn