I have two tables. One with points and another is with polygons. How could I find which point inside of which polygon?


You just misplaced the parameters in ST_CONTAINS(), first parameter will be polygon and second one will be point, since ST_CONTAINS(g1,g2) will return 1 if g1 completely contains g2 or will return 0.

SELECT messagespoints.coordinates FROM messagespoints 
WHERE T_CONTAINS( (SELECT geofencespolygon.coordinates 
FROM geofencespolygon),messagespoints.coordinates)

You can also join both tables using ST_Contains() in on clause as shown below:

SELECT messagespoints.coordinates 
FROM geofencespolygon
inner join messagespoints 
on ST_CONTAINS(geofencespolygon.coordinates,messagespoints.coordinates)


 Schema and insert statements:

 create table testPoly(polID int, pol polygon);

 create table testPoint(pntID int, pnt point);
 insert into testPoint values(1,ST_GeomFromText('POINT(2 1)')); 
 insert into testPoint values(2,ST_GeomFromText('POINT(5 3)')); 
 insert into testPoint values(3,ST_GeomFromText('POINT(5 10)')); 

 INSERT INTO testPoly VALUES (1,ST_GeomFromText('POLYGON((0 0, 8 0, 4 4, 0 0))'));


 select polID,pntID
 FROM testPoly 
 inner join testPoint
 on ST_Contains(pol, pnt)


polID pntID
1 1

db<>fiddle here