Tag: postgresql

COUNT Multiple Columns using GROUP BY – SQL

I am trying to create a GROUP BY COUNT of ~30 columns in my database. The database is basically a shiftplan, where on each column a row can be assigned different shift types (denoted as a D, N, A, X, F, C, etc..). I can use the following query to get a count of each shift type, am struggling to

‘cannot extract element from a scalar’ when order by id is DESC

json postgresql postgresql-9.3 sql where-clause

I have a PostgreSQL 9.3.10 database, a table with 15842 records and use Intellij for making queries. I want to query some recent data from JSON column (named ‘data’). My query is that simple: The problem is that I get: [22023] ERROR: cannot extract element from a scalar when I use desc order. When I use asc the query does

How to filter within a Window Function PostgreSQL

postgresql sql window-functions

I only want to filter in Window function to keep the created_at is null so I don’t use WHERE clause previously. Because when I order by created_at desc it will show the null value first. How to add a …

Select statement using Group-by while ignoring column on Postgresql

postgresql sql

I have a table name “report” in Postgresql db like: Student Class Marks Observation_time A 1 11 21/7/2020 A 2 13 18/7/2020 B 1 19 17/7/2020 A …

Optimizing GROUP BY + COUNT DISTINCT on unnested jsonb column

json postgresql postgresql-performance query-optimization sql

I am trying to optimize a query in Postgres, without success. Here is my table: I have indexes on id and meta columns: There is 62k rows in this table. The request I’m trying to optimize is this one: In this query, meta is a dict like this one: I want to get the full list of key / value

Why is the current time constant when executing for every row (using a LATERAL JOIN)?

current-time postgresql sql

Why is it the case that I can see the same timestamp when wrapping a now() function within a lateral join. I formulated a code sample to illustrate by example: Results in the same timestamp, for example (trimmed): I would have expected that each row contains a new timestamp (in increasing order) as LATERAL is executed on a row-by-row basis.

Sql Database design hierarchy

database-design hierarchical-data postgresql sql

I have been tasked to create an app to track our contracts. I am planing to use Django and PostgreSQL. Our existing data is in Excel with 52 rows. I have found an hierarchy pattern in the file which contains as below Holding Office Registered Office Operating Office For example – Holding office (company) can contain one more Registered Office

Truncate if exists in psql function and call function

plpgsql postgresql search-path sql truncate

I have the following code to create a function that truncates all rows from the table web_channel2 if the table is not empty: create or replace function truncate_if_exists(tablename text) returns void …