I am new to postgres, and I am using Visual Studio Code IDE (version 1.62) to write my scripts. While doing so, I am observing this strange behaviour where VS Code doesn’t highlight the data type smallserial (2 bytes). Here is what I see: But I know that the script is correct because this query runs suc…
Tag: postgresql
Extract JSON content in Metabase SQL query
Using: Django==2.2.24, Python=3.6, PostgreSQL is underlying DB Working with Django ORM, I can easily make all sort of queries, but I started using Metabase, and my SQL might be a bit rusty. The problem: I am trying to get a count of the items in a list, under a key in a dictionary, stored as a JSONField: Exam…
Get customer name from table in PostgreSQL
Using this query, we get partner details who have a test today. The customer name is present in another table res_partner: I have tried following code: But I got an error ERROR: column rs.id does not exist LINE 1: …t join (select partner_id from parking_test)pl on rs.id=pl.i… res_partner table: pa…
What does SELECT followed by a SQL command as a string mean
What does something like mean? I know usually you have the column name after the select, like SELECT first_name, but what does it mean when it is in single quotes? I believe the || means to concatenate the strings. Sorry, I’m new to this and couldn’t seem to find anything about this. EDIT: Sorry I…
How can I replace this correlated subquery within a function call?
Given the following tables buckets points And the following query Output How can I remove the correlated sub-query to improve the performance? Currently ~280,000 points * ~650 buckets = ~180,000,000 loops = very slow! Basically I want to remove the correlated sub-query and apply the width_bucket function only…
sql – Deletion in closure table with multiple same paths
I have the following hierarchical structure: Here is the closure table I’ve come up with: I want to remove the link between B and D, and therefore I want to delete the link between A and D (the one of depth 2). The problem is that I don’t want to delete the link between A and D of depth 3
Similiar UPDATE instruction in PostgreSQL
Is there an example of a 2-columns table, (x, y – INTEGER), which given instructions: will show different results? Answer Sure:
Issues creating a function
Thank you for reading my post and trying to assist. I am pretty new with PostgreSQL and am struggling with trying to create a Function to transform a table column that is currently integer (1,0) into ‘Yes’ or ‘No” and redisplaying the table after the change, what’s your recommend…
SQL DELETE – Only keep last n records in group (delete old records in group)
[Edit] : this answer helped DELETE FROM Table WHERE ‘RANK’ > 1 I have a table which keeps track of some entity’s status history (tb_status) DDL here is an example status_id ref_entity_aa ref_entity_ab status 1 “a” 1 OK 1 “a” 2 OK 1 “b” 1 OK 1 “b&#…
constraint c for table t does not exist on PostgreSQL even though it’s there
I’m trying to run an INSERT query on TablePlus. Instead of the query sending a success message, I’m getting an ERROR: constraint “minutes_clone_stick_tickers_unique” for table “minutes_clone” does not exist. Here is an image of my table structure. to replicate: I’ve t…