I am attempting to run a spearman correlation on some data under the column name solar+. However, when attempting to use select statements to select the desired number of rows from the egauge13830 table, the plus sign is considered an operator and causes an error. These are the 2 statements I’ve tried a…
Tag: postgresql
select current address
I am new in sql and have a table as folow: patientid |gender|yearbirth|zipcode|admission | ———–|——|———|——-|——————-| P1213060727|w | 1926|55268 |2017-01-23 16:28:00| P1213060727|w | 1926|55270 |2018-09-26 18:1…
behavior of sql query with OFFSET and fetch clause in postgres/oracle with deletion of records
I have a query like : select * from mytable order by mycol_1 OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY; Now, if in this iteration ,I delete several records out of the 10 records fetched by above query …
Calculate mode() on an array column without skewing averages in PostgreSQL
I’ve designed a table to keep track of running processes: Where pauses is the number of times the process has haulted and power_levels is an array of integers from 0 to 4, with repetitions allowed, representing power levels that that process has been consuming. Using a single query I would like to selec…
PostgreSQL Generated Column from a JSONB column with nested values
I have following table with a JSONB column: CREATE TABLE movies ( contributors JSONB ); The data in the column looks like this: INSERT INTO movies (contributors) VALUES(‘[ {“last_name”: “…
Database table design for ‘availability’ within time ranges per day
I am working on a meeting scheduling web-app, where a person can share their availability for specific time ranges within the day. E.g. A user is available 3 time-ranges within the day, from 09:10 to 10:00, 13:00 to 14:00 and 16:30 to 17:15. This can go on for 6 days per week, from Monday to Saturday. What I …
How to list out management levels from Postgres table
I have a table with the following fields: Id, manager_id, and candidate_name The manager_id will point at id which allows me to reference the management chain. I want to generate an output like the …
PostgreSQL 11 – default timestamp column
I have a table like this. create table public.test123 (id int not null primary key, dt timestamp null default clock_timestamp()); I then insert into it from 2 different client sessions, the first …
Does join speed depend on total amount of records in the table?
If i have a table that looks like this (composite PK user_id + asset_type): |user_id|asset_type|amount| One user have at most 10 records in this table. Imagine if there 200 millions records and i …
PostgreSQL: Make data appear in different rows instead of same row
I have this query: SELECT SUM(CASE WHEN color = ‘blue’ THEN 1 ELSE 0 END) AS “Blue”, SUM(CASE WHEN color = ‘purple’ THEN 1 ELSE 0 END) AS “Purple”, SUM(CASE WHEN color = ‘yellow’ THEN 1 …