On 12/08/2016 06:54 AM, Igor Korot wrote:
Adrian,
On Thu, Dec 8, 2016 at 9:47 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
