Importing SQLite database

OmniDB [1]http://www.omnidb.com.br/en_index.aspx is able to convert databases from one RDBMS to another. I
suggest you perform a conversion from your SQLite file to a PostgreSQL
database.
This page [2]https://github.com/OmniDB/OmniDB/wiki/3.-Creating-Users-and-Connections can help you on how to add connections to OmniDB and this
other page [3]https://github.com/OmniDB/OmniDB/wiki/10.-Schema-Conversion explains how to use the convert feature.

[1]: http://www.omnidb.com.br/en_index.aspx
[2]: https://github.com/OmniDB/OmniDB/wiki/3.-Creating-Users-and-Connections
[3]: https://github.com/OmniDB/OmniDB/wiki/10.-Schema-Conversion

Em qui, 8 de dez de 2016 às 10:55, Igor Korot <ikorot01@gmail.com> escreveu:

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.

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?
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

William Ivanski

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.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

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.

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian,

On Thu, Dec 8, 2016 at 9:54 AM, Igor Korot <ikorot01@gmail.com> 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

Anything else you see?
Does Postgres uses single quotes for literal values or double quotes?
I'm talking about
INSERT INTO... VALUES(); statements.

SQLite does use double quotes, but I already cleaned it using single ones.

Thank you.

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.

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?

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/08/2016 07:02 AM, Igor Korot wrote:

Adrian,

Anything else you see?
Does Postgres uses single quotes for literal values or double quotes?

Single:

https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html

I'm talking about
INSERT INTO... VALUES(); statements.

SQLite does use double quotes, but I already cleaned it using single ones.

That may be going away:

http://sqlite.org/lang_keywords.html

"For resilience when confronted with historical SQL statements, SQLite
will sometimes bend the quoting rules above:

If a keyword in single quotes (ex: 'key' or 'glob') is used in a
context where an identifier is allowed but where a string literal is not
allowed, then the token is understood to be an identifier instead of a
string literal.

If a keyword in double quotes (ex: "key" or "glob") is used in a
context where it cannot be resolved to an identifier but where a string
literal is allowed, then the token is understood to be a string literal
instead of an identifier.

Programmers are cautioned not to use the two exceptions described in the
previous bullets. We emphasize that they exist only so that old and
ill-formed SQL statements will run correctly. Future versions of SQLite
might raise errors instead of accepting the malformed statements covered
by the exceptions above."

Thank you.

--
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

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 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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/08/2016 04:47 PM, Igor Korot wrote:

Hi, guys,

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?

Because you are trying a local socket connection and psql cannot find
the socket. Not sure where the Apple install would create it, so cannot
help there. However if you replicate the connection that worked with SQL
shell you should be able to connect. So:

psql -U postgres -d postgres -h localhost

FYI, not having the -h tells psql to connect using a socket.

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

That is more of a client side attribute. For psql the default is to
enclose every command in a transaction, so no explicit BEGIN/COMMIT is
needed:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"AUTOCOMMIT

When on (the default), each SQL command is automatically committed
upon successful completion. To postpone commit in this mode, you must
enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL
commands are not committed until you explicitly issue COMMIT or END. The
autocommit-off mode works by issuing an implicit BEGIN for you, just
before any command that is not already in a transaction block and is not
itself a BEGIN or other transaction-control command, nor a command that
cannot be executed inside a transaction block (such as VACUUM).

Note: In autocommit-off mode, you must explicitly abandon any
failed transaction by entering ABORT or ROLLBACK. Also keep in mind that
if you exit the session without committing, your work will be lost.

Note: The autocommit-on mode is PostgreSQL's traditional
behavior, but autocommit-off is closer to the SQL spec. If you prefer
autocommit-off, you might wish to set it in the system-wide psqlrc file
or your ~/.psqlrc file.

"

Thank you.

--
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

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/08/2016 04:47 PM, Igor Korot wrote:

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"?

Any idea why I can't connect?

Because you are trying a local socket connection and psql cannot find
the socket. Not sure where the Apple install would create it, so cannot
help there.

I think "/var/pgsql_socket" *is* the Apple-blessed location; at least,
"strings" finds that string in /usr/lib/libpq.5.6.dylib on my Mac.

So I guess Igor is using Apple's copy of psql, or anyway a psql that is
linked to Apple's build of libpq, but the server he wishes to talk to is
using some other directory to keep the socket file in. The out-of-the-box
default directory is /tmp, but if this server was built by someone else
they might have changed that. Look around for a socket file named
".s.PGSQL.5432".

FYI, not having the -h tells psql to connect using a socket.

Also, you can use -h /path/to/socket/dir to specify connecting
using a socket file in a specific directory.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi, guys,
I'm working thru my script and I hit a following issue:

In the script I have a following command:

CREATE TABLE playersinleague(id integer, playerid integer, ishitter
char, age integer, value decimal, currvalue decimal, draft boolean,
isnew char(1), current_rank integer, original_rank integer, deleted
integer, teamid integer, notes varchar(125), PRIMARY KEY(id,playerid),
foreign key(id) references leagues(id), foreign key(playerid)
references players(playerid),foreign key(teamid) references
teams(teamid));

Now this command finished successfully, however trying to insert a
record with following command:

INSERT INTO playersinleague VALUES(1,1,'1',27,42.0,42.0,0,'0',1,1,0,23,NULL);

gives following error:

psql:/Users/igorkorot/draft.schema:10578: ERROR: column "draft" is of
type boolean but expression is of type integer

Looking at https://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

Any idea what is the problem?

Thank you.

P.S.: Sorry for the top-post.

On Thu, Dec 8, 2016 at 10:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/08/2016 04:47 PM, Igor Korot wrote:

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"?

Any idea why I can't connect?

Because you are trying a local socket connection and psql cannot find
the socket. Not sure where the Apple install would create it, so cannot
help there.

I think "/var/pgsql_socket" *is* the Apple-blessed location; at least,
"strings" finds that string in /usr/lib/libpq.5.6.dylib on my Mac.

So I guess Igor is using Apple's copy of psql, or anyway a psql that is
linked to Apple's build of libpq, but the server he wishes to talk to is
using some other directory to keep the socket file in. The out-of-the-box
default directory is /tmp, but if this server was built by someone else
they might have changed that. Look around for a socket file named
".s.PGSQL.5432".

FYI, not having the -h tells psql to connect using a socket.

Also, you can use -h /path/to/socket/dir to specify connecting
using a socket file in a specific directory.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-12-10 20:32 GMT+01:00 Igor Korot <ikorot01@gmail.com>:

Hi, guys,
I'm working thru my script and I hit a following issue:

In the script I have a following command:

CREATE TABLE playersinleague(id integer, playerid integer, ishitter
char, age integer, value decimal, currvalue decimal, draft boolean,
isnew char(1), current_rank integer, original_rank integer, deleted
integer, teamid integer, notes varchar(125), PRIMARY KEY(id,playerid),
foreign key(id) references leagues(id), foreign key(playerid)
references players(playerid),foreign key(teamid) references
teams(teamid));

Now this command finished successfully, however trying to insert a
record with following command:

INSERT INTO playersinleague VALUES(1,1,'1',27,42.0,42.0,0,
'0',1,1,0,23,NULL);

gives following error:

psql:/Users/igorkorot/draft.schema:10578: ERROR: column "draft" is of
type boolean but expression is of type integer

Looking at https://www.postgresql.org/docs/9.5/static/datatype-
numeric.html,
I don't see a 'boolean' as supported data type.

Any idea what is the problem?

you need explicit casting

postgres=# create table foo1(a boolean);
CREATE TABLE
Time: 191,257 ms
postgres=# insert into foo1 values(1);
ERROR: column "a" is of type boolean but expression is of type integer
LINE 1: insert into foo1 values(1);
^
HINT: You will need to rewrite or cast the expression.
Time: 56,549 ms
postgres=# insert into foo1 values(1::boolean);
INSERT 0 1

Regards

Pavel

On Dec 10, 2016, at 11:32 AM, Igor Korot <ikorot01@gmail.com> wrote:

Hi, guys,
I'm working thru my script and I hit a following issue:

In the script I have a following command:

CREATE TABLE playersinleague(id integer, playerid integer, ishitter
char, age integer, value decimal, currvalue decimal, draft boolean,
isnew char(1), current_rank integer, original_rank integer, deleted
integer, teamid integer, notes varchar(125), PRIMARY KEY(id,playerid),
foreign key(id) references leagues(id), foreign key(playerid)
references players(playerid),foreign key(teamid) references
teams(teamid));

Now this command finished successfully, however trying to insert a
record with following command:

INSERT INTO playersinleague VALUES(1,1,'1',27,42.0,42.0,0,'0',1,1,0,23,NULL);

gives following error:

psql:/Users/igorkorot/draft.schema:10578: ERROR: column "draft" is of
type boolean but expression is of type integer

Looking at https://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

Booleans aren't numeric.

https://www.postgresql.org/docs/9.5/static/datatype-boolean.html

Boolean will take a range of formats, including '0' - an untyped literal
"0". But it won't take an integer, which is what an unquoted 0 is.

You'll need to modify your insert statement slightly to use a valid boolean
value for that field ("true" or "false" are idiomatic).

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-12-10 20:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-12-10 20:32 GMT+01:00 Igor Korot <ikorot01@gmail.com>:

Hi, guys,
I'm working thru my script and I hit a following issue:

In the script I have a following command:

CREATE TABLE playersinleague(id integer, playerid integer, ishitter
char, age integer, value decimal, currvalue decimal, draft boolean,
isnew char(1), current_rank integer, original_rank integer, deleted
integer, teamid integer, notes varchar(125), PRIMARY KEY(id,playerid),
foreign key(id) references leagues(id), foreign key(playerid)
references players(playerid),foreign key(teamid) references
teams(teamid));

Now this command finished successfully, however trying to insert a
record with following command:

INSERT INTO playersinleague VALUES(1,1,'1',27,42.0,42.0,0,
'0',1,1,0,23,NULL);

gives following error:

psql:/Users/igorkorot/draft.schema:10578: ERROR: column "draft" is of
type boolean but expression is of type integer

Looking at https://www.postgresql.org/docs/9.5/static/datatype-numeric.
html,
I don't see a 'boolean' as supported data type.

Any idea what is the problem?

you need explicit casting

postgres=# create table foo1(a boolean);
CREATE TABLE
Time: 191,257 ms
postgres=# insert into foo1 values(1);
ERROR: column "a" is of type boolean but expression is of type integer
LINE 1: insert into foo1 values(1);
^
HINT: You will need to rewrite or cast the expression.
Time: 56,549 ms
postgres=# insert into foo1 values(1::boolean);
INSERT 0 1

if you can, fix import. If you cannot, you have to fix CAST rule.
Unfortunately, there are not possibility to alter cast rules cleanly - one
ugly workaround is necessary

Attention - direct update of system tables is bad, and don't do it.

SELECT oid FROM pg_cast WHERE castsource = 'integer'::regtype AND
casttarget='boolean'::regtype;

as super user run

update pg_cast set castcontext = 'a' where id = 11276; -- oid is a result
of previous query

Then conversion is automatic.

Regards

Pavel

On 12/10/2016 11:32 AM, Igor Korot wrote:

Looking athttps://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

thats because boolean isn't a number, its a true/false value.

https://www.postgresql.org/docs/current/static/datatype-boolean.html

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

On Sat, Dec 10, 2016 at 2:50 PM, John R Pierce <pierce@hogranch.com> wrote:

On 12/10/2016 11:32 AM, Igor Korot wrote:

Looking
athttps://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

thats because boolean isn't a number, its a true/false value.

https://www.postgresql.org/docs/current/static/datatype-boolean.html

OK, I changed all 0s to "FALSE".
The script did run but then failed silently (without error). No commit
were executed.

Is there any way to find which statement failed?

I can attach the script for you guys to review - I just don't know if
this ML OKs the attachment.

Thank you for a suggestion.

--
john r pierce, recycling bits in santa cruz

--
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

On 12/10/2016 06:56 PM, Igor Korot wrote:

Hi,

On Sat, Dec 10, 2016 at 2:50 PM, John R Pierce <pierce@hogranch.com> wrote:

On 12/10/2016 11:32 AM, Igor Korot wrote:

Looking
athttps://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

thats because boolean isn't a number, its a true/false value.

https://www.postgresql.org/docs/current/static/datatype-boolean.html

OK, I changed all 0s to "FALSE".
The script did run but then failed silently (without error). No commit
were executed.

Sounds like you did:

BEGIN;

Your commands

and did not do a COMMIT; before exiting the session.

Is there any way to find which statement failed?

I can attach the script for you guys to review - I just don't know if
this ML OKs the attachment.

Thank you for a suggestion.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
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

Thank you Adrian.
That was it.

Now I can continue testing.

On Sat, Dec 10, 2016 at 11:26 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 12/10/2016 06:56 PM, Igor Korot wrote:

Hi,

On Sat, Dec 10, 2016 at 2:50 PM, John R Pierce <pierce@hogranch.com>
wrote:

On 12/10/2016 11:32 AM, Igor Korot wrote:

Looking
athttps://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

thats because boolean isn't a number, its a true/false value.

https://www.postgresql.org/docs/current/static/datatype-boolean.html

OK, I changed all 0s to "FALSE".
The script did run but then failed silently (without error). No commit
were executed.

Sounds like you did:

BEGIN;

Your commands

and did not do a COMMIT; before exiting the session.

Is there any way to find which statement failed?

I can attach the script for you guys to review - I just don't know if
this ML OKs the attachment.

Thank you for a suggestion.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
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