Description So, I'm working on a project that stores sensor measurements from multiple devices in PostgreSQL+TimescaleDB database. The structure of the table (hypertable): column_name type comment identifier text device identifier key text name of the metric value_num double precision numeric…
I'm trying to calculate the average in this sample; This example is working (but only when I select a specific ID, rather than the avg for every ID limited to 20 entries) but I'm having a hard time remembering how…
Need to figure out an efficient way to query a table using another table as a filter/config (postgres 14.5). The filter table has 4 levels that match 4 levels in the data. Each level can be read as a RegExp…
I just started learning SQL and there is my problem. I have a column that contains acronyms like "GP2", "MU1", "FR10", .... and I want to add '0's to the acronyms that don't have enough characters. For example I want…
So I have a command where for example SELECT something, string_agg(other, ';') FROM table GROUP BY something HAVING COUNT(*)>1; but I don't know how to separate in two columns, because it doesn't see string_agg as a column. This is my…
I have created role under postgresql using PgAdmin. CREATE ROLE user_name LOGIN PASSWORD 'xyz'; I'm able to login to server using pgAdmin using above credentials. But when I try to connect from MSSQL Server (Babelfish) getting an following error: But…
How can I use the value from one query into the second one. I tried with an alias "papa" but I read that the order of executions makes the alias not available to the second query. What would be the…
I have a table like this CREATE TABLE userinteractions ( userid bigint, dobyr int, -- lots more fields that are not relevant to the question ); My problem is that some of the data is polluted with multiple dobyr values…
Is it possible to run PostgreSQL 11's VACUUM FULL for a short while and then get some benefit? Or does cancelling it midway cause all of its progress to be lost? I've read about pg_repack (https://aws.amazon.com/blogs/database/remove-bloat-from-amazon-aurora-and-rds-for-postgresql-with-pg_repack/) but the way it…
How to let the query result be ordered by the exact order of passed items in the WHERE clause? For example, using this query: SELECT id, name FROM my_table WHERE id in (1,3,5,2,4,6) ORDER BY id Result: id | name…