Thread: Variable constants ?
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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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!
On 2019-08-15 16:56:57 -0400, stan wrote:
> bossiness constants
On 2019-09-02 13:31:14 -0400, stan wrote:
> bossiness plan
> bossiness model
On 2019-09-13 05:57:33 -0400, stan wrote:
> bossiness work
I'm starting to think that this is not a typo :-)
SCNR,
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>