I have written a stored procedure that basically loops over an array of fields and performs some manipulation in the db for each iteration. What I want to achieve is, either all the iterations of loops should occur or neither one of them should occur. So let’s say there were 5 elements in the fields arr…
Tag: postgresql
PostgreSQL- Round REAL data type (yes, I know numeric exist)
I know REAL data type is not accurate and normally for currency I should use numeric data type. But, I’m asked to do some stuff and one of the conditions is that the data type is real. When I try to do round((….),2) for example, I get that round function does not exist for this data type. My quest…
PGSQL query to get all records of a column containing line breaks
What is the shortest Pgsql query to get all records that contain a line-break in a given column ? Answer You can try something like this
Full text search failure on PostgreSQL
I have a PostgreSQL used to index text content. The SearchVector column is created successfully using the following code The content looks like the following: But if I try to run a query to get plurals or singular of manual (in Italian: manuale is one, manuali are 2 or more) it fails: return nothing return no…
PostgreSQL -Assign Unique ID to each groups of records, based on a condition
I am trying to group records of a table, based on a condition, and then asing in a new column a simple integer ID for each group obtained from where the condition is met. ID TMSTP CAT_TYPE TELEGRAMMZAEHLER 1 2022-05-03 20:52:02 32 5004 2 2022-05-03 20:51:34 32 5002 3 2022-05-03 20:51:34 32 5001 4 2022-05-03 2…
Generate random pairs SQL
Suppose we have these two tables. TABLE1: TABLE_2: I want to pair all rows of TABLE_1 with some random columns from TABLE_2 where each pair is gonna have a random amount of distinct rows from TABLE_2 (range 1,2,3) An output could be: Answer did the thing for me.
Filtering in count function – Postgresql
I have the following query which gives me the amount of referrals users have. However, I would like to only count the referral if the referred user has activated the premium plan. How could this be achieved? Dbfiddle here. I am using PostgreSQL version 14. Answer You can try to use FILTER clause or condition …
Distance between two integer values
is there a simple method to calculate the distance between two values e.g. 10 and 30 which will result to 20. Currently I do it as follows: Answer The Absolute value function does the trick.
How to determine if lists have at least one same element
Product table contains tags columns: tag columns contain tags separated by semicolon like How to find all rows where tag1 and tag2 columns contain at least one same tag ? For example row should be in result since both columns contain AC Row Should not be in result since all tags are different. Using PostgreSQ…
How to update multiple columns on PostgreSQL with values from another table
I have the following update statement: but.. what if I have more values that need to be updated? Do I need to: This looks expensive. Couldn’t find any other way though. Answer One way is to update both columns in a single expression: