From: | Dani <danielcheagle(at)gmail(dot)com> |
---|---|
To: | john snow <ofbizfanster(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: is column order important when creating a multi-column uniqueness constraint? |
Date: | 2018-01-07 12:22:44 |
Message-ID: | CAEXvJLdcOakSAw7xJRSQPM5+o1N9ecX+7+V0pyHj_4CUhAi0JQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 8:35 GMT-02:00 john snow <ofbizfanster(at)gmail(dot)com>:
> we have a table for storing transaction details where the combination of
> the following 3 columns need to be a unique composite value (nulls in any
> of the columns not allowed either, so composite could probably be a primary
> key):
>
> location_code (single char like 'A', 'B', etc; currently, only 4 different
> values)
>
> transaction_id (32 bit integer)
>
> materialtype_id (32 bit integer, but currently numbers less than 10000
> values)
>
> will specifying the composite primary key or uniqueness constraint
> (during creation or definition time) in the following ways result in any
> significant differences in performance? in other words, does ordering of
> the columns matter, and if yes, why or how?
>
> primary key(location_code, transaction_id, materialtype_id)
>
> primary key(transaction_id, ...)
>
> primary key(materialtype_id, ...)
>
> ...
>
> currently, i'm thinking that when querying against each of those columns
> individually, filtering by location_id or transaction_id alone will occur
> somewhat more frequently than filtering by materialtype_id
>
> thanks for any help!
>
>
--
"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:-)
From | Date | Subject | |
---|---|---|---|
Next Message | Dani | 2018-01-07 14:11:22 | Re: ?How create a one serial decimal(500,0) column or simulate it with bigint multicolumns serial? |
Previous Message | john snow | 2018-01-07 10:35:33 | is column order important when creating a multi-column uniqueness constraint? |