I have PostgreSQL tables with the following columns:

Table (1)

| Column        | Datatype                |
| ------------- |:-----------------------:|
| group         | TEXT                    |
| geom          | Geometry (Polygon)      |

Table (2)

| Column        | Datatype     |
| ------------- |:------------:|
| group         | TEXT         |
| lat           | NUMERIC      |
| long          | NUMERIC      |

I want to, for every row in table (2), create or update the geometry columns in the first table (1) using the lat and long where the group column names are matching. Apologies if something like this has been answered before but I could not find a good example. I've tried queries like below (I know its wrong) but I can't seem to figure out how to use the PostGIS functions to get the behavior I want...

UPDATE TABLE_1
SET geom = st_setsrid(
    st_makepolygon(
        st_makeline(
            geom, st_makepoint(longitude, latitude)
        )
    ),
4269)
from (
     select
        long as longitude,
        lat as latitude,
        group
      from TABLE_2
) as subquery
where group = TABLE_1.group;

From discussion in comments:

Preferably, the geom column should be a polygon (if there are 3 or more points), if only 2 then it should be a line, and lastly a point (if there is only one set of coordinates). Essentially I am trying to create my own shapefiles from some latitude and longitude data I have. Also I am using: WGS 84, EPSG 4326

I have PostgreSQL tables with the following columns:

Table (1)

| Column        | Datatype                |
| ------------- |:-----------------------:|
| group         | TEXT                    |
| geom          | Geometry (Polygon)      |

Table (2)

| Column        | Datatype     |
| ------------- |:------------:|
| group         | TEXT         |
| lat           | NUMERIC      |
| long          | NUMERIC      |

I want to, for every row in table (2), create or update the geometry columns in the first table (1) using the lat and long where the group column names are matching. Apologies if something like this has been answered before but I could not find a good example. I've tried queries like below (I know its wrong) but I can't seem to figure out how to use the PostGIS functions to get the behavior I want...

UPDATE TABLE_1
SET geom = st_setsrid(
    st_makepolygon(
        st_makeline(
            geom, st_makepoint(longitude, latitude)
        )
    ),
4269)
from (
     select
        long as longitude,
        lat as latitude,
        group
      from TABLE_2
) as subquery
where group = TABLE_1.group;

From discussion in comments:

Preferably, the geom column should be a polygon (if there are 4 or more points), if less than 4 it should just be a collection of points. Essentially I am trying to create my own geometries from some latitude and longitude data I have. Also I am using: WGS 84, EPSG 4326

I have postgres tables with the following columns:

Table (1)

| Column        | Datatype                |
| ------------- |:-----------------------:|
| group         | TEXT                    |
| geom          | Geometry (Polygon)      |

Table (2)

| Column        | Datatype     |
| ------------- |:------------:|
| group         | TEXT         |
| lat           | NUMERIC      |
| long          | NUMERIC      |

I want to, for every row in table (2), create or update the geometry columns in the first table (1) using the lat and long where the group column names are matching. Apologies if something like this has been answered before but I could not find a good example. I've tried queries like below (I know its wrong) but I can't seem to figure out how to use the PostGIS functions to get the behavior I want...

UPDATE TABLE_1
SET geom = st_setsrid(
    st_makepolygon(
        st_makeline(
            geom, st_makepoint(longitude, latitude)
        )
    ),
4269)
from (
     select
        long as longitude,
        lat as latitude,
        group
      from TABLE_2
) as subquery
where group = TABLE_1.group;

From discussion in comments:

Preferably, the geom column should be a polygon (if there are 3 or more points), if only 2 then it should be a line, and lastly a point (if there is only one set of coordinates). Essentially I am trying to create my own shapefiles from some latitude and longitude data I have. Also I am using: WGS 84, EPSG 4326

I have PostgreSQL tables with the following columns:

Table (1)

| Column        | Datatype                |
| ------------- |:-----------------------:|
| group         | TEXT                    |
| geom          | Geometry (Polygon)      |

Table (2)

| Column        | Datatype     |
| ------------- |:------------:|
| group         | TEXT         |
| lat           | NUMERIC      |
| long          | NUMERIC      |

I want to, for every row in table (2), create or update the geometry columns in the first table (1) using the lat and long where the group column names are matching. Apologies if something like this has been answered before but I could not find a good example. I've tried queries like below (I know its wrong) but I can't seem to figure out how to use the PostGIS functions to get the behavior I want...

UPDATE TABLE_1
SET geom = st_setsrid(
    st_makepolygon(
        st_makeline(
            geom, st_makepoint(longitude, latitude)
        )
    ),
4269)
from (
     select
        long as longitude,
        lat as latitude,
        group
      from TABLE_2
) as subquery
where group = TABLE_1.group;

From discussion in comments:

Preferably, the geom column should be a polygon (if there are 3 or more points), if only 2 then it should be a line, and lastly a point (if there is only one set of coordinates). Essentially I am trying to create my own shapefiles from some latitude and longitude data I have. Also I am using: WGS 84, EPSG 4326