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 consider the classification this way:
Tag: postgresql
SQL Group by JSON arrays
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 much documentation with examples for
Postgres : using computed variable in a SELECT statement part 1/2
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 solve Answer We can calculate the age_norm column in a subquery and then
Insert aggreated data from one SQL table to another (Postgres)
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 pretty weak so I’m struggling to
Executiung performant SQL queries equivalent to “nested deletes”
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 Item. But cascade deleting from Item to Article isn’t possible as
Postgres: avoid listing all columns (“must appear in the GROUP BY clause or be used in an aggregate function”)
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 there is a lot of feeds, and user see only few of
How to create temporal table from current and history?
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 the
creating a full text index in SQL database
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 module which sound promising. So my questions are as follows. why was it removed in PostgreSQL 8.1?
PostgreSQL: Select latest entries only when values differ in a column
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 would make sure
PostgreSQL: Create array by grouping values of the same id
Given the following input data: id category 1 A 1 B 2 A 2 R 2 C 3 Z I aim aiming to get the following output table: id categories 1 {“A”,”B”} 2 {“A”,”R”,”C”} 3 {“Z”} using the following query: But what I get is the following table: id categories 1 {“A”,”B”,”R”,”C”,”Z”} 2 {“A”,”B”,”R”,”C”,”Z”} 3 {“A”,”B”,”R”,”C”,”Z”} How can I obtain