epgsql - Erlang PostgreSQL Client

# Using timescaledb with epgsql

Create a new table.

-- create a new table
CREATE TABLE h ( 
  time TIMESTAMPZ NOT NULL, 
  data TEXT NOT NULL 
);

Create an hypertable (opens new window).

SELECT create_hypertable('h', by_range('time'));

Create a new index.

CREATE INDEX index_data_time ON h (data, time DESC);

Here a function to insert data.

insert(Connection, Data) ->
    Template = "INSERT INTO h (time, data) VALUES (now(), $1)",
    epgsql:equery(Conn, Template, [Data]).

The same as lambda function.

Insert = fun (Conn, Data) ->
  Template = "INSERT INTO h (time, data) VALUES (now(), $1)",
  epgsql:equery(Conn, Template, [Data])
end.

Insert a new data into the table.

{ok, Conn} = epgsql:connect(#{ 
    host => "localhost", 
    username => <<"sandbox">>, 
    password => <<"sandbox">>, 
    database => <<"sandbox">>
}).

Insert(Conn, <<"test">>).

# FAQ

# How to create a connection?

{ok, C} = epgsql:connect(#{ 
  host => "localhost", 
  username => <<"sandbox">>, 
  password => <<"sandbox">>, 
  database => <<"sandbox">>
}).

# How to execute a simple query?

{ok, [], []}
 = epgsql:squery(C, "CREATE TABLE t (id integer);").

# How to use select with simple query?

Simple queries are executed with squery/2 or squery/3 functions.

{ok,[{column,<<"id">>,int4,23,4,-1,0,18149,1}],[]}
  = epgsql:squery(C, "SELECT * FROM t;").

# How to insert data with extended query?

Extended queryes are executed with equery/2 or equery/3 functions.

{ok, 1}
  = epgsql:equery(C, "INSERT INTO t (id) VALUES ($1);", [1]).

Where $1 is a reference to the first element of the list provided as third argument.

# How to prepare a statement?

{ok, Statement} = epgsql:parse(C, "insert integer", "INSERT INTO t (id) VALUES ($1)" [])

# How to prepare a query?

{ok, 1} = epgsql:prepared_query(C, Statement, [3]).
{ok, 1} = epgsql:prepared_query(C, Statement, [4]).

# How to execute asynchronously a prepared query?

An asynchronous query can be executed using epgsqla module.

epsqla:bind(C, Statement, ["execution name"], [1]).

# How to execute synchronously a prepared query?

An synchronous query can be executed using epgsql module.

epgsql:execute(C, Statement, ["execution name"], []).

# How to execute incrementaly a prepared query?

An incremental query can be executed using epgsqli module.

epsqli:execute(C, Statement, ["execution name"], [1]).

# How to use a transaction with a simple queries?

One or many simple queries can be added in a transaction.

epgsql:with_transaction(C, fun(CC) ->
  epgsql:squery(CC, "insert integer", "INSERT INTO t (id) VALUES (1024);")
end).

# How to use a transaction with extended queries?

One or many extended queries can be added in a transaction.

epgsql:with_transaction(C, fun(CC) ->
    epgsql:equery(CC, "insert integer", "INSERT INTO t (id) VALUES ($1)", [12]),
    epgsql:equery(CC, "insert integer", "INSERT INTO t (id) VALUES ($1)", [23]),
    epgsql:equery(CC, "insert integer", "INSERT INTO t (id) VALUES ($1)", [34])
end).

# What happens when a transaction fail?

When a query fails into a transaction, a rollback is made by default.

{rollback,error}
 = epgsql:with_transaction(C, fun(CC) ->
     epgsql:equery(CC, "insert integer", "INSERT INTO t (id) VALUES ($1)", [212]),
     epgsql:equery(CC, "insert integer", "INSERT INTO t (id) VALUES ($1)", [323]),
     epgsql:equery(CC, "insert integer", "INSERT INTO t (id) VALUES ($1)", [434]),
     throw(error)
   end).