Tag: postgresql

non UNIQUE constraints

check-constraints constraints postgresql sql unique

So, I got in this situation: I create a table (let say model_a), in this table I have 2 cols (let say col1 and col2). I need a constraint that satisfy this reality: It’s not UNIQUE constraint, you can duplicate the first row. The only one time this constraint active is col1 is the same but col2 is diffe…

PostgreSQL UPDATE statement treats keyword as a column

postgresql sql

sorry for the noob question, just getting started with SQL and I have a problem that I could not resolve. Basically, I created a table with “date of birth” column and chose wrong TYPE (integer), so every time I am trying to enter date of birth as “1998-01-04” it is just showing 1998. S…

How to calculate agregate values by condition?

average postgresql sql

here my base example Try smth like this, but nothing Could you help me? Answer UPDATE: for these, I have added a new column to allow ordering of the rows. As the OP referred to day_name as a number in code, I have assumed that they refer to days – and thus changed the ‘id’ column to ‘d…

how to select max score from duplicate mobile_number

greatest-n-per-group postgresql sql sql-order-by

how can I filter record 60122839991 with the highest score 95.3758 and return as below without duplicate phone number Answer Use distinct on:

Eliminate double quotes from a json field in a selective manner in PostgreSQL

json postgresql sql string

I’m a new user of a PostgreSQL database with a json field which causes me some trouble. In fact, string values have been inserted into this json filed with double quote when they are supposed to be numerical values. I have a field named “test_json” which is composed of for example these 2 ro…

Does index help a sql select sorting performance?

postgresql sql

I have a SQL statement like this: I think the benefit for indexing ‘createdAt’ column is quite minimal, since it does the select first then sort 3 rows. Am I correct? Or it’s better to add indexing? Answer There are two possible indexing strategies for the query you show: Index the IN condit…

recursively creating multiple rows for every date between two dates in one row

common-table-expression datetime postgresql recursive-query sql

I have staff holidays (rdos) stored in a single row, I am very close to recursively expanding them to show a full row for each day of leave. Running this on Postgres Database version 10.10 Data Set: My query currently: I am getting duplicate regressions and unwanted cross matches. Any ideas? I have seen simil…