Re: Importing SQLite database - Mailing list pgsql-general

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.

>
> 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?

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