Re: Importing SQLite database

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Igor Korot <ikorot01(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Importing SQLite database
Date: 2016-12-08 14:47:24
Message-ID: e79e82ab-5b1a-88fb-f2df-6fd2fee89268@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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.

The issue is that Postgres will not understand AUTOINCREMENT. In
Postgres to get the same behavior you would do:

id SERIAL PRIMARY KEY

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

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(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Igor Korot 2016-12-08 14:54:36 Re: Importing SQLite database
Previous Message John DeSoi 2016-12-08 14:11:01 Re: PDF files: to store in database or not