PostgreSQL/Data Type/Point

Create table with point data type

   <source lang="sql">

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

      </source>
   
  

Use Point data type in insert statement

   <source lang="sql">

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=#

      </source>
   
  

Using Point data type in a function

   <source lang="sql">

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=#

      </source>
   
  

Using point data type in insert statement

   <source lang="sql">

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=#

      </source>