In Postgres how to create columns with geometry?

Question

I have point data stored in the postgis server and I need to create a new table with their geometry values.

How can I extract their coordinates and translate them into a table. Something like:

column1 = coordinate X | column2= coordinate Y

I need this to more easily export the point data into QGIS.

• ziggy: Im slightly confused-do you have a table with the point geometry already? if yes then are you simply trying to bring that into QGIS?
• Inactivated Account: Do you mean you have coordinates stored in a PostgreSQL table and you want to convert them to PostGIS geometry?
• Luffydude: I mean I already have a table A with some point geometry, I need to create a new table B with table A's geometry column separated in X and Y
• Get Spatial: If you have point data in PostGIS, bringing it in to QGIS should be simple. Before giving more detail about possible options to convert your data, please give more detail. Specifically, lay out your table and column structure anyhow the point data, i.e., x and y coords are dried in your Postgres table. Also, as mentioned on other questions, review the docs for Postgres,PostGIS. What you are asking is basic functionality that you should at least attempt on your own first.
• Lee Hachadoorian: This question needs a lot more detail, and from you comment, the question itself is misleading. You already have a table with a geometry column, and you want a table with the coordinates. You should address: Same table or different table? Do you just need the coordinates, or does it actually have to be a PostGIS table? Could it be a view? The reference to QGIS is particularly confusing because QGIS can already easily display a PostGIS spatial layer, so what do you mean by "more easily export the point data into QGIS"?

Answer 1

I was waiting that the moderators will unhold your question here after the edit but still nothing so here is a possible answer, check the comments I left there for adding the count of overlapping points:

Here to add the columns of X and Y :

alter table your_Table add column x double precision; 
alter table your_Table add column y double precision;
update your_Table set x=st_x(your_GeometryColumn), y=st_y(your_GeometryColumn);

• thibautg: To update a field, you can just write UPDATE your_table SET x = ST_X(geom)