Here are example commands for installing the PostGIS extension into a gc
schema:
CREATE SCHEMA gc;
CREATE EXTENSION postgis SCHEMA gc;
Here are example commands to move PostGIS and its objects to a new schema for version 2.4.4 of PostGIS in case you've already installed it:
CREATE SCHEMA gc;
UPDATE pg_extension
SET extrelocatable = TRUE
WHERE extname = 'postgis';
ALTER EXTENSION postgis
SET SCHEMA gc;
ALTER EXTENSION postgis
UPDATE TO "2.4.4next";
ALTER EXTENSION postgis
UPDATE TO "2.4.4";
This isn't strictly necessary; just helpful:
ALTER DATABASE whatever
SET search_path = public,gc;
When you see an error like:
st_setsrid(unknown, integer)
it doesn't mean the ST_SetSRID does not exist, it means that there is a problem with the argument type unknown.
So, you can fix this by casting the HexEWKB directly to a geometry, using the cast operator, ::, and then call ST_SetSRID on that:
INSERT INTO tout.battest(geom) VALUES
(ST_SetSRID('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941'
::geometry, 2154));
This works because the HexWBK is already a geometry representation, but in a string format, hence the need for a cast (see this Stack Overflow answer explaining the :: operator as a shortcut for cast). This also means that you do not need any of the ST_From...WKB type operators.
If you want to see the geometry directly, just use ST_AsText, again demonstrating that it is already a direct representation of a geometry.
SELECT ST_AsText('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941');
which returns:
MULTIPOLYGON(((924133.579996152 6564295.65010458,924134.009996152 6564298.11010458,924134.699996152 6564297.99010458,924134.269996152 6564295.53010458,924133.579996152 6564295.65010458)))
EDIT: It is worth mentioning that as you mention CSV in the title, if you have lots of rows to insert, it might be quicker to run the COPY command on the CSV file to load the data into a geometry (mulipolygon) column with no SRID set and then use:
ALTER TABLE 'battest' ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 2154) USING ST_SetSRID(geom, 2154)
where you need the using clause to prevent unknown SRID errors in the alter table part.
Best Answer
When you spatially-enable a PostGIS database, the relevant functions, SRS table, and views are placed in the public schema, as you state. That does not mean that all or any of your own spatial tables need to be in the same public schema. PostGIS will still work on all spatial data in the "new" schemas.
In fact, I usually place my application-specific tables in a separate schema. That way, if you need to do a major version upgrade to PostGIS, you can keep your application-specific table backups and restores as a separate procedure from the one that replaces the spatial tools.
So, I think you're doing well. Finally, in case you didn't do it already, it's a good idea to add the new schema to the search path:
ALTER DATABASE my_db SET search_path = gc, public;