Git Product home page Git Product logo

postgresql-temporal's Introduction

temporal

Temporal Extension for PostgreSQL

This module adds data types, functions, and indexing strategies useful for managing time data in PostgreSQL.

The PERIOD data type represents a range of time, e.g. '[2000-01-01, 2000-03-05)' represents the time from the beginning of January 2000 to the 5th of March.

To build it, just do this:

make
make install
make installcheck

If you encounter an error such as:

"Makefile", line 8: Need an operator

You need to use GNU make, which may well be installed on your system as gmake:

gmake
gmake install
gmake installcheck

If you encounter an error such as:

make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

env PG_CONFIG=/path/to/pg_config make && make installcheck && make install

And finally, if all that fails (and if you're on PostgreSQL 8.1 or lower, it likely will), copy the entire distribution directory to the contrib/ subdirectory of the PostgreSQL source tree and try it there without pg_config:

env NO_PGXS=1 make && make installcheck && make install

If you encounter an error such as:

ERROR:  must be owner of database regression

You need to run the test suite using a super user, such as the default "postgres" super user:

make installcheck PGUSER=postgres

Once temporal is installed, you can add it to a database. If you're running PostgreSQL 9.1.0 or greater, it's a simple as connecting to a database as a super user and running:

CREATE EXTENSION temporal;

If you've upgraded your cluster to PostgreSQL 9.1 and already had temporal installed, you can upgrade it to a properly packaged extension with:

CREATE EXTENSION temporal FROM unpackaged;

For versions of PostgreSQL less than 9.1.0, you'll need to run the installation script:

psql -d mydb -f /path/to/pgsql/share/contrib/temporal.sql

If you want to install temporal and all of its supporting objects into a specific schema, use the PGOPTIONS environment variable to specify the schema, like so:

PGOPTIONS=--search_path=extensions psql -d mydb -f temporal.sql

Dependencies

The temporal data type has no dependencies other than PostgreSQL.

Copyright and License

See the file LICENSE.

postgresql-temporal's People

Contributors

jeff-davis-aster avatar theory avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar

postgresql-temporal's Issues

Ruby/Sequel extension?

I noticed you were active on ruby-related projects. Might you be aware of a ruby/sequel extension that would allow to make use of the Postgres type?

make installcheck fails on postgres 9.1.2 arch linux

slave-iv% whoami           
postgres
slave-iv% psql -V
psql (PostgreSQL) 9.1.2
contains support for command-line editing

slave-iv% make installcheck | sed -e 's/^/    /'
/usr/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=. --psqldir='/usr/bin'   --inputdir=test --load-language=plpgsql --dbname=contrib_regression period
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== installing plpgsql                     ==============
CREATE LANGUAGE
============== running regression test queries        ==============
test period                   ... FAILED

======================
 1 of 1 tests failed. 
======================

The differences that caused some tests to fail can be viewed in the
file "/var/lib/postgres/PostgreSQL-Temporal/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/var/lib/postgres/PostgreSQL-Temporal/regression.out".

make: *** [installcheck] Error 1
slave-iv%

Expected behavior?

backend=# \d test_revs
                                    Table "test.test_revs"
   Column   |           Type           |                       Modifiers                       
------------+--------------------------+-------------------------------------------------------
 id         | integer                  | not null default nextval('test_id_seq'::regclass)
 rev        | integer                  | not null default nextval('test_rev_seq'::regclass)
 status     | activatable              | not null default 'draft'::activatable
 live       | boolean                  | not null default false
 draft      | boolean                  | not null default false
 start_date | timestamp with time zone | not null default (now())::timestamp(0) with time zone
 stop_date  | timestamp with time zone | not null default 'infinity'::timestamp with time zone
 name       | character varying        | not null default ''::character varying
Indexes:
    "test_revs_pkey" PRIMARY KEY, btree (rev)
    "test_id_rev_key" UNIQUE CONSTRAINT, btree (id, rev)
    "test_draft_excl" EXCLUDE USING btree (id WITH =) WHERE (draft) DEFERRABLE INITIALLY DEFERRED
    "test_live_excl" EXCLUDE USING btree (id WITH =) WHERE (live) DEFERRABLE INITIALLY DEFERRED
    "test_period_excl" EXCLUDE USING gist (id WITH =, period(start_date, stop_date) WITH &&) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "test" CONSTRAINT "test_id_draft_fkey" FOREIGN KEY (id, draft) REFERENCES test_revs(id, rev) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "test" CONSTRAINT "test_id_rev_fkey" FOREIGN KEY (id, rev) REFERENCES test_revs(id, rev) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "test_rel_revs" CONSTRAINT "test_rel_test_rev_fkey" FOREIGN KEY (test_id, test_rev) REFERENCES test_revs(id, rev) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
Triggers:
    _10_test_revs__upd AFTER UPDATE ON test_revs FOR EACH ROW WHEN (old.rev <> new.rev) EXECUTE PROCEDURE test_revs__upd()

backend=# select * from test_revs;
 id | rev | status | live | draft |       start_date       |       stop_date        | name 
----+-----+--------+------+-------+------------------------+------------------------+------
  1 |   1 | draft  | f    | f     | 2011-06-08 17:15:14+02 | 2011-06-08 17:15:14+02 | 
  1 |   2 | draft  | t    | t     | 2011-06-08 17:15:14+02 | 2011-06-08 17:15:14+02 | foo
(2 rows)

backend=# update test_revs set stop_date = stop_date + interval '1 sec';
ERROR:  conflicting key value violates exclusion constraint "test_period_excl"
DETAIL:  Key (id, period(start_date, stop_date))=(1, [2011-06-08 17:15:14+02, 2011-06-08 17:15:15+02)) conflicts with existing key (id, period(start_date, stop_date))=(1, [2011-06-08 17:15:14+02, 2011-06-08 17:15:15+02)).

I get the second one, which generates an error.

The first took me by surprise. (In a good way, too, but I'd like to make sure it's the expected behavior before I rely on it.)

PostgreSQL 9.3 make install fails

Hi,

I'm on Xubuntu 14.04.2 i386 arch. I'm trying to create temporal-enabled database following the instructions from strabon website ( http://strabon.di.uoa.gr/installation-linux).

I got an error when I tried the make install check

sudo make installcheck
/usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/lib/postgresql/9.3/bin'   --inputdir=test --load-language=plpgsql --dbname=contrib_regression period
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== installing plpgsql                     ==============
CREATE LANGUAGE
============== running regression test queries        ==============
test period                   ... FAILED

======================
 1 of 1 tests failed. 
======================

The differences that caused some tests to fail can be viewed in the
file "/home/martin/Documents/PostgreSQL-Temporal-master/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/home/martin/Documents/PostgreSQL-Temporal-master/regression.out".

make: *** [installcheck] Error 1

and in the "regression.diffs"

*** /home/martin/Documents/PostgreSQL-Temporal-master/test/expected/period.out  2012-01-24 09:02:10.000000000 +0100
--- /home/martin/Documents/PostgreSQL-Temporal-master/results/period.out    2015-05-29 15:59:52.261660971 +0200
***************
*** 1,4 ****
--- 1,5 ----
  \set ECHO 0
+ unrecognized value "0" for "ECHO"; assuming "none"
  psql:temporal.sql:14: NOTICE:  return type period is only a shell
  psql:temporal.sql:17: NOTICE:  argument type period is only a shell
  select '[2009-01-01, 2009-03-01)'::period @> '[2009-02-03, 2009-02-07)'::period;

======================================================================

Regards,
Martin

PostgreSQL 9.3 compatibility?

I've tried installing this extension into 9.3 and failed; unable to make without error and simply putting 9.1 library (.c, .so) in comparable 9.3 locations result in errors. Can I assume I'm doing something wrong, or is it not possible?

Separately, I know it's on Github, but do you have an interest to take this further with collaborators? Seems like a great starting point for the temporal datatype historians need -- plugging away at https://github.com/ComputingPlace/Topotime myself.

Karl

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.