Re: Using varchar primary keys.

On Mon, Apr 1, 2013 at 1:11 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:

>
>
>
> On Tue, Apr 2, 2013 at 8:35 AM, jesusthefrog <jesusthefrog(at)gmail(dot)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.