2016-12-10 20:43 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2016-12-10 20:32 GMT+01:00 Igor Korot <ikoro...@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 <t...@sss.pgh.pa.us> wrote: >> > Adrian Klaver <adrian.kla...@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 >> > >
