Thread: Importing SQLite database
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.
OmniDB [1] 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] can help you on how to add connections to OmniDB and this other page [3] explains how to use the convert feature.
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
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
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
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
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
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 > >> > > > > 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
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 >
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
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
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
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
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
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
> 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
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
update pg_cast set castcontext = 'a' where id = 11276; -- oid is a result of previous query
Then conversion is automatic.
Regards
Pavel
RegardsPavelThank 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
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
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
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
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