document how to insert "weird" data types, that are not just numbers or strings.

[zieone]

The documentation doesn't cover how to insert data types that are "weird", things like point() for example, where you have to insert point(lat,long).

It would be great if pgrest could handle it, but in the meantime you can do it with a trigger.
example below:

given a table like so:

CREATE TABLE public.contact_locations (
    id integer NOT NULL PRIMARY KEY,
    name text,
    contact_id integer,
	longitude float8,
	latitude float8,
	location point
);

create a function like so:

-- pgrest can't insert point()'s. so we use a trigger.
--
create or replace function public.contact_locations_addpoint()
returns trigger as $$
BEGIN
IF NEW.location is null THEN
   NEW.location := point(NEW.latitude,NEW.longitude);
END IF;
RETURN NEW;
END$$ LANGUAGE 'plpgsql';

and a trigger like so:

create trigger contact_locations_insertpoint
BEFORE INSERT OR UPDATE on contact_locations
FOR EACH ROW EXECUTE PROCEDURE contact_locations_addpoint();

then you can:

curl "http://localhost:3000/contact_locations" -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" -d '{"latitude":53.5775, "longitude":-6.611944, "contact_id":19,"name":"Hill Of Tara"}'

and it will add the point() for you:

curl "http://localhost:3000/contact_locations?id=eq.19"  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json"
[{"id":19,"name":"Hill Of Tara","contact_id":19,"latitude":53.5775,"longitude":-6.611944,"location":"(53.5775,-6.611944)","username":"zieone"}]

side note: inserting username is trivial with the insertusername function in PG: https://www.postgresql.org/docs/11/contrib-spi.html#id-1.11.7.45.8

I don't have time at the moment to tackle actually massaging this into documentation, if it should go in the documentation, hopefully someone can massage this into documentation form someday.

[steve-chavez]

Great suggestion for documenting a how-to.

Have you tried inserting location as a record literal? i.e. "location": "(53.5775, -6.611944)"?

It should work without the trigger. Example request:

curl "http://localhost:3000/contact_locations" -X POST -H "Content-Type: application/json" -d '{"id": 5, "latitude":53.5775, "longitude":-6.611944, "location": "(53.5775, -6.611944)", "contact_id":19,"name":"Hill Of Tara"}'

[zieone]

@steve-chavez

yes, I tried, it doesn't work. It also doesn't work that way from psql either:

# create table t ( p point );
CREATE TABLE
# insert into t VALUES ( (53.5775, -6.611944) );
ERROR:  column "p" is of type point but expression is of type record
LINE 1: insert into t VALUES ( (53.5775, -6.611944) );
                               ^
HINT:  You will need to rewrite or cast the expression.
# insert into t VALUES ( point(53.5775, -6.611944) );
INSERT 0 1

Perhaps it should work in both places, from postgrest and psql, but that's not the case today, in my experience.

[steve-chavez]

@zieone Hmm. The value should be a record literal, e.g. quoted '(53.5775, -6.611944)' like:

create table t ( p point );
-- CREATE TABLE
insert into t VALUES ( '(53.5775, -6.611944)' );
-- INSERT 0 1

Could you retry with quoting? Also share your PostgreSQL version, could be related to that. It works for me on pg 11.4.

[zieone]

AH! @steve-chavez you are correct, here it is in curl form, that works.

$ curl -w "%{http_code}\n" "http://localhost:3000/t" -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" -d '{"p":"(53.5775, -6.611944)"}'
201

So my above example is no longer directly useful, since "(lat,long)" will work, but there are other data types/etc where one does need to do this.

[steve-chavez]

@zieone What other types do you have in mind?

Record literal syntax should probably work for every composite type.

[steve-chavez]

Now documented in https://postgrest.org/en/stable/how-tos/working-with-postgresql-data-types.html