[SQL] generate_series() with TSTZRANGE
Hi everyone!
I'm new around here, so please forgive me if this is a bit trivial. It
seems that generate_series() won't generate time stamp ranges. I
googled around and didn't see anything handy, so I wrote this out and
thought I'd share and see if perhaps there was a better way to do it:
SELECT tstzrange((lag(a) OVER()), a, '[)')
FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
12:00:00', '1 hour')
AS a OFFSET 1;
Basically, it's generating a series of time stamps one hour apart, then
using the previous record and the current record to construct the
TSTZRANGE value. It's offset 1 to skip the first record, since there is
no previous record to pair with it.
If you were looking at Josh Berkus' example at
http://lwn.net/Articles/497069/ you might use it like this to generate
data for testing and experimentation:
INSERT INTO room_reservations
SELECT 'F104', 'John', 'Another Talk',
tstzrange((lag(a) OVER()), a, '[)')
FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
12:00:00', '1 hour')
AS a OFFSET 1;
Thanks!
--
Wolfe Whalen
wo...@quios.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] generate_series() with TSTZRANGE
That's much better, thank you!
--
Wolfe Whalen
wo...@quios.net
On Thu, Sep 13, 2012, at 06:52 AM, Sergey Konoplev wrote:
> On Thu, Sep 13, 2012 at 3:53 AM, Wolfe Whalen
> wrote:
> > SELECT tstzrange((lag(a) OVER()), a, '[)')
> > FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
> > 12:00:00', '1 hour')
> > AS a OFFSET 1;
>
> What about this form?
>
> select tstzrange(a, a + '1 hour'::interval, '[)')
> from generate_series(
> '2012-09-16'::timestamp,
> '2012-09-16 23:00'::timestamp,
> '1 hour'::interval) as a;
>
> >
> > Basically, it's generating a series of time stamps one hour apart, then
> > using the previous record and the current record to construct the
> > TSTZRANGE value. It's offset 1 to skip the first record, since there is
> > no previous record to pair with it.
> >
> > If you were looking at Josh Berkus' example at
> > http://lwn.net/Articles/497069/ you might use it like this to generate
> > data for testing and experimentation:
> >
> > INSERT INTO room_reservations
> > SELECT 'F104', 'John', 'Another Talk',
> > tstzrange((lag(a) OVER()), a, '[)')
> > FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
> > 12:00:00', '1 hour')
> > AS a OFFSET 1;
> >
> > Thanks!
> >
> > --
> > Wolfe Whalen
> > wo...@quios.net
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Sergey Konoplev
>
> a database and software architect
> http://www.linkedin.com/in/grayhemp
>
> Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] unique keys / foreign keys on two tables
Hi Gary,
The most straightforward way to ensure that the two tables have unique
IDs would be to create one sequence called something like
"destination_seq" and have the id column in both tables default to
NEXTVAL('destination_seq').
As far as storing the destinations go, I'm guessing that you're looking
for a good way to tell what type of id it is without checking both
tables to see which one it exists in. If you need to be able to extract
the destination type from the ID, you could go with something more
robust like:
CREATE OR REPLACE FUNCTION dest_nextval (dest_type int) RETURNS int4 AS
$$
BEGIN
RETURN (nextval('destination_seq') << 1) | dest_type;
END;
$$LANGUAGE plpgsql;
That would use the same destination_seq value, but it it would shift the
number 1 bit to the left. So you could use DEFAULT dest_nextval(0) for
Extensions and DEFAULT dest_nextval(1) for Groups. Your IDs would still
be 100% unique, but you could test for the type by checking IF (id & 1)
= 1 or 0.
The | is a "bitwise" operator for "OR", and the & is the bitwise
operator for AND. They're covered a bit in section 9.3 of the
documentation, but if you went this route you'd probably want to look up
a more thorough explanation of bitwise operations in general.
The same principle works with larger numbers if you needed more types.
If you shifted two bits, you could | by 0, 1, 2, or 3. Shifting 3 bits
would give you 8 possibilities and so on. It's a pretty neat way to
"encode" other information into one field.
I hope that helps!
Best,
Wolfe
--
Wolfe Whalen
wo...@quios.net
On Thu, Nov 29, 2012, at 02:14 AM, Gary Stainburn wrote:
> I'm designing the schema to store a config from our switchboards.
>
> As with all PBX's the key is the dialed number which is either an
> extension
> number or a group (hunt/ring/pickup) number.
>
> I have two tables, one for extensions and one for groups, basically
>
> ext_id int4 primary key
> ext_desctext
>
>
>
>
> and
>
>
>
> grp_id int4 primary key
> grp_desctext
> .
> .
> .
>
> I now need to be able to ensure the id field is unique across both
> tables.
> Presumably I can do this with a function and a constraint for each table.
> Does anyone have examples of this?
>
>
> Next I have other tables that refer to *destinations* which will be an ID
> that
> could be either an extension or a group. Examples are 'Direct Dial In'
> numbers which could point to either. How would I do that?
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres trigger issue with update statement in it.
Hi Kaleeswaran,
We're glad to have you on the mailing list. I don't know enough about
your trigger function to know exactly where it's going wrong, but I
threw together a quick example that has an insert trigger on a child
table that updates a row on the parent table. I'm hoping this might
help. If it doesn't help, maybe you could give us a little more
information about your function or tables. I'd be happy to help in any
way that I can.
CREATE TABLE survey_records (
name varchar(100),
obsoleted timestamp DEFAULT NULL
);
CREATE TABLE geo_surveys (
measurement integer
) INHERITS (survey_records);
CREATE OR REPLACE FUNCTION obsolete_old_surveys() RETURNS trigger AS $$
BEGIN
UPDATE survey_records SET obsoleted = clock_timestamp()
WHERE survey_records.name = NEW.name AND survey_records.obsoleted
IS NULL;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER obsolete_old_surveys_tr
BEFORE INSERT ON geo_surveys
FOR EACH ROW EXECUTE PROCEDURE obsolete_old_surveys();
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide',
5);
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide',
10);
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide',
93);
You'd wind up with something like this:
SELECT * FROM survey_records;
name | obsoleted
+
Carbon Dioxide | 2013-04-03 23:59:44.228225
Carbon Dioxide | 2013-04-03 23:59:53.66243
Carbon Dioxide |
(3 rows)
SELECT * FROM geo_surveys;
name | obsoleted | measurement
++-
Carbon Dioxide | 2013-04-03 23:59:44.228225 | 5
Carbon Dioxide | 2013-04-03 23:59:53.66243 | 10
Carbon Dioxide || 93
(3 rows)
The parent survey_records is actually updating the child table rows
when you do an update. Parent tables can almost seem like a view in
that respect. You would have to be a bit careful if you're going to
have an update trigger on a child that updated the parent table. It's
easy to wind up with a loop like this:
Child: Update row 1 -> Trigger function -> Update Row 1 on parent
->Parent: Let's see... Row 1 is contained in this child table, so
let's update it there.
->Child: Update row 1 -> Trigger function -> Update Row 1 on parent
->Parent: Let's see... Row 1 is contained in this child table, so
let's update it there.
... etc etc.
Best Regards,
Wolfe
--
Wolfe Whalen
wo...@quios.net
On Wed, Apr 3, 2013, at 09:08 PM, Kaleeswaran Velu wrote:
Hello Friends,
I am new to Postgres DB. Recently installed Postgres 9.2.
Facing an issue with very simple trigger, tried to resolve myself by
reading documents or google search but no luck.
I have a table A(parent) and table B (child). There is a BEFORE INSERT
OR UPDATE trigger attached in table B. This trigger has a update
statement in it. This update statement should update a respective
record in table A when ever there is any insert/update happen in table
B. The issue here is where ever I insert/update record in table B,
getting an error as below :
** Error **
ERROR: cannot begin/end transactions in PL/pgSQL
SQL state: 0A000
Hint: Use a BEGIN block with an EXCEPTION clause instead.
Context: PL/pgSQL function func_update_payment() line 53 at SQL
statement
Line no 53 in the above error message is an update statement. If I
comment out the update statement, trigger works fine.
Can anyone shed some lights on this? Your help is appreciated.
Thanks and Regards
Kaleeswaran Velu
Re: [SQL] DELETE...RETURNING problem with libpq
Hi Brice,
I believe that you'll need PQcmdTuples - "Returns the number of rows
affected by the SQL command."
Watch out, it returns char* instead of int. I believe it's supposed to
be used for the UPDATE ... RETURNING as well, but I'd double check on
that.
It's under "30.3.3. Retrieving Result Information for Other Commands"
in the 8.4 docs ("These functions are used to extract information
from PGresult objects that are not SELECT results."):
[1]http://www.postgresql.org/docs/8.4/static/libpq-exec.html
Let us know if that helps or if we should dig into it a little deeper.
Best regards,
Wolfe
--
Wolfe Whalen
wo...@quios.net
On Sat, May 25, 2013, at 04:07 AM, Brice André wrote:
Dear all,
I am trying to translate a code written in php to C++. So, I am now
using lipq in order to access my postgresql database from C++.
As performance is an important feature, I am using prepared statements.
I have a SQL statement that performs a 'DELETE ... RETURNING ... '
stuff and I execute it from a prepared statement (using PQprepare and
PQexecPrepared). Now, when I execute this command, it properly deletes
requested row, but when I use command PQntuples, it returns 0, as if no
data was returned.
When I execute the same sql command from PgAdmin or from my old php
script (that did not use prepared statements), everything works fine.
Note that, in another part of my script, I use the same technique to
perform an 'UPDATE ... RETURNING' and it works properly...
Does anyone has an idea of what may fail and how I can solve this
problem ?
Regards,
Brice
PS : my postgresql server version is 8.4 and it is running on a Debian
server, if it may help.
References
1. http://www.postgresql.org/docs/8.4/static/libpq-exec.html
