Re: Catching unique_violation exception on specific column/index

On 06/11/2018 11:21 AM, Alexey Dokuchaev wrote:
> On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:
>> If that functionality is an important part of your code, you should
>> consider upgrading to 10 (or 9.6 if your are really conservative)
>> rather sooner than later.
>
> Oh well, fair enough. As much as I'd love to stick to the lowest
> supported (and sometimes even unsupported) versions, ON CONFLICT is
> indeed very handy, esp. since I have a few UPSERT's implemented the
> old way already (via catching the "unique_violation" exception).
>
> Shall I update to 9.6/10, I have a bit off-topic (to the original
> subject) question: right now, when I need to get the length of an
> array (never multidimensional), I do this:
>
> coalesce(array_length(foo, 1), 0);
>
> In 9.4+, I can call cardinality(). I'm a bit hesitant: is doing so
> semantically correct, or should I still do coalesce(..., 0) in this
> case? This is not about the outcome, it's whether cardinality() is
> semantically correct to obtain the number of the array items, or it
> was introduced for other means?

https://www.postgresql.org/docs/10/static/arrays.html

8.15.3. Accessing Arrays

"... cardinality returns the total number of elements in an array across
all dimensions. It is effectively the number of rows a call to unnest
would yield: ..."

>
> ./danfe
>

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