Re: Importing SQLite database

On 12/08/2016 06:54 AM, Igor Korot wrote:
> Adrian,
>
> On Thu, Dec 8, 2016 at 9:47 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 12/08/2016 04:54 AM, Igor Korot wrote:
>>>
>>> Hi, ALL,
>>> I have a text file which I got from exporting the SQLite database.
>>>
>>> The file contains an SQL statement which will generate the database.
>>
>>
>> Is there a CREATE DATABASE statement in the file you are referencing?
>
> Well there is no CREATE DATABASE() in the SQLite.
> But I created a database by hand, so no issue here.
>
>>
>> Otherwise you will have to create the database first and then load the file
>> into it.
>>
>>>
>>> Excerpt from this file:
>>>
>>> [code]
>>> CREATE TABLE leagues( id INTEGER AUTOINCREMENT PRIMARY KEY, name
>>> VARCHAR(100),balance DOUBLE(10,2));
>>> CREATE TABLE players(playerid INTEGER AUTOINCREMENT PRIMARY KEY,
>>> player_name VARCHAR(60), player_position CHAR(1));
>>> CREATE TABLE player_draft(id INTEGER, playerid INTEGER,
>>> drafted_position CHAR(1), FOREIGN KEY id REFERENCE leagues(id),
>>> FOREIGN KEY playerid REFERENCE players(playerid));
>>> INSERT INTO leagues VALUES(1, 'Test1', 260.00);
>>> INSERT INTO leagues VALUES(2, 'Test2', 260.00);
>>> INSERT INTO players VALUES(1, 'Player1', 'D');
>>> INSERT INTO players VALUES(2, ''Player2', 'F');
>>> [/code]
>>>
>>> My question would be:
>>> Is there a command in Postgres which will open this file and execute all
>>> those
>>> commands one-by-one in a transaction?
>>
>>
>> Yes there is assuming the [code][/code] tags are for the email only.
>
> Yes, "code" tags are for e-mail only.
>
>>
>> The issue is that Postgres will not understand AUTOINCREMENT. In Postgres to
>> get the same behavior you would do:
>>
>> id SERIAL PRIMARY KEY
>
> I'm not very familiar with Postgres, so let me ask you - is
> autoincrementing behavior set
> by default for the primary key?
> Like if I want to insert the record in the table and if I omit this
> column it will get the last inserted
> value in this table plus 1.

No that is a Sqlite thing:
http://sqlite.org/autoinc.html


If you want to replicate in Postgres you will need to use the SERIAL type:

https://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL

along with PRIMARY KEY so:

id SERIAL PRIMARY KEY

This sets up a SEQUENCE:

https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

for the id column, which supplies an incrementing, but not necessarily
gap free sequence of numbers.

>
>>
>> If you clean up the file you can do, assuming you created a database called
>> some_database:
>>
>> psql -d some_database -U some_user -f your_file
>>
>
> The file also contains line "BEGIN TRANSACTION" as the first line and
> "COMMIT" as last.
> Is the syntax the same for Postgres or is it different?

It is the same.

See below for list of Postgres commands:

https://www.postgresql.org/docs/9.5/static/sql-commands.html

>
> Thank you.
>
>>
>> If you do not want to do the manual clean up, then Willam's suggestion looks
>> interesting.
>>
>>
>>
>>> Or I will have to do a manual table creation, then split this file and
>>> use "LOAD..."
>>> command to load the data into the tables?
>>>
>>> Hopefully I made myself clear.
>>> Let me know if not and I will try to clarify further.
>>>
>>> Thank you.
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com