Thread: ?How create a one serial decimal(500,0) column or simulate it withbigint multicolumns serial?
?How create a one serial decimal(500,0) column or simulate it withbigint multicolumns serial?
From
Dani
Date:
Hi! All!
I Need create a one decimal serial column or simulate it with multiple columns of bigint.create table bank (
id0 decimal( 500, 0) not null auto-increment,
etc text
)
)
or
create table bank (
id0 bigint not null,
id1 bigint not null,
id2 bigint not null,
idn bigint not null,
etc text
primary key(id0, id1, id2, idn),
auto_increment(id0, id1, id2, idn)
)
id0 id1 id2 idn text
1 1 1 1 T
1 1 1 2 T
1 1 1 3 T
1 1 2 1 T
1 1 2 2 T
1 1 2 3 T
1 1 3 1 T
1 1 3 2 T--
"There are many plans in the Human heart, But
is the Lord's Purpose that prevails"
"Existem Muitos planos e desejos no coração Humano, MAS
são os Propósitos do Senhor que prevalecem"
[]'s Dani:-)
is the Lord's Purpose that prevails"
"Existem Muitos planos e desejos no coração Humano, MAS
são os Propósitos do Senhor que prevalecem"
[]'s Dani:-)
Re: ?How create a one serial decimal(500,0) column or simulate it with bigint multicolumns serial?
From
Andreas Kretschmer
Date:
On 7 January 2018 07:17:29 CET, Dani <danielcheagle@gmail.com> wrote: >Hi! All! > >I Need create a one decimal serial column or simulate it with multiple >columns of bigint. > Use 1 normal serial column and calculate that during the select, maybe using a VIEW. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company
Re: ?How create a one serial decimal(500,0) column or simulate itwith bigint multicolumns serial?
From
"David G. Johnston"
Date:
On Saturday, January 6, 2018, Dani <danielcheagle@gmail.com> wrote:
Hi! All!I Need create a one decimal serial column or simulate it with multiple columns of bigint.
You will need to write the logic yourself using an insert triggers - default nextval(sequence-name) isn't going to work.
David J.
Re: ?How create a one serial decimal(500,0) column or simulate itwith bigint multicolumns serial?
From
Dani
Date:
Hi!
Thanks for the quotes!
My need is one column per table (primary id and auto_increment) and bigger max value than bigint :-). for now the max value for a sequence
in Postgresql is bigint.
Examples of one possible solution:
------------------------------ -------------------------
CREATE TABLE decimal_hold0
(
id bigserial not null primary key,
actual_value numeric(1000,0) not null default 0,
comment text not null
)
------------------------------ ------------------------
AS
$$
Update decimal_hold0 Set actual_value = actual_value + 1 where id = miid Returning actual_value;
$$ LANGUAGE SQL;
Really Many Thanks in Advance!!! :-) ![](https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif)
Thanks for the quotes!
My need is one column per table (primary id and auto_increment) and bigger max value than bigint :-). for now the max value for a sequence
in Postgresql is bigint.
Examples of one possible solution:
------------------------------
CREATE TABLE decimal_hold0
(
id bigserial not null primary key,
actual_value numeric(1000,0) not null default 0,
comment text not null
)
------------------------------
in first time use => Insert into decimal_hold0(comment) Values ('1a_column_for_money_dindin_ table_seq');
optional (just for better understanding)
=>Select * from decimal_hold0;
=> result is"1, 0, '1a_column_for_money_dindin_ table_seq'"
=>Select * from decimal_hold0;
=> result is"1, 0, '1a_column_for_money_dindin_
------------------------------ ------------------------
create or replace function next_value(miid bigint) returns decimal(1000, 0)AS
$$
Update decimal_hold0 Set actual_value = actual_value + 1 where id = miid Returning actual_value;
$$ LANGUAGE SQL;
------------------------------ -------------------------
create table money_dindin (
id decimal(1000, 0) not null primary key default next_value(1),
eba text
)
create table money_dindin (
id decimal(1000, 0) not null primary key default next_value(1),
eba text
)
the real thing is showing in money_dindin.id :-)
insert into money_dindin (eba) Values ('eba1'), ('eba2'), ('eba3'), ('eba4'), ('eba5'), ('eba6')
testes=# select * from money_dindin ;
id | eba
----+------
1 | eba1
2 | eba2
3 | eba3
4 | eba4
5 | eba5
6 | eba6
(6 registros)
:-)
insert into money_dindin (eba) Values ('eba1'), ('eba2'), ('eba3'), ('eba4'), ('eba5'), ('eba6')
testes=# select * from money_dindin ;
id | eba
----+------
1 | eba1
2 | eba2
3 | eba3
4 | eba4
5 | eba5
6 | eba6
(6 registros)
:-)
My renew question(s) is
1) how make function next_value thread safe and transaction safe (or is this already safe? )
2) how simulate a sequence with type Decimal(x, 0)
3) and se possible, get ride the decimal_hold0 table :-)
Any Ideia is Wellcome :-)
![](https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif)
2018-01-07 6:45 GMT-02:00 David G. Johnston <david.g.johnston@gmail.com>:
On Saturday, January 6, 2018, Dani <danielcheagle@gmail.com> wrote:Hi! All!I Need create a one decimal serial column or simulate it with multiple columns of bigint.You will need to write the logic yourself using an insert triggers - default nextval(sequence-name) isn't going to work.David J.
--
"There are many plans in the Human heart, But
is the Lord's Purpose that prevails"
"Existem Muitos planos e desejos no coração Humano, MAS
são os Propósitos do Senhor que prevalecem"
[]'s Dani:-)
is the Lord's Purpose that prevails"
"Existem Muitos planos e desejos no coração Humano, MAS
são os Propósitos do Senhor que prevalecem"
[]'s Dani:-)
Re: ?How create a one serial decimal(500,0) column or simulate itwith bigint multicolumns serial?
From
"David G. Johnston"
Date:
On Sunday, January 7, 2018, Dani <danielcheagle@gmail.com> wrote:
Hi!
Thanks for the quotes!
My need is one column per table (primary id and auto_increment) and bigger max value than bigint :-). for now the max value for a sequence
in Postgresql is bigint.
Create two bigint sequences. Call nextval on the first one. If the value given is somewhere near the max call nextval on the other and reset the first one, otherwise use currentval on the other sequence.
That should get you started: the docs should be able to fill in the blanks.
Frankly, I'd wait to add that second bigint until you are nearing exhaustion of the first bigint sequence. Given you expected volume how long until the first sequence is exhausted?
David J.
Re: ?How create a one serial decimal(500,0) column or simulate itwith bigint multicolumns serial?
From
Andreas Kretschmer
Date:
Am 07.01.2018 um 07:17 schrieb Dani: > Hi! All! > > I Need create a one decimal serial column or simulate it with multiple > columns of bigint. > > gaps don't are problem. > > pseudo-code: > > create table bank ( > id0 decimal( 500, 0) not null auto-increment, > etc text > ) > > or > > create table bank ( > id0 bigint not null, > id1 bigint not null, > id2 bigint not null, > idn bigint not null, > etc text > primary key(id0, id1, id2, idn), > auto_increment(id0, id1, id2, idn) > ) > > How a example of behavior (using a range of 1 .. 3) > > insert into bank(etc) Values "3*3*3 times 'Thanks'"; > > select * from bank; > > id0 id1 id2 idn text > 1 1 1 1 T > 1 1 1 2 T > 1 1 1 3 T > 1 1 2 1 T > 1 1 2 2 T > 1 1 2 3 T > 1 1 3 1 T > 1 1 3 2 T > 1 1 3 3 T > 1 2 1 1 T > etc. Not sure if i understand you, but maybe you are looking for somethink like this: test=*# select s, (s/9)%3 as "3^2", (s/3)%3 as "3^1", s%3 as "3^0" from generate_series(0,20) s; s | 3^2 | 3^1 | 3^0 ----+-----+-----+----- 0 | 0 | 0 | 0 1 | 0 | 0 | 1 2 | 0 | 0 | 2 3 | 0 | 1 | 0 4 | 0 | 1 | 1 5 | 0 | 1 | 2 6 | 0 | 2 | 0 7 | 0 | 2 | 1 8 | 0 | 2 | 2 9 | 1 | 0 | 0 10 | 1 | 0 | 1 11 | 1 | 0 | 2 12 | 1 | 1 | 0 13 | 1 | 1 | 1 14 | 1 | 1 | 2 15 | 1 | 2 | 0 16 | 1 | 2 | 1 17 | 1 | 2 | 2 18 | 2 | 0 | 0 19 | 2 | 0 | 1 20 | 2 | 0 | 2 (21 rows) test=*# -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com