Tag: postgresql

How to convert fields to JSON in Postgresql

I have a table with the following schema (postgresql 14): message are only string, phrases. sentiment is a string, only one word classification are string but can have 1 to many word comma separated I would like to create a json field with these columns, like this: Also, if possible, is there a way to conside…

SQL Group by JSON arrays

json postgresql sql

So I have a JSON variable (var2), which is an array. I have the following data: I’m trying to write a query that gives back the number of dollars and the machines per group: Example: I’m not used to JSON arrays and it’s kinda tricky for me. I’ve tried many things and I can’t find…

Postgres : using computed variable in a SELECT statement part 1/2

postgresql sql

I’m having a SELECT statement as follow (doesn’t work): Is there a way to “inject” here the “variable” age_normin the query ? EDIT: Asked a similar question here, but this time with an additional column in the SELECT statement, which is the use case I’m trying to solv…

Insert aggreated data from one SQL table to another (Postgres)

postgresql sql

I have two SQL tables. This is Votes: and this is votes_aggregate: As you can see each entry in the votes table is a unique vote. I want to aggregate all the individual votes mapping to the same (catalog_item_id, listing_id) to a unique entry in the aggregate_votes table (2nd table). My Postgres skills are pr…

Executiung performant SQL queries equivalent to “nested deletes”

common-table-expression performance postgresql sql

Consider the following ERD for Order, Item and Article entities: I’d like to delete Orders satisfying a complex condition, and then delete Items associated to those Orders, and finally delete Articles associated with those Items. Cascade deleting from Orders to Items is possible as Order is a parent of …

Postgres: avoid listing all columns (“must appear in the GROUP BY clause or be used in an aggregate function”)

postgresql sql

This query works: but it’s very explicit and I’d like it simplified to: But this gives the error column “video_feed_unscored.title” must appear in the GROUP BY clause or be used in an aggregate function. Any other way to simplify the query? Answer Maybe not simplify but assuming, that …

How to create temporal table from current and history?

dbt postgresql sql

Let’s say I have a current table that looks like this: and I have a history table that looks as such: How can I build a temporal table such as the following in pure SQL? I am using a PostgresDB is dialect helps! I know I can do this in Python, I’m stuck on calling dynamically named columns using t…

creating a full text index in SQL database

full-text-search postgresql sql

I am searching for a database solution for real full text indexing. I have read Postgres’ full text search chapter but it describes text searching which is not a “full” index and it is heuristic in nature. However I found this https://pgpedia.info/f/fulltextindex.hml contrib/fulltextindex mo…

PostgreSQL: Select latest entries only when values differ in a column

date postgresql sql

Imagine the data: Per id, I would like to return the two latest entries of different dates (not just timestamp, the date part should be different): I assume I need to use partition and lag on the audit_id but I don’t know how to start structuring it. Answer I would attack this in two parts. The first wo…