is column order important when creating a multi-column uniqueness constraint?

[john snow]

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!

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

Post by john snow
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
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!