Re: [GENERAL] Importing SQLite database - Mailing list pgsql-general

From Pavel Stehule
Subject Re: [GENERAL] Importing SQLite database
Date
Msg-id CAFj8pRDQvxaY=F44aMN3J6tPzFVVL8Qvdmum2y1LnebTGWYztw@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Importing SQLite database  (Pavel Stehule <pavel.stehule@gmail.com>)
List 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


 
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


pgsql-general by date:

Previous
From: Steve Atkins
Date:
Subject: Re: [GENERAL] Importing SQLite database
Next
From: John R Pierce
Date:
Subject: Re: [GENERAL] Importing SQLite database