I am using a PostgreSQL table that has 800+ columns. I need to determine which columns have 100% population. I have two fields that I know are fully populated but I need to determine if there are others. I can…
Using PostgreSQL 14 I have a table 'fruits' with a JSONB column named 'items' containing this sample data: [{"anz": 4, "typ": "Banana"}, {"anz": 5, "typ": "Apple"}, {"anz": 2, "typ": "Grapefruit"}] [{"anz": 1, "typ": "Banana"}, {"anz": 1, "typ": "Apple"}, {"anz": 3,…
I have this simply query that returns 100 rows, each row is for 1 course: select c.* from courses c join schools s ON s.id = c.school_id How can I get the # of students who's favorited the course, if…
PostgreSQL has SETOF <sometype> type according to the doc and SETOF <sometype> type is used in a function as shown below: -- ↓ ↓ ↓ Here ↓ ↓ ↓ CREATE FUNCTION my_func() RETURNS SETOF <sometype> ... Now, I want to…
I've the following values in my Table1 : Date Time Id Value Unit 2024-01-01 06:00:10 Key_001_C 1557 kWh 2024-01-01 06:00:01 Key_002_01_C 10323 kWh 2024-01-01 06:00:01 Key_002_02_C 8405 kWh 2024-01-01 06:00:01 Key_010_C 122 kWh 2024-01-01 06:00:08 Key_101_01_C 6591 kWh 2024-01-01 06:00:01…
Lets say I have a model Document. Document document_id -> int client_id -> int I want the document id to be auto incremented based on the client id avoiding a race condition. I have an unique constraint on document_id, client_id.…
I have a table: CREATE TABLE t_table ( c_id int4 NOT NULL, c_date_called int4 NOT NULL, CONSTRAINT t_table_un UNIQUE (c_id, c_date_called) ); that stores distinct snapshots of data, with data as such: INSERT INTO t_table (c_id, c_date_called) VALUES (1,9), (2,9),…
I wrote two queries and thought they would give the same result, but it didn't happen. Could you please explain to me why? 1) SELECT date, COUNT(DISTINCT user_id) FROM (SELECT user_id, date(time) as date, COUNT(order_id) FROM user_actions WHERE order_id NOT…
I need to fetch the string For reference 110_112_120_122_130_132_140_143_149_151_154_160_162_165_171_173_194_215_236_257_278_299 use this as a subjects I am using the below query select * from table_name where subjects like '%_21_%' ; which is not showing desired result. Note - 21 can come…
Lots of similar questions on this but have not come across an answer for an "Azure Database for PostgreSQL flexible server" set up with Private Access (VNet Integration). The Postgresql DB is used by an Azure Web App that queries…