PostgreSQL/Data Type/Point

Create table with point data type

postgres=# CREATE TABLE cities (
postgres(#    name            varchar(80),
postgres(#    location        point
postgres(# );
CREATE TABLE
postgres=#
postgres=# drop table cities;
DROP TABLE
postgres=#
postgres=#

Use Point data type in insert statement

postgres=#
postgres=# CREATE TABLE cities (
postgres(#    name            varchar(80),
postgres(#    location        point
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO cities VALUES ("San Francisco", "(-194.0, 53.0)");
INSERT 0 1
postgres=#
postgres=# select * from cities;
     name      | location
---------------+-----------
 San Francisco | (-194,53)
(1 row)
postgres=#
postgres=# drop table cities;
DROP TABLE
postgres=#

Using Point data type in a function

postgres=#
postgres=# CREATE TABLE emp (
postgres(#    name        text,
postgres(#    salary      numeric,
postgres(#    age         integer,
postgres(#    cubicle     point
postgres(# );
CREATE TABLE
postgres=#
postgres=#
postgres=# CREATE FUNCTION new_emp() RETURNS emp AS $$
postgres$#    SELECT text "None" AS name,
postgres$#        1000.0 AS salary,
postgres$#        25 AS age,
postgres$#        point "(2,2)" AS cubicle;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# select new_emp();
         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")
(1 row)
postgres=#
postgres=# drop function new_emp();
DROP FUNCTION
postgres=# drop table emp;
DROP TABLE
postgres=#
postgres=#

Using point data type in insert statement

postgres=# CREATE TABLE emp (
postgres(#    name        text,
postgres(#    salary      numeric,
postgres(#    age         integer,
postgres(#    cubicle     point
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into emp (cubicle) values (point(0,0));
INSERT 0 1
postgres=# insert into emp (cubicle) values (point(2,1));
INSERT 0 1
postgres=#
postgres=#
postgres=# SELECT *
postgres-#    FROM emp
postgres-#    WHERE emp.cubicle ~= point "(2,1)";
 name | salary | age | cubicle
------+--------+-----+---------
      |        |     | (2,1)
(1 row)
postgres=#
postgres=#
postgres=# drop table emp;
DROP TABLE
postgres=#