Re: [GENERAL] Importing SQLite database

Hi, guys,

On Thu, Dec 8, 2016 at 10:19 AM, Charles Clavadetscher
<clavadetscher@swisspug.org> wrote:
> Hello
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver
>> Sent: Donnerstag, 8. Dezember 2016 16:09
>> To: Igor Korot <ikorot01@gmail.com>
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] 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.
>
> Adding to that. The sequence is unaware of the value that already are available in the column. Your insert statements
willcreate rows without changing the sequence. That means that after finishing the import you will need to set the
valueof the sequence to the maximum value available in the column. 
>
> Here an example:
>
> db=> create table test (id serial primary key, txt text);
> CREATE TABLE
> db=> \d test
>                          Table "public.test"
>  Column |  Type   |                     Modifiers
> --------+---------+---------------------------------------------------
>  id     | integer | not null default nextval('test_id_seq'::regclass)
>  txt    | text    |
> Indexes:
>     "test_pkey" PRIMARY KEY, btree (id)
>
> db=> insert into test values (1,'asdf'),(2,'fdgd'),(3,'werwe');
> INSERT 0 3
>
> db=> select * from test_id_seq;
>  sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt |
is_cycled| is_called 
>
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
>  test_id_seq   |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 |
f        | f 
> (1 row)
>
> Since the value of the sequence still is 1 you may get into trouble:
>
> db=> insert into test (txt) values ('hallo');
> ERROR:  duplicate key value violates unique constraint "test_pkey"
> DETAIL:  Key (id)=(1) already exists.
>
> So you set the value of the sequence:
>
> db.archivedb=> select setval('test_id_seq'::regclass,(select max(id) from test));
>  setval
> --------
>       3
> (1 row)
>
> And then everything works as expected.
>
> db=> insert into test (txt) values ('hallo');
> INSERT 0 1
> kofadmin@kofdb.archivedb=> select * from test;
>  id |  txt
> ----+-------
>   1 | asdf
>   2 | fdgd
>   3 | werwe
>   4 | hallo
> (4 rows)
>
> Hope this helps.
> Bye
> Charles
>
>>
>> >
>> >>
>> >> 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

This is the result of running "SQL shell":

[code]
Last login: Thu Dec  8 19:46:41 on ttys001
Igors-MacBook-Air:~ igorkorot$ /Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.1.24)
Type "help" for help.

postgres=#
[/code]

And this is the result of running "psql" command in Terminal:

[code]
Igors-MacBook-Air:dbhandler igorkorot$ psql -U postgres -d postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
[/code]

Any idea why I can't connect?

Also is PostgreSQL is set initially to use "Transaction-mode"? I.e.
every SQL command should end up with COMMIT?

Thank you.

>> >>
>> >
>> > 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
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>