Variable constants ?

On 8/15/19 1:56 PM, stan wrote:

I need to put a few bossiness constants, such as a labor rate multiplier in an
application. I am adverse to hard coding these things. The best plan i have
come up with so far is to store them in a table, which would have only 1
row, and a column for each needed constant.

Anyone have a better way to do this?

Another way(better in eye of beholder) a conf file that can be written to.

Failing a better way is there some way I can limit this table to only allow
one row to exist?

Yes add the row and REVOKE INSERT afterwards. Then you have a single row
that can be updated/deleted(though you could revoke that also).

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 15 Aug 2019, stan wrote:

I need to put a few bossiness constants, such as a labor rate multiplier
in an application. I am adverse to hard coding these things. The best plan
i have come up with so far is to store them in a table, which would have
only 1 row, and a column for each needed constant.

Anyone have a better way to do this?

Failing a better way is there some way I can limit this table to only
allow one row to exist?

Stan,

I've resolved similar issues with changing regulatory agency staff. For your
application(s) I suggest a table like this:

create table labor_rate_mult (
rate real primary_key,
start_date date not null,
end_date date
)

This provides both a history of labor rate multipliers and the ability to
select either the most current one or a previous one.

If other factors affect the rate, add attribute columns for them.

Regards,

Rich

stan <stanb@panix.com> writes:

Failing a better way is there some way I can limit this table to only allow
one row to exist?

I was recently reminded of a cute trick for that: make a unique index
on a constant.

regression=# create table consts(f1 int, f2 int);
CREATE TABLE
regression=# create unique index consts_only_one on consts((1));
CREATE INDEX
regression=# insert into consts values(1,2);
INSERT 0 1
regression=# insert into consts values(3,4);
ERROR: duplicate key value violates unique constraint "consts_only_one"
DETAIL: Key ((1))=(1) already exists.

Shepard's nearby advice about keeping some history is probably a better
plan though.

regards, tom lane

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, August 15, 2019 6:13 PM
To: stan <stanb@panix.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Variable constants ?

stan <stanb@panix.com> writes:

Failing a better way is there some way I can limit this table to only
allow one row to exist?

I was recently reminded of a cute trick for that: make a unique index on a constant.

regression=# create table consts(f1 int, f2 int); CREATE TABLE regression=# create unique index consts_only_one on consts((1)); CREATE INDEX regression=# insert into consts values(1,2); INSERT 0 1 regression=# insert into consts values(3,4);
ERROR: duplicate key value violates unique constraint "consts_only_one"
DETAIL: Key ((1))=(1) already exists.

Shepard's nearby advice about keeping some history is probably a better plan though.

regards, tom lane
____________________________________________________________________________

Here is another trick to keep a table to just one row:

reg#create table test(c1 int check(c1=1) default 1, c2 int);
CREATE TABLE
reg#create unique index one_row on test(c1);
CREATE INDEX
reg#insert into test (c2) values(3);
INSERT 01
reg# insert into test (c2) values(4);
ERROR: duplicate key value violates unique constraint "one_row"
DETAIL: Key (c1)=(1) already exists.
SQL state: 23505

Regards,
Igor Neyman

On 2019-08-15 23:27, Rich Shepard wrote:

On Thu, 15 Aug 2019, stan wrote:

I need to put a few bossiness constants, such as a labor rate
multiplier
in an application. I am adverse to hard coding these things. The best
plan
i have come up with so far is to store them in a table, which would
have
only 1 row, and a column for each needed constant.

Anyone have a better way to do this?

Failing a better way is there some way I can limit this table to only
allow one row to exist?

Stan,

I've resolved similar issues with changing regulatory agency staff. For
your
application(s) I suggest a table like this:

create table labor_rate_mult (
rate real primary_key,
start_date date not null,
end_date date
)

This provides both a history of labor rate multipliers and the ability
to
select either the most current one or a previous one.

If other factors affect the rate, add attribute columns for them.

Regards,

Rich

Another way to keep a history is using a daterange instead of two
columns for start and end date. Something like

create table labor_rate_mult (
rate real primary_key,
validity daterange not null
)

This makes it easier to manage and avoid e.g. overlappings.

Regards
Charles

On Fri, 16 Aug 2019, Charles Clavadetscher wrote:

Another way to keep a history is using a daterange instead of two columns for
start and end date. Something like

create table labor_rate_mult (
rate real primary_key,
validity daterange not null
)

Charles,

Just out of curiosity, what is the range for a rate that is still current?
Does it change every day?

Regards,

Rich

On 2019-08-16 14:50, Rich Shepard wrote:

On Fri, 16 Aug 2019, Charles Clavadetscher wrote:

Another way to keep a history is using a daterange instead of two
columns for start and end date. Something like

create table labor_rate_mult (
rate real primary_key,
validity daterange not null
)

Charles,

Just out of curiosity, what is the range for a rate that is still
current?
Does it change every day?

Regards,

Rich

Hi Rich

That would be a range with an empty upper bound. Let's say that the rate
is valid since 2019-08-14 then the range would look like

[2019-08-14,)

A query to find the current rate would look like:

SELECT rate
FROM labor_rate_mult
WHERE validity @> CURRENT_DATE;

Here you can find documentation on the range types (cool stuff I
believe):

https://www.postgresql.org/docs/11/rangetypes.html

Regards
Charles

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Neugasse 84
CH – 8005 Zürich

http://www.swisspug.org

+---------------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| Swiss PostgreSQL |
| Users Group |
| |
+---------------------------+

On Thu, Aug 15, 2019 at 11:27 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

create table labor_rate_mult (
rate real primary_key,
start_date date not null,
end_date date
)

I think the rate should not be the primary key, since that would
prevent keeping the whole history when the value is resetted to a
previous one. Probably here a surrogate key will make the trick.

The range solution is probably a more elegant one.

I would also propose the over-complicated possibility of making an
extension wrapping functions that return each single constant value.
In this way, changing the value means upgrading the extension and is
another way to keep history of changes, but probably is because I
don't like one-raw tables so much.

Luca

On Fri, 16 Aug 2019, Charles Clavadetscher wrote:

That would be a range with an empty upper bound. Let's say that the rate
is valid since 2019-08-14 then the range would look like
[2019-08-14,)
A query to find the current rate would look like:

SELECT rate
FROM labor_rate_mult
WHERE validity @> CURRENT_DATE;

Here you can find documentation on the range types (cool stuff I believe):
https://www.postgresql.org/docs/11/rangetypes.html

Charles,

Interesting. Certainly worth considering the next time an application needs
a range of dates.

Regards,

Rich

On 16/08/2019 09:27, Rich Shepard wrote:

On Thu, 15 Aug 2019, stan wrote:

I need to put a few bossiness constants, such as a labor rate multiplier
in an application. I am adverse to hard coding these things. The best
plan
i have come up with so far is to store them in a table, which would have
only 1 row, and a column for each needed constant.

Anyone have a better way to do this?

Failing a better way is there some way I can limit this table to only
allow one row to exist?

Stan,

I've resolved similar issues with changing regulatory agency staff.
For your
application(s) I suggest a table like this:

create table labor_rate_mult (
ￜ rateￜￜￜￜￜￜￜￜ real primary_key,
ￜ start_dateￜￜￜￜￜ date not null,
ￜ end_dateￜￜￜ date
)

This provides both a history of labor rate multipliers and the ability to
select either the most current one or a previous one.

If other factors affect the rate, add attribute columns for them.

Regards,

Rich

I think a better approach is to:

ￜ* include time
ￜ* store independent of timezone (avoids problems with daylight saving)
ￜ* only have one timestamp

ￜￜ DROP TABLE IF EXISTS labour_rate_mult;

ￜￜ CREATE TABLE labour_rate_mult
ￜￜ (
ￜￜￜ ￜￜￜ rate_nameￜￜￜￜￜￜￜￜ text,
ￜￜￜ ￜￜￜ effective_startￜￜ timestamptz,
ￜￜￜ ￜￜￜ rate_valueￜￜￜￜￜￜￜ real,
ￜￜￜ ￜￜￜ validￜￜￜￜￜￜￜￜￜￜￜￜ boolean,
ￜￜￜ ￜￜￜ PRIMARY KEY (rate_name, effective_start)
ￜￜ );

ￜￜ INSERT INTO labour_rate_mult
ￜￜ (
ￜￜￜ ￜￜￜ rate_name,
ￜￜￜ ￜￜￜ effective_start,
ￜￜￜ ￜￜￜ rate_value,
ￜￜￜ ￜￜￜ valid
ￜￜ )
ￜￜ VALUESￜ -- test data omits time for clarity
ￜￜￜ ￜￜￜ ('junior', '2001-02-01', 4.2, true),
ￜￜￜ ￜￜￜ ('junior', '2008-11-16', 6, true),
ￜￜￜ ￜￜￜ ('junior', '2012-07-23', 4.5, true),
ￜￜￜ ￜￜￜ ('junior', '2019-09-11', 3.7, true),
ￜￜￜ ￜￜￜ ('junior', '2030-12-31', 0, false),
ￜￜￜ ￜￜￜ ('adult', '2001-01-01', 8.4, true),
ￜￜￜ ￜￜￜ ('adult', '2012-07-23', 9.9, true),
ￜￜￜ ￜￜￜ ('adult', '2030-05-03', 0, false)
ￜￜ /**/;/**/

ￜￜ SELECT
ￜￜￜ ￜￜￜ rate_value
ￜￜ FROM
ￜￜￜ ￜￜￜ labour_rate_mult
ￜￜ WHERE
ￜￜￜ ￜￜￜￜￜￜￜ rate_name = 'junior'
ￜￜￜ ￜￜￜ AND effective_start <= '2012-07-23' -- stand in for
ￜￜ CURRENT_TIMESTAMP
ￜￜￜ ￜￜￜ AND valid
ￜￜ ORDER BY
ￜￜￜ ￜￜￜ effective_start DESC
ￜￜ LIMIT 1
ￜￜ /**/;/**/

Cheers.
Gavin

P.S.
Previously, I accidentally just sent it to Rich!