StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You should be able to feed that dump file straight into <code>psql</code>:</p> <pre><code>/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql </code></pre> <p>If you want the <code>id</code> column to "auto increment" then change its type from "int" to "serial" in the table creation line. PostgreSQL will then attach a sequence to that column so that INSERTs with NULL ids will be automatically assigned the next available value. PostgreSQL will also not recognize <code>AUTOINCREMENT</code> commands, so these need to be removed.</p> <p>You'll also want to check for <code>datetime</code> columns in the SQLite schema and change them to <code>timestamp</code> for PostgreSQL (thanks to <a href="https://stackoverflow.com/users/373402/clay">Clay</a> for pointing this out).</p> <p>If you have booleans in your SQLite then you could convert <code>1</code> and <code>0</code> and <code>1::boolean</code> and <code>0::boolean</code> (respectively) or you could change the boolean column to an integer in the schema section of the dump and then fix them up by hand inside PostgreSQL after the import.</p> <p>If you have BLOBs in your SQLite then you'll want to adjust the schema to use <code>bytea</code>. You'll probably need to mix in some <a href="https://stackoverflow.com/a/3103416/479863"><code>decode</code> calls as well</a>. Writing a quick'n'dirty copier in your favorite language might be easier than mangling the SQL if you a lot of BLOBs to deal with though.</p> <p>As usual, if you have foreign keys then you'll probably want to look into <a href="http://www.postgresql.org/docs/current/static/sql-set-constraints.html" rel="noreferrer"><code>set constraints all deferred</code></a> to avoid insert ordering problems, placing the command inside the BEGIN/COMMIT pair.</p> <p>Thanks to <a href="https://stackoverflow.com/users/6372/nicholas-riley">Nicolas Riley</a> for the boolean, blob, and constraints notes.</p> <p>If you have <code>`</code> on your code, as generated by some SQLite3 clients, you need to remove them.</p> <p>PostGRESQL also doesn't recognize <code>unsigned</code> columns, you might want to drop that, or add a custom-made constraint such as this:</p> <pre><code>CREATE TABLE tablename ( ... unsigned_column_name integer CHECK (unsigned_column_name > 0) ); </code></pre> <p>While SQLite defaults null values to <code>''</code>, PostgreSQL requires them to be set as <code>NULL</code>. </p> <p>The syntax in the SQLite dump file appears to be mostly compatible with PostgreSQL so you can patch a few things and feed it to <code>psql</code>. Importing a big pile of data through SQL INSERTs might take awhile but it'll work.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload