Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEun4-004p5X-0x for pgsql-general@arkaria.postgresql.org; Wed, 05 Jun 2024 17:51:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sEun4-00AF8P-1s for pgsql-general@arkaria.postgresql.org; Wed, 05 Jun 2024 17:51:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEun3-00AF8F-FL for pgsql-general@lists.postgresql.org; Wed, 05 Jun 2024 17:51:13 +0000 Received: from mail-lf1-x131.google.com ([2a00:1450:4864:20::131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sEumx-003c4g-Ja for pgsql-general@postgresql.org; Wed, 05 Jun 2024 17:51:12 +0000 Received: by mail-lf1-x131.google.com with SMTP id 2adb3069b0e04-52b8b7b8698so175262e87.1 for ; Wed, 05 Jun 2024 10:51:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=f10-com-br.20230601.gappssmtp.com; s=20230601; t=1717609864; x=1718214664; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=BuH8vJSGer2XcJj4GuwokbeTMOh1xhv85XB9LAsUOJg=; b=Bf54X+CHNlVtGlZ49guqCJkyMj8OHX1z86iT21JfobjIpz8N3puhePorXH/rMrZ1sT fcxaTADpzlP2myPETdyYEIzUOZ8bCd1tLI5pIC0jvxtxju4JE8eNM/H6niJ4075tsMAO hwT+YqYGApLoALhrI+EwaAh5pRU9m9ef7N5Ykq0QrFQmuRzLRjT4sw+KX0sFA7DqLtRT m72Naktp4AFZrHzCf1wW7eNBGNnIh5J1vF9BmB6J7YhwMZYEZQ2Vn9d1Ia1Vi4YOFP4J lSbfy1Z152Up2FDa4XKOX2BhyVhMtRIGBCV63c275+JM2ygwV4hNNgeuxGS2RDFJmJFp 9Jzw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717609864; x=1718214664; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=BuH8vJSGer2XcJj4GuwokbeTMOh1xhv85XB9LAsUOJg=; b=KQpo+CZ8oMvCxkZinwbqzpi852cbf2nc+g8y26tP26p7mPzZ86v8V0l4C3G8PFg1Su fSjrkNOKIcXCWcruYdSm/wJH7HZfTKlDINT2jDQytvlMbZe29KKVS24FX1GaQnEXna6L tkYxugiGeHYenMHCCqG0XiTdRfVZbFt9doePqa2lzFOb5u9aTaRrF8ROSI8i7dWiy3Bv isIhKfynzWu7ZoMHVqqfNAVDzD9i5RbCf5w7VFha4M3ckir+PFH3kEYQdaItdA/JecV/ FPofrrVzsV6zGoFPb4VkUn6/D9/sagjIJUIFrftGFhA8CO2ukB0UQ4cn0TzzKnJQ96lQ qotQ== X-Gm-Message-State: AOJu0YzOaCF0XUswsbSrj0IHlySlKAqeixXx/BDmb6kfktXuGlgycZ9T x0/HBG71np1pjXcD/ltv0zP0xT+BoppzH7FKkawvGInbGcJCvnVG7wKPRiUpGCJnYNffJVxMDKX uzvjI1T6v1GI6fBrfuAj+RdyiIVfH9atLQWh8SJwn2zDEpFZeXw== X-Google-Smtp-Source: AGHT+IErn1AdjiSMeUVJ6eumnO/Ll26zuc0aRoDU0nJh4tCZwefNCQ5FEAya5/8rDV4Z8KexIeJBbNNCn7z59tickQI= X-Received: by 2002:ac2:58db:0:b0:52b:853e:4d44 with SMTP id 2adb3069b0e04-52bab4cf6c0mr1835098e87.35.1717609864494; Wed, 05 Jun 2024 10:51:04 -0700 (PDT) MIME-Version: 1.0 From: Marcos Pegoraro Date: Wed, 5 Jun 2024 14:50:26 -0300 Message-ID: Subject: Length returns NULL ? To: Postgres General Content-Type: multipart/alternative; boundary="000000000000d622ec061a283593" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d622ec061a283593 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable There are some functions called ...length, but only array_length returns NULL on empty array, why ? select array_length('{}'::text[],1), -->NULL jsonb_array_length('[]'), -->0 bit_length(''), -->0 octet_length(''), -->0 length(''), -->0 char_length(''), -->0 length(B''); -->0 I know, it is documented, but the question is, why does it work differently ? array_length ( anyarray, integer ) =E2=86=92 integer Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.) array_length(array[1,2,3], 1) =E2=86=92 3 array_length(array[]::int[], 1) =E2=86=92 NULL array_length(array['text'], 2) =E2=86=92 NULL --000000000000d622ec061a283593 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
There are some functions called ...length, but on= ly=C2=A0array_length returns NULL on empty array, why ?

<= /div>
select array_length('{}'::text[],1), -->NULL
= =C2=A0 =C2=A0 =C2=A0 =C2=A0jsonb_array_length('[]'), -->0
=C2= =A0 =C2=A0 =C2=A0 =C2=A0bit_length(''), -->0
=C2=A0 =C2=A0 = =C2=A0 =C2=A0octet_length(''), -->0
=C2=A0 =C2=A0 =C2=A0 =C2= =A0length(''), -->0
=C2=A0 =C2=A0 =C2=A0 =C2=A0char_length(&#= 39;'), -->0
=C2=A0 =C2=A0 =C2=A0 =C2=A0length(B''); -->= ;0
I know, it is documented, but the= question is, why does it work differently ?

array_length=C2=A0(=C2=A0an= yarray,=C2=A0integer=C2=A0) =E2=86=92=C2= =A0integer
Returns the length of the reque= sted array dimension. (Produces NULL instead of 0 for empty or missing arra= y dimensions.)

array_length(array[1,2,3], 1)=C2=A0=E2=86=92=C2=A03
array_length(array[]::int[], 1)=C2= =A0=E2=86=92=C2=A0NU= LL
array_length(array['text'], 2)=C2= =A0=E2=86=92=C2=A0NU= LL
--000000000000d622ec061a283593--