Re: Importing SQLite database
От | Adrian Klaver |
---|---|
Тема | Re: Importing SQLite database |
Дата | |
Msg-id | e2b6afac-b532-81fa-8381-6b652f84658c@aklaver.com обсуждение исходный текст |
Ответ на | Re: Importing SQLite database (Igor Korot <ikorot01@gmail.com>) |
Ответы |
Re: Importing SQLite database
("Charles Clavadetscher" <clavadetscher@swisspug.org>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: