3

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity?

e.g. if I have a tables t1, t2 and a view definition

create view v as select coalesce(t1.c1, t2.c1) c1 from t1, t2 where X;

is there some way of giving v1.c1 a type affinity? I've tried casting it

select cast(coalesce(t1.c1, t2.c1) as INTEGER) c1

but that doesn't seem to work.

3
  • 2
    SQLite uses affinity when storing data into columns. Views do not store data. What is your desired effect of forcing an affinity onto a view's column?
    – CL.
    Nov 16, 2015 at 12:22
  • It's for database modelling and comparing schemas. It seems the closest thing to working out the type of the column
    – nik
    Nov 17, 2015 at 8:30
  • In SQLite, a view's columns do not have types. The values themselves have types, and there can be different types in the same column.
    – CL.
    Nov 17, 2015 at 8:40

1 Answer 1

0
CREATE VIEW v AS
SELECT CAST(COALESCE(CAST(t1.c1 AS INTEGER), CAST(t2.c1 AS INTEGER)) AS INTEGER) AS c1
FROM t1, t2 WHERE X;

n this case, both t1.c1 and t2.c1 are individually cast to INTEGER before being used in the COALESCE function. This ensures that the result of COALESCE will have INTEGER type affinity.

Please note that if the data in t1.c1 or t2.c1 cannot be cast to INTEGER, the COALESCE function will return NULL for that row. Therefore, you should make sure that the casting is appropriate for your data to avoid unexpected results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.