Tag: postgresql

Using the function SPLIT_PART twice in PostgreSQL?

I have a TEXT column where each text is formatted as such: /customers/{customer_id}/views/{id1}~{id2}/ I am trying to fetch the id2 only. My idea is how to split the string by the / character first, where I will have: customers, {customer_id}, views, {id1}~{id2}. And then, get the last position: {id1}~{id2}. …

SQL Custom Function is not returning data as expected by matching to my conditions

date function postgresql sql

I am trying to create this function but null values fail But some of the conditions are not working, specially when I am passing null calls that are not giving expected results are: Expecting above to return (‘REVIEWED’, -2) while I am getting nothing Expecting above to return ( , -2) while I am g…

How to compose Django Model Filter for relation existence?

django postgresql python sql

There is a many-to-many relationship between A and B. There are 3 tables to represent that relationship. TableA, TableB, TableAB Now I have another table TableC where it has a foreign key to TableA, and I want to filter objects from TableC where it has a relationship with TableB. The following is high-level c…

How do I return a table from a function with a bespoke column name?

dynamic-sql plpgsql postgresql sql

This function works: But when I try to add some text to the column name: This just fails as a syntax error on $1. Or: select * from a(‘some prefix’) doesn’t work. Is there some other syntax that does the job? Answer That’s simply not possible. SQL does not allow dynamic column names. Y…

How to use Peewee to make CASE SQL queries?

peewee postgresql python sql

I have a table in postgres that holds a priority integer for each row. The priority needs to update whenever I add a new row or change the priority of an existing one for all of the other rows in the table. One way to do it in pure SQL would be with a CASE query: Question: How can I

Error: Returned type unknown at ordinal position 2, but query expects text

function postgresql sql

I created an SQL function in PostgreSQL that returns a table with text values, it is throwing error Function definition: Answer You could try casting the string literals explicitly to text using the double colon operator:

How to rotate a two-column table?

crosstab dynamic-sql pivot postgresql sql

This might be a novice question – I’m still learning. I’m on PostgreSQL 9.6 with the following query: My query returns the following dynamic rows: locales count en 10 fr 7 de 3 n additional locales (~300)… n-count I’m trying to rotate it so that locale values end up as columns with a s…

Nest Postgresql Sequelize one-to-one Error with ForeignKey

nest node.js postgresql sequelize.js sql

My Table Has one-to-one connection with table But when i try add some data in second table< i got an Error – Nest error insert or update on table “checkingResults” violates foreign key constraint “checkingResults_resultId_fkey” What happened? I try any thing… And how i r…

sql query that depends on each value of other table

postgresql sql

I’m not used to query sentences and this one is a bit tricky, I don’t know even if it is possible (at least with just a single sql query) or if I should change the question name to something more accurated, if so let me know. I have two tables, I will make them simple. The first one that is

SQL WHERE condition when one does not return true, then try other

postgresql sql

I have to query a table based on two fields such that if first field matches then don’t check the second but if first field does not match then check if second field matches for a value something like: But if first condition succeeds, it must not check for second condition Example: Suppose the following…