pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: anjithapaul@gmail.com
Subject: BUG #16753: 'expected 2-element int8 array' error while getting data using query with subquery
Date: Mon, 30 Nov 2020 18:17:59 +0000
Message-ID: <16753-0e156a5fd6cee2d3@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16753
Logged by:          Anjitha Paulose
Email address:      anjithapaul@gmail.com
PostgreSQL version: 12.5
Operating system:   Windows, Linux
Description:        

I am using timescale db on top of postgres db. 
I am having table with mac_address and uptime. Mac address will be given
with colon or without colon. I am having the following data

   mac_address   |      timestamp      | uptime
-------------------+-----------------------------+----------
 AA:BB:45:TT:CC:34 | 2020-11-30 00:00:04 |    0
 112233445566         | 2020-11-30 00:00:06 |    0
 NN:CC:33:33:22:22 | 2020-11-30 00:00:05 |    0
 AA:BB:45:TT:CC:34 | 2020-11-30 00:30:04 |    1
 112233445566         | 2020-11-30 00:30:06 |    1
 NN:CC:33:33:22:22 | 2020-11-30 00:30:05 |    1 

I have created an hourly continous agggregate view to get average uptime in
each hour using below query

CREATE VIEW aggregated_data
WITH (timescaledb.continuous) AS
SELECT
time_bucket(INTERVAL '1 hour', timestamp) AS time_bucket,
mac_address,
AVG(uptime) AS avg_uptime
FROM data_table
GROUP BY time_bucket, mac_address
When I am trying to get data from aggregated_data table using below query, I
am getting error 'expected 2-element int8 array'

SELECT mac_address, time_bucket,
avg_uptime, count(*) over() as count
FROM aggregated_data
WHERE time_bucket >= '2020-10-28 18:00:00' and time_bucket < '2020-11-30
08:31:52'
AND mac_address IN (
          SELECT mac_address
          FROM aggregated_data
          WHERE time_bucket >= '2020-11-28 18:00:00' and time_bucket <
'2020-11-30 08:31:52'
          GROUP BY mac_address
          HAVING AVG(avg_uptime) > 0 AND AVG(avg_uptime) <=10
  )
ORDER BY time_bucket LIMIT 10 OFFSET 0



view thread (2+ messages)  latest in thread

Message-ID: <16753-0e156a5fd6cee2d3@postgresql.org>
Permalink:  ../16753-0e156a5fd6cee2d3@postgresql.org/
Also on:    postgresql.org/message-id/16753-0e156a5fd6cee2d3@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, anjithapaul@gmail.com
  Subject: Re: BUG #16753: 'expected 2-element int8 array' error while getting data using query with subquery
  In-Reply-To: <16753-0e156a5fd6cee2d3@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox