Re: Importing SQLite database

2016-12-10 20:43 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2016-12-10 20:32 GMT+01:00 Igor Korot <ikorot01(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>