QGIS default style stored in PostGIS public.layer_styles table won't load with data from other schemas

Question

In QGIS 2.18.15 (and 3.0), I cannot get default styles stored in 'public.layer_styles' in a PostGIS database to load automatically when adding a new layer to the map. I believe the problem is that the data layers are stored in different schema from 'public'.

The following approaches provide the desired result of automatically loading the default 'public.layer_styles' when a layer is added from the database:

1) 'public.layer_styles' column 'f_table_catalog' contains the name of the database, column 'f_table_schema' contains the name 'public' --> this only works if the data layers are ALSO stored in the 'public' schema.

2) 'public.layer_styles' column 'f_table_catalog' contains the name of the database, column 'f_table_schema' contains the name 'another_schema' (in this case 'dnk') --> this only works if the data layers are ALSO stored in the 'another_schema' schema (here, 'dnk').

Option 2 above gets me most of the way to where I want to be. The only problem is that it limits me to only being able to load data with default styles from one additional named schema ('dnk' in this case). I have several other schemas in the same database from which I'd like to add data layers and have them styled automatically based on 'public.layer_styles'. I want to be able to load data layers from any named schema outside of 'public' and have the default layer_styles stored in 'public' be applied.

Is there some kind of wildcard character that can go in the 'f_table_schema' column to enable this (instead of 'public' or 'another_schema')? Is there another way to do this that I'm missing?

Answer 1

Well, you are of course NOT limited to one specific schema (Why should this be exactly dnk?) besides public and QGIS loads default styles for whatever schema your tables are stored in (but first of all I strongly recommend to the latest QGIS Versions 2.18.22 LTR and/or 3.2 by Aug. 6th, 2018).

I have a layer plz_by_31468 stored in a schema plz (without loss of generality), geometry column is geom (Postal code regions in Bavaria, Germany). If e.g. in QGIS 3.2 under Layer|Properties|Style|Style|Save Style|Save in database (postgres)

you save the style with e.g. the following settings:

you create the following entry in your public.layer_styles table:

and this style definitely loads per default (at least in the two latest versions mentioned above that I have access to).

If you have created the public.layer_styles table manually, make sure that the tables schema is in the f_table_name, that you have no misspellings in the f_table_name column and that the useasdefault column is set to some boolean True (t, 1, ... should work also, cp. https://www.postgresql.org/docs/10/static/datatype-boolean.html for valid representations of boolean type in PostgreSQL) only for the style you want to load as default and False for any other.