I have a table of email addresses: How can I find and replace the email format so example.person@gmail.com -> example.person_gmailcom@test.com? E.g: Results in example.person@test.comgmail.com Playground here: https://dbfiddle.uk/GnIfomiO Answer This is probably most simply done by splitting the email addr…
Tag: postgresql
How to split an XML string into parts with different keys
from XML column I wanna try get info. Every client have more that one contract_key in XML, im using split_part and strpos for get needed information : But problem is this function return the first match found: ID Key amountR AVGamount 2253545 1 10000 50 this client in same XML have more Contract_key: ID Key a…
SQL with grouping sets returns the initial table
I have a sample table like this: name manager country position salary Mike Mark USA Content Writer 40000 Kate Mark France SEO Specialist 12000 John Caroline USA Outreach Expert 32000 Alice Caroline Italy SEO Specialist 50000 Philip Caroline Italy Marketing Manager 30000 Julia Caroline Italy SEO Specialist 440…
Prisma PostgreSQL queryRaw error code 42P01 table does not exist
I am trying to run a query that searches items in the Item table by how similar their title and description are to a value, the query is the following: However when the code is run I receive the following error: I have run also the following query: Which correctly shows that the Item table exists! Where is th…
Postgres: column “all_comments.id” must appear in the GROUP BY clause or be used in an aggregate function
I am using with recursive to count the number of all descendants. That would be the following SQL query, but if I specify only root_id for group by, I get I get the following error. column “all_comments.id” must appear in the GROUP BY clause or be used in an aggregate function I don’t unders…
Copy data on parent and child table
I have two database tables. What I need to do is to copy specific data from one storage to another, but also keep the mapping to the photos. First part I can do easily writing but after that newly inserted items does not have photos. Note, that document_id field is unique for not copied items. Answer Assuming…
PostgreSQL: Return gaps and value mismatches between columns
Imagine the following data: I am interested in returning the entries where there is a gap in the data for a particular student, where the category changes from one year to another, or both. So the output that I’m after is: The reason why id 4 would not be returned is because there is actually no gap or …
Output of non-existent values when grouping in sql
For example, i have a table with the data: Screenshot This table named “table”. I have the SQL query: And I get the result: Question: how do I write a query that would return all the values that are in the kind field (or any other field that can be in group by)? Even if this value is 0. For
What’s the best way to “append” to a list of strings stored in a JSONB column in a table PostgreSQL such that duplicates are not stored
Say there is a table employee One of the rows has the following information in the tags column Currently to update the column with additional values I perform the following query However this adds duplicates to the cell and makes it look like which is less than desirable for my use case. Currently to circumve…
SQL select column group by where the ratio of a value is 1
I am using PSQL. I have a table with a few columns, one column is event that can have 4 different values – X1, X2, Y1, Y2. I have another column that is the name of the service and I want to group by using this column. My goal is to make a query that take an event and verify