Why QGis cannot display a PostGIS raster column?

Question

I have squared and imported a raster (see for details on the file) in PostgreSQL/PostGIS using raster2pgpsql:

raster2pgsql -c -F -I -C -r -M -t 1000x1000  contours.dem  public.tiles | psql -d postgres -U jlandercy

Processing 1/1: contours.dem
BEGIN
CREATE TABLE
INSERT 0 1
[...]
INSERT 0 1
CREATE INDEX
ANALYZE
NOTICE:  Adding SRID constraint
NOTICE:  Adding scale-X constraint
NOTICE:  Adding scale-Y constraint
NOTICE:  Adding blocksize-X constraint
NOTICE:  Adding blocksize-Y constraint
NOTICE:  Adding alignment constraint
NOTICE:  Adding coverage tile constraint required for regular blocking
NOTICE:  Adding spatially unique constraint required for regular blocking
NOTICE:  Adding number of bands constraint
NOTICE:  Adding pixel type constraint
NOTICE:  Adding nodata value constraint
NOTICE:  Adding out-of-database constraint
NOTICE:  Adding maximum extent constraint
----------------------
 t
(1 row)

COMMIT
VACUUM

There is no complaint during the insert, but I cannot see the layer in explorer.

Instead in DB Manager I can see the layer, but it makes QGis crash when I try to display it.

I have unique identifier rid, all 12 constraints have been built, the raster coordinate system is set. DB Manager is able to fetch all metadata:

But I cannot succeed in displaying the raster. Each time I try to add to canvas, QGis hangs (normal) and finally crashes (unexpected) without error message.

Is there a problem with my import or data storage? Or must I investigate towards PostgreSQL configuration? (Each of the 529 rows weights about 4Mo).

I cannot also import just one tile using filter query in DBManager:

Load button makes some work, but there is no layer created.

Anyway, if I import the file directly, I can see the raster without any problem.

I have the same problem if I simplify the raster and insert it in one piece.

What am I missing to make it work?

Additional information

\d tiles
                           Table "public.tiles"
  Column  |  Type   |                      Modifiers
----------+---------+-----------------------------------------------------
 rid      | integer | not null default nextval('tiles_rid_seq'::regclass)
 rast     | raster  |
 filename | text    |
Indexes:
    "tiles_pkey" PRIMARY KEY, btree (rid)
    "enforce_spatially_unique_tiles_rast" EXCLUDE USING btree ((rast::geometry) WITH =)
    "tiles_st_convexhull_idx" gist (st_convexhull(rast))
Check constraints:
    "enforce_coverage_tile_rast" CHECK (st_iscoveragetile(rast, '0100000000000000000000F03F000000000000F0BF3BD576D4A6DB0041595214C6B71F0641000000000000000000000000000000008A7A00001A59BE58'::raster, 1000, 1000))
    "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[1000, 718]))
    "enforce_max_extent_rast" CHECK (st_envelope(rast) @ '01030000208A7A000001000000050000003BD576D4A6DB0041595214C6C75903413BD576D4A6DB0041595214C6B71F06413BD576D476A40341595214C6B71F06413BD576D476A40341595214C6C75903413BD576D4A6DB0041595214C6C7590341'::geometry)
    "enforce_nodata_values_rast" CHECK (_raster_constraint_nodata_values(rast) = '{-340282306073710000000000000000000000000.0000000000}'::numeric[])
    "enforce_num_bands_rast" CHECK (st_numbands(rast) = 1)
    "enforce_out_db_rast" CHECK (_raster_constraint_out_db(rast) = '{f}'::boolean[])
    "enforce_pixel_types_rast" CHECK (_raster_constraint_pixel_types(rast) = '{32BF}'::text[])
    "enforce_same_alignment_rast" CHECK (st_samealignment(rast, '0100000000000000000000F03F000000000000F0BF3BD576D4A6DB0041595214C6B71F0641000000000000000000000000000000008A7A000001000100'::raster))
    "enforce_scalex_rast" CHECK (round(st_scalex(rast)::numeric, 10) = round(1::numeric, 10))
    "enforce_scaley_rast" CHECK (round(st_scaley(rast)::numeric, 10) = round(- 1::numeric, 10))
    "enforce_srid_rast" CHECK (st_srid(rast) = 31370)
    "enforce_width_rast" CHECK (st_width(rast) = ANY (ARRAY[1000, 810]))

View raster_columns contains:

airproject=# SELECT * FROM raster_columns
;
-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
r_table_catalog  | airproject
r_table_schema   | public
r_table_name     | tiles
r_raster_column  | rast
srid             | 31370
scale_x          | 1
scale_y          | -1
blocksize_x      | 1000
blocksize_y      | 1000
same_alignment   | t
regular_blocking | f
num_bands        | 1
pixel_types      | {32BF}
nodata_values    | {-3.4028230607371e+38}
out_db           | {f}
extent           | 01030000208A7A000001000000050000003BD576D4A6DB0041595214C6C75903413BD576D4A6DB0041595214C6B71F06413BD576D476A40341595214C6B71F06413BD576D476A40341595214C6C75903413BD576D4A6DB0041595214C6C7590341
spatial_index    | t

View raster_overviews is empty.

Answer 1

It seems QGis has a different mechanism for raster management. I am now using v3.4 LTS (w/ Postgres 10.1 and Postgis 2.4), and the issue is still present. I can say it is at the QGis side the issue resides.

It looks like raster import mechanism lacks somehow connection string to database. This is why rasters are not shown in PostgreSQL sources and we have difficulties to add this kind of layers to canvas.

Solution provided by @Marvin works for QGis files, but it is a bit tricky and if you aim to store your project into PostgreSQL you will have to modify the ByteA field which is definitly not a good option.

But wait, there is a hack to circonvolve this issue:

• Go to Database Manager, then raster fields are displayed as expected;
• Do not drag and drop the layer, instead right click on raster and chose Add to canvas;
• Then, you will be prompted for credentials;
• Provide it again and the raster field will be added to canvas.

Tested with DEM and Orthophoto, it is just about remind QGis for connection credentials.

Answer 2

Try the following:

1. Create a *.qgs file with local (grid-)data
2. Open the *.qgs file with a texteritor
3. Go to the line that begins with "<datasource>"
4. Change the line to: "<datasource>PG: dbname=[DBNAME] host=[HOST] user=[USER] password=[PW] port=5432 mode=2 schema=public column=rast table=tiles where='rid = 1'</datasource>" --> replace the square brackets!
5. save the file
6. open it with QGIS

I hope it works