Обсуждение: ?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?

От
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.


Thanks So Much!!!!

[]'s Dani. :-)


--
"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:-)

Re: ?How create a one serial decimal(500,0) column or simulate it with bigint multicolumns serial?

От
Andreas Kretschmer
Дата:
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?

От
"David G. Johnston"
Дата:
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?

От
Dani
Дата:
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
)
------------------------------------------------------
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'"
------------------------------------------------------
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
)

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)

:-)
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 :-)

Really Many Thanks in Advance!!! :-)

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:-)

Re: ?How create a one serial decimal(500,0) column or simulate itwith bigint multicolumns serial?

От
"David G. Johnston"
Дата:
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?

От
Andreas Kretschmer
Дата:

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