problem with column type

[npltr62]

OGR_FDW =1.1 CREATE SERVER sandre FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource 'WFS:https://services.sandre.eaufrance.fr/geo/obs', format 'WFS', updateable 'false');

CREATE FOREIGN TABLE IF NOT EXISTS data.sa_obstecoul(
    fid bigint NULL,
    msgeometry geometry(Geometry,4326) NULL,
    gml_id character varying NULL COLLATE pg_catalog."default",
    cdobstecoul character varying NULL COLLATE pg_catalog."default",
    stobstecoul character varying NULL COLLATE pg_catalog."default",
    cdmodevalidobstecoul character varying NULL COLLATE pg_catalog."default",
    lbmodevalidobstecoul character varying NULL COLLATE pg_catalog."default",
    cdetouvrage character varying NULL COLLATE pg_catalog."default",
    lbetouvrage character varying NULL COLLATE pg_catalog."default",
    nomprincipalobstecoul character varying NULL COLLATE pg_catalog."default",
    nomsecondaireobstecoul character varying NULL COLLATE pg_catalog."default",
    cdtypeouvrage character varying NULL COLLATE pg_catalog."default",
    lbtypeouvrage character varying NULL COLLATE pg_catalog."default",
    coordxpointcarouvrage real NULL,
    coordypointcarouvrage real NULL,
    typecoordpointcarouvrage character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchpiscicole1 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchpiscicole1 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchpiscicole2 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchpiscicole2 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchpiscicole3 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchpiscicole3 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchpiscicole4 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchpiscicole4 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchpiscicole5 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchpiscicole5 character varying NULL COLLATE pg_catalog."default",
    cdtypeelmobseuil1 character varying NULL COLLATE pg_catalog."default",
    lbtypeelmobseuil1 character varying NULL COLLATE pg_catalog."default",
    cdtypeelmobseuil2 character varying NULL COLLATE pg_catalog."default",
    lbtypeelmobseuil2 character varying NULL COLLATE pg_catalog."default",
    cdtypeelmobseuil3 character varying NULL COLLATE pg_catalog."default",
    lbtypeelmobseuil3 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchnavig1 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchnavig1 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchnavig2 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchnavig2 character varying NULL COLLATE pg_catalog."default",
    cdtypedispfranchnavig3 character varying NULL COLLATE pg_catalog."default",
    lbtypedispfranchnavig3 character varying NULL COLLATE pg_catalog."default",
    cdusageobstecoul1 character varying NULL COLLATE pg_catalog."default",
    lbusageobstecoul1 character varying NULL COLLATE pg_catalog."default",
    cdusageobstecoul2 character varying NULL COLLATE pg_catalog."default",
    lbusageobstecoul2 character varying NULL COLLATE pg_catalog."default",
    cdusageobstecoul3 character varying NULL COLLATE pg_catalog."default",
    lbusageobstecoul3 character varying NULL COLLATE pg_catalog."default",
    cdusageobstecoul4 character varying NULL COLLATE pg_catalog."default",
    lbusageobstecoul4 character varying NULL COLLATE pg_catalog."default",
    hautmaxter character varying NULL COLLATE pg_catalog."default",
    hautchutetobstecoul character varying NULL COLLATE pg_catalog."default",
    cdhautchutclobstecoul character varying NULL COLLATE pg_catalog."default",
    lbhautchutclobstecoul character varying NULL COLLATE pg_catalog."default",
    datemajobstecoul character varying NULL COLLATE pg_catalog."default",
    datevalidobstecoul character varying NULL COLLATE pg_catalog."default",
    grenobstecoul character varying NULL COLLATE pg_catalog."default",
    ouvragelie character varying NULL COLLATE pg_catalog."default",
    idtronconhydrograelt character varying NULL COLLATE pg_catalog."default",
    nomentitehydrographique character varying NULL COLLATE pg_catalog."default",
    cdtronconhydrographique character varying NULL COLLATE pg_catalog."default",
    cdentitehydrographique character varying NULL COLLATE pg_catalog."default",
    cdzonehydro character varying NULL COLLATE pg_catalog."default",
    idtronconhydrobdtopo character varying NULL COLLATE pg_catalog."default",
    cddepartement character varying NULL COLLATE pg_catalog."default",
    lbdepartement character varying NULL COLLATE pg_catalog."default",
    cdcommune character varying NULL COLLATE pg_catalog."default",
    lbcommune character varying NULL COLLATE pg_catalog."default",
    numcircadminbassin character varying NULL COLLATE pg_catalog."default",
    nomcircadminbassin character varying NULL COLLATE pg_catalog."default",
    cdeumassedeau character varying NULL COLLATE pg_catalog."default",
    altipointcarouvrage real NULL,
    nomlimitehydrographique character varying NULL COLLATE pg_catalog."default",
    denmaxouvrage real NULL,
    pkobstecoul real NULL
)
    SERVER sandre
    OPTIONS (layer 'sa:ObstEcoul');

when i display the above foreign table i get this error "WARNING: GDAL AppDefined [1] Value '2B251' of field sa:ObstEcoul.CdCommune parsed incompletely to integer 2." What i forgot to set? open_options?

[pramsey]

Really hard to say. I'd want to first take ogr_fdw out of the equation entirely, and run a gdal_translate from the WFS service into a GPKG file, to see if GDAL alone can correctly do the transform. I got a variation on the error you got, but with a double parsing problem. It's like in the data reading step, GDAL is occasionally mis-matching the data records with the schema definitions, so strings get parsed as integers, or floats. Or the WFS server is mis-matching them, sending back the wrong data in the wrong fields.

[npltr62]

thank you for your reply. In order to resolve the above issue I'm trying to pass the WFS service through my server geoserver. But when i want to show table data with your extension, it return this error GDAL AppDefined [1] HTTP error code : 400 .

[pramsey]

WFS is a finicky protocol. Ensure you can do everything via GDAL commandline directly, and if that doesn't work, inspect things with curl. The extension is only as good as the underlying connection.