raster2pgsql: Strange error when creating a table

Question

When I try to load an ASC file into my psql setup, I get a strange error message. I have created the SQL input file via

raster2pgsql -s 4236 -I -C -M gis_data.asc -F public.lightning > lightning.sql

The parameters follow exactly this link here. When I try to load the data into pgsql with

psql -d database_name -f lightning.sql

I get the following error message:

BEGIN
psql:lightning.sql:2: NOTICE:  CREATE TABLE will create implicit sequence "lightning_rid_seq" for serial column "lightning.rid"
psql:lightning.sql:2: ERROR:  type "raster" does not exist
LINE 1: ...lic"."lightning" ("rid" serial PRIMARY KEY,"rast" raster,"fi...
                                                             ^
psql:lightning.sql:3: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:lightning.sql:4: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:lightning.sql:5: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:lightning.sql:6: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
psql:lightning.sql:8: ERROR:  relation "public.lightning" does not exist

I thought that the SQL input file would instruct pgsql to create the required tables, especially since the lightning.sql file starts with

BEGIN;
CREATE TABLE "public"."lightning" ("rid" serial PRIMARY KEY,"rast" raster,"filename"   
text); 

Do I need to create the tables manually? What is the required type raster? Where is the mistake I do to get the GIS data into the pgsql database?

PS: Interestingly, my lightning.sql file contains a strange bit sequence. Is that normal? I thought it would be a full ASCII file with SQL instructions?

BEGIN; CREATE TABLE "public"."lightning" ("rid" serial PRIMARY KEY,"rast" raster,"filename"
text);

INSERT INTO "public"."lightning" ("rast","filename") VALUES                            
('01000001001975ADBD4F55B53F1975ADBD4F55B5BF0000000000003BC0B4CA4C69FDFF514000000000000 ... ')
CREATE INDEX "lightning_rast_gist" ON "public"."lightning" USING gist                  
(st_convexhull("rast"));
ANALYZE "public"."lightning";
SELECT AddRasterConstraints('public','lightning','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,,
FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);
END;
VACUUM ANALYZE "public"."lightning";

Was maybe the conversion not OK?

Answer 1

From the PostGIS webpage :

-- enable raster support (for 3+)
CREATE EXTENSION postgis_raster;

Answer 2

When you create a Postgis template database via loading and executing the sql scripts: postgis.sql and spatial_ref_sys.sql, you create the basic postgis template. This adds the spatial functions, reference systems, geometry data types, but not the raster datatype. To add that, you have to load and execute another script: rtpostgis.sql which is located next to the other 2 files. On linux thats in /usr/share/postgresql/9.1/contrib/postgis-2.0.1/rtpostgis.sql