.year Method on PostgreSQL driver don't work

[Wrzlbrm]

The '.map({ .id, .birth.year, })' will produce an error:

SQL : SELECT "bla".id as "data_1", STRFTIME('%Y', DATE("bla".birth)) as "data_2" FROM "bla" WHERE "bla".name like '%blu%' BIND: [] Unknown Error!!! Please, copy this backtrace and open an issue on https://github.com/FCO/Red/issues/new Driver: Failure Original error: DB::Pg::Error::FatalError.new(message => "function strftime(unknown, date) does not exist", message-detail => Str, message-hint => "No function matches the given name and argument types. You might need to add explicit type casts.", context => Str, type => "ERROR", type-localized => "ERROR", state => "42883", statement-position => "33", internal-position => Str, internal-query => Str, schema => Str, table => Str, column => Str, datatype => Str, constraint => Str, source-file => "parse_func.c", source-line => "629", source-function => "ParseFuncOrColumn")

Original error: function strftime(unknown, date) does not exist in method error-check at /home/xxxx/.raku/sources/9ECF9162BB652CECE7C4EFD616D11879DA0C14A1 (DB::Pg::Database) line 123 in method prepare at /home/xxxx/.raku/sources/9ECF9162BB652CECE7C4EFD616D11879DA0C14A1 (DB::Pg::Database) line 168 in method stt-exec at /home/xxxx/.raku/sources/D9B54C35560643F2820A95310C90B3AD6270FBAB (Red::Driver::Pg) line 191 in submethod TWEAK at /home/xxxx/.raku/sources/3B629ED93D3057656BF5884192D0920BB361A992 (Red::ResultSeq::Iterator) line 16 in method iterator at /home/xxxx/.raku/sources/32194E0D484D448592F6558AF2BBCE89D137A35C (Red::ResultSeq) line 104 in block at redtest.raku line 24

Actually thrown at: in block at /home/xxxx/.raku/sources/770FE738C10327A7E0F87BFF51670779A369DBC4 (Red::Statement) line 18 in any at /home/xxxx/.raku/sources/770FE738C10327A7E0F87BFF51670779A369DBC4 (Red::Statement) line 16 in method error-check at /home/xxxx/.raku/sources/9ECF9162BB652CECE7C4EFD616D11879DA0C14A1 (DB::Pg::Database) line 123 in method prepare at /home/xxxx/.raku/sources/9ECF9162BB652CECE7C4EFD616D11879DA0C14A1 (DB::Pg::Database) line 168 in method stt-exec at /home/xxxx/.raku/sources/D9B54C35560643F2820A95310C90B3AD6270FBAB (Red::Driver::Pg) line 191 in submethod TWEAK at /home/xxxx/.raku/sources/3B629ED93D3057656BF5884192D0920BB361A992 (Red::ResultSeq::Iterator) line 16 in method iterator at /home/xxxx/.raku/sources/32194E0D484D448592F6558AF2BBCE89D137A35C (Red::ResultSeq) line 104 in block at redtest.raku line 24

SQL should be: select id, EXTRACT(YEAR FROM birth) ....select id, name, EXTRACT(YEAR FROM birth) With SQLite driver all is working OK.

[FCO]

Thank you for opening the ticket. I'll work on that as soon as I arrive at home. Sorry for the delay.

[FCO]

@Wrzlbrm I've added the DateTime functions to Pg. Would you mind to test it and reopen this issue if you find some problem?

[Wrzlbrm]

Thank you for quick reaction. After some tests I found still some problems. First: if the table is created, Date fields added as varchar to database. DateTime fields as timestamp (OK). model Bla { has UInt $.id is serial; has Date $.d is column is rw; has DateTime $.dt is column is rw; has Int $.n is column is rw; } schema(Bla).drop.create;

SQL : CREATE TABLE "bla" ( id serial NOT NULL primary key, d varchar(255) NOT NULL , dt timestamp NOT NULL , n integer NOT NULL )

Second: on a query DateTime (timestamp) fields will maped correct. But on Date (varchar) the EXTRACT(YEAR FROM "bla".d ) won't work. Error: Original error: DB::Pg::Error::FatalError.new(message => "function pg_catalog.extract(unknown, character varying) does not exist", message-detail => Str, message-hint => "No function matches the given name and argument types. You might need to add explicit type casts.", context => Str, type => "ERROR", type-localized => "ERROR", state => "42883", statement-position => "33", internal-position => Str, internal-query => Str, schema => Str, table => Str, column => Str, datatype => Str, constraint => Str, source-file => "parse_func.c", source-line => "629", source-function => "ParseFuncOrColumn")

The EXTRACT(YEAR FROM "bla".d ) function work only on date or timestamp fields. select EXTRACT(YEAR FROM '2024-01-01'::date), EXTRACT(YEAR FROM '2024-01-04 11:51:24.660'::timestamp) --> 2024, 2024

[FCO]

Thanks, I'll take a look after day work.

[FCO]

@Wrzlbrm I've added Date to Pg. It's running the tests now. Please let me know you find something broken again.