Re: [racket-users] Note: DB layer does not handle Pg enums

[David Storrs]

Oh, cool.  I didn't know that.  Thanks, Jay.

On Mon, Oct 16, 2017 at 6:09 PM, Jay Kominek  wrote:
> On Mon, Oct 16, 2017 at 12:04 PM, David Storrs 
> wrote:
>>
>> Not a critical issue, but the db package will not handle enums in
>> Postgres.  This is clear from the docs but I just now tripped over it
>> and thought I'd raise awareness.
>>
>> Workaround:  make the field of type TEXT and then add a table constraint:
>> CONSTRAINT "files_current_state_cns" CHECK (current_state in
>> ('INFO','NEW','COPYING','OK')),
>
>
> You can also change your query, instead of the table, with something like:
>
> (query-exec db "insert into files (..., ($1::text)::file_state, ...)" "OK")
>
> that query now expects the client side to provide a text value for $1 (which
> the racket code is completely capable of); the server will handle the cast
> from text to file_state for you. Similarly you can get data in unsupported
> types from the database by adding a cast to text in your selects.
>
> --
> Jay Kominek

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [racket-users] Note: DB layer does not handle Pg enums

[Jay Kominek]

On Mon, Oct 16, 2017 at 12:04 PM, David Storrs 
wrote:

> Not a critical issue, but the db package will not handle enums in
> Postgres.  This is clear from the docs but I just now tripped over it
> and thought I'd raise awareness.
>
> Workaround:  make the field of type TEXT and then add a table constraint:
> CONSTRAINT "files_current_state_cns" CHECK (current_state in
> ('INFO','NEW','COPYING','OK')),
>

You can also change your query, instead of the table, with something like:

(query-exec db "insert into files (..., ($1::text)::file_state, ...)" "OK")

that query now expects the client side to provide a text value for $1
(which the racket code is completely capable of); the server will handle
the cast from text to file_state for you. Similarly you can get data in
unsupported types from the database by adding a cast to text in your
selects.

-- 
Jay Kominek

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[racket-users] Note: DB layer does not handle Pg enums

[David Storrs]

Not a critical issue, but the db package will not handle enums in
Postgres.  This is clear from the docs but I just now tripped over it
and thought I'd raise awareness.

Workaround:  make the field of type TEXT and then add a table constraint:
CONSTRAINT "files_current_state_cns" CHECK (current_state in
('INFO','NEW','COPYING','OK')),


Relevant walkthrough:

OSX 10.11.6 (15G1611)
PostgreSQL 9.6

create type file_state as enum ('INFO','NEW','COPYING','OK');
create table files ... "current_state" FILE_STATE NOT NULL ...;


psql -d biomantica
biomantica=# insert into files (..., current_state,...) values (..., 'OK', ...))
INSERT 0 1  ; insert succeeded

; from racket
(define db (postgresql-connect ...))
(query-exec db "delete from files")
(query db "insert into files (..., current_state, ...) values (...,'OK',...))

Exception: 'query-exec: unsupported type  typeid: 131721'


All of the ellided column names and values match up and were straight
copy/paste from psql into the racket command, so I have to assume the
issue is the file_state enum.  As mentioned above, you can create an
enum-by-another-name using a text field and a constraint.

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.