Casting To A Column's Type

Procedural SQL (PSQL) StatementsAggregate Functions

Casting to a Column’s Type

It is also possible to cast expressions to the type of an existing table or view column.Only the type itself is used;in the case of string types, this includes the character set but not the collation.Constraints and default values of the source column are not applied.

create table ttt (
  s varchar(40) character set utf8 collate unicode_ci_ai
);
commit;

select cast ('Jag har många vänner' as type of column ttt.s)
from rdb$database;

Warning

Warnings

If a column’s definition is altered, existing CASTs to that column’s type may become invalid.If these CASTs occur in PSQL modules, their invalidation may be detected.See the note The RDB$VALID_BLR field, in Appendix A.

Cast Examples

A full-syntax cast:

select cast ('12' || '-June-' || '1959' as date) from rdb$database

A shorthand string-to-date cast:

update People set AgeCat = 'Old'
  where BirthDate < date '1-Jan-1943'

Notice that you can drop even the shorthand cast from the example above, as the engine will understand from the context (comparison to a DATE field) how to interpret the string:

update People set AgeCat = 'Old'
  where BirthDate < '1-Jan-1943'

However, this is not always possible.The cast below cannot be dropped, otherwise the engine would find itself with an integer to be subtracted from a string:

select cast('today' as date) - 7 from rdb$database