Hi, guys,
On Thu, Dec 8, 2016 at 10:19 AM, Charles Clavadetscher
<clavadetsc...@swisspug.org> wrote:
> Hello
>
>> -----Original Message-----
>> From: pgsql-general-ow...@postgresql.org
>> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
>> Sent: Donnerstag, 8. Dezember 2016 16:09
>> To: Igor Korot <ikoro...@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.kla...@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 will create rows without
> changing the sequence. That means that after finishing the import you will
> need to set the value of 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.kla...@aklaver.com
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@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
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
