On Mon, Apr 1, 2013 at 1:11 PM, Tim Uckun <timuc...@gmail.com> wrote:
>
>
>
> On Tue, Apr 2, 2013 at 8:35 AM, jesusthefrog <jesusthef...@gmail.com>wrote:
>
>> On the topic of 'natural' versus 'synthetic' primary keys, I am generally
>> in the camp that an extra ID field won't cost you too much, and while one
>> may not need it for a simple table (i.e. id, name) one might add any number
>> of columns later, and you'll be glad to have it.
>>
>>
> Nothing prevents you from adding more columns if you use varchar primary
> keys.
>
>
>>
>> My preferred method is to give every table an ID column of UUID type and
>> generate a UUID using the uuid-ossp contrib module. This also prevents
>> someone not familiar with the database design from using an ID somewhere
>> they should not (as is possible with natural PKs) or treating the ID as an
>> integer, not an identifier (as is all too common with serial integers).
>>
>>
>>
> This would be a concern if you had multi master writes . As far as I know
> Postgres does not have a true multi master replication system so all the
> writes have to happen on one server right?
>
> As for UUIDs I use them sometimes but I tend to also use one serial column
> because when I am communicating with people it makes it so much easier to
> say "dealer number X" than "dealer number SOME_HUGE_UUID". I often have to
> talk to people about the data and UUIDs make it very difficult to
> communicate with humans.
>
I've been wishing for a smaller uuid type for a while. Say you need to
assign a Order #. Customers might be referencing the number, so you don't
want it to be too long. But you don't want Order #'s to be guessable or
have the characters/digits be transposed accidently.
I've been using a unique text column with a default of random_characters(12)
CREATE OR REPLACE FUNCTION public.random_characters(length integer)
RETURNS text
LANGUAGE sql
STABLE
AS $function$
SELECT array_to_string(array((
SELECT SUBSTRING('abcdefghjkmnpqrstuvwxyz23456789'
FROM mod((random()*31)::int, 31)+1 FOR 1)
FROM generate_series(1, $1))),'');
$function$;
This seems to work ok. I don't allow 1's or l's or 0's or O's, as they can
easily be mistyped or misread.
