Re: Advise on primary key for detail tables (OS: Raspberry Pi)

On 04/06/2017 06:40 AM, Ertan Küçükoğlu wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
> Sent: Thursday, April 6, 2017 4:18 PM
> To: Ertan Küçükoğlu <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr>;
> pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Advise on primary key for detail tables (OS:
> Raspberry Pi)
>
>> On 04/06/2017 03:27 AM, Ertan Küçükoğlu wrote:
>>> Sorry for top posting.
>>>
>>> I have a serial in master table because I need to know data insertion
> order.
>>> DateTime on Raspberry Pi is not accurate due to power loss and lack of
>>> internet access to fetch correct time from.
>>
>> Understand, though it does beg the question, why have the uuid column?
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>
> Programmatically it is easier as I can start saving from very bottom detail
> table instead of having to save first the master table record. Users will
> directly work on detail tables. Master table will be saved & updated by
> software automatically as last piece of information.

Hmm. Well back to your original question, PK's on the detail serial
columns. Given you have a uuid column to mark uniqueness the PK on the
serial column is redundant. The issue is that database and software
above it will not 'know' that the uuid column is your PK stand in.
Putting a UNIQUE index or PK on the uuid column would help flag the
column as unique. Whether the index is used in a given query is
dependent on the scope of the query. At some point it is faster for
Postgres to just scan the table rather then incur the cost of a look up
on the index.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com