7

In a previous question that I posted, the answer that I accepted involved creating a Virtual Layer from a source layer. The query used to create the Virtual Layer was:

select *  
from my_layer  
order by field_a

Although the query works as intended, some of the source field types are converted to different types in the Virtual Layer. Specifically:

  • Integer (32 bit) are converted to Integer (64 bit)
  • Date and Date&Time are converted to Text (string)
  • Boolean are converted to Integer (64 bit)

I use the Refactor Fields to get the field types back to their original, but this involves another step that I would prefer to eliminate.

Question: How to create a Virtual Layer that retains the source field types?

Stu Smith
  • 8,252
  • 8
  • 35
  • 84
  • Have you tried a cast (e.g. mydate::DATE) for those fields? – jpinilla Mar 19 '23 at 09:05
  • 3
    See what Virtual layer context help says: https://i.stack.imgur.com/QAz7E.png "where type can be any of int, real or text". So this seems to suggest that virtual layers do not support fieldtypes like Date&Time or Boolean, but I might be wrong. See also here: https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#special-comments – Babel Mar 19 '23 at 10:11
  • 2
    I think the problem lies with SQLITE, which knows only limited data types: https://www.sqlite.org/datatype3.html – christoph Mar 19 '23 at 10:50
  • 2
    You could experiment with the OGR Virtual Format, where you have more options to define your field types: https://gdal.org/drivers/vector/vrt.html. After loading your XML description (i.e. myfile.vrt) into QGIS you could replace the layer datasource with the content of your VRT file using the changeDataSource plugin. – christoph Mar 19 '23 at 11:06
  • The answer clearly involves the lack of field type support for Date, Time, and Boolean. @Babel was the first to point this out, so if you'd like to form your comment as an answer I'd be happy to credit. – Stu Smith Mar 25 '23 at 21:29

2 Answers2

5

Virtual layers lack field type support for Date, Time, and Boolean. See what Virtual layer context help says:

where type can be any of int, real or text

enter image description here

See also here: https://docs.qgis.org/latest/en/docs/user_manual/managing_data_source/create_layers.html#special-comments

Babel
  • 71,072
  • 14
  • 78
  • 208
1

I don't know a possibility to define the resulting field-type of a virtual layer, but as workaround You could create the virtual layer with a query that contains only the absolute necessary fields and add all other fields via join from the original layers.

That has multiple advantages:

  1. simple queries for the virtual layer
  2. attribute-tables and dialogs, which show clear, from where the fields come
  3. correct (and all) field-types from original layers

So create virtual_lyr with something like

select 
id_field
from my_layer  
order by field_a

And then join all other data with layer-properties-dialogue or via Python:

join_info = QgsVectorLayerJoinInfo()
join_info.setJoinLayer(my_layer)
join_info.setJoinFieldName(id_field)
join_info.setTargetFieldName(id_field)
join_info.setUsingMemoryCache(True)
virtual_lyr.addJoin(join_info)

One possible disadvantage:

The resulting layers with simple query and join could be less performant than the with complex queries without join, I haven't tested this so far.

hth Ludwig

ludwig
  • 168
  • 5