Re: Variable constants ?

[Peter J. Holzer]

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
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Variable constants ?

[Gavin Flower]

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!

Re: Variable constants ?

[Rich Shepard]

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

Re: Variable constants ?

[Luca Ferrari]

On Thu, Aug 15, 2019 at 11:27 PM Rich Shepard  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

Re: Variable constants ?

[Charles Clavadetscher]

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 |
|   |
+---+

Re: Variable constants ?

[Rich Shepard]

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

Re: Variable constants ?

[Charles Clavadetscher]

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_datedate 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

RE: Variable constants ?

[Igor Neyman]

-Original Message-
From: Tom Lane  
Sent: Thursday, August 15, 2019 6:13 PM
To: stan 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Variable constants ?

stan  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

Re: Variable constants ?

[Tom Lane]

stan  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

Re: Variable constants ?

[Rich Shepard]

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_datedate 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

Re: Variable constants ?

[Adrian Klaver]

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.kla...@aklaver.com

Variable constants ?

[stan]

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