How to find the latitude value and save it in another table in postgresql

Question

I have a shp file, I want to get the latitude value and store it in an another table.

CREATE TABLE Polepoints ( X varchar(50),Y varchar(50),Z  varchar(50));

Insert action :

INSERT INTO Polepoints (X) Select ST_X (ST_Centroid(geos)) from mytable

It throws out error

column X doesn't exist

but the column and the table exists. I am a beginner.

Answer 1

The problem has to be somewhere else. If the table Polepoints really has a column named X, this error shouldn't appear (see example below). Check if you're connected to the right database or even if the real table polepoints (the one containing the column X) is in a different schema.

CREATE TABLE Polepoints ( X varchar(50),Y varchar(50),Z  varchar(50));

WITH mytable (geos) AS (
  VALUES ('POINT(1 1)'),('POINT(2 3)')
)
INSERT INTO Polepoints (X) SELECT ST_X (ST_Centroid(geos)) FROM mytable;

SELECT * FROM polepoints;

 x | y | z 
---+---+---
 1 |   | 
 2 |   |