Tag: postgresql

Postgresql, one function calls another function and get 2 out values to caller

Postgresql 12. Want to call function testA() from another function testB(), and assign two “out” values to the 2 local variables. Don’t know how to do the assignment, please help. create or …

Postgresql – Select in Select Alias Problem

arrays postgresql postgresql-12 sql subquery

I have query. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d SELECT users.id as user_ids, (SELECT ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses….

How can I get the division of two group by on SQL?

join postgresql sql

I’ve seen several posts to get the percentage of each row (using partition by), others about getting one row divided by another (using inner join with the same table), but I haven’t seem this problem, …

How to use nextval() to insert a row containing its own ID in a string

auto-increment create-table postgresql sql sql-insert

I have a table that contains a column with a string containing its own ID. How can I insert a new line using a single SQL statement? I need something like https://www.db-fiddle.com/f/3NwLNBirN7mHKpDk9NyHSy/1 Answer One option is to select the next serial in a subquery first: You could also use a computed colu…

Efficient way to handle below query? in SQLServer or PostgreSql (Self join)

postgresql self-join sql sql-server

I have an employee table as below I need to select employee_id, manager_id, result. result should have true or false depends on below conditions. If the employee is manager for someone then true If the employee has a manager then true I came up with a query, but I need to know if there are any other better wa…

PostgreSQL. constraint allowing only one of many possible values

postgresql sql unique-constraint unique-index where-clause

I have statuses like this: started,calculated,finished I need a constraint allowing only one NOT finished status in a table. This is allowed: this is forbidden due to two not finished statuses: Answer You can use a filtering unique index: The trick is to pass a fixed value instead of a column name to the on c…

Calling stored function in postgres 12 with json type argument by SQL query from pgAdmin results in error

json plv8 postgresql sql

I’m trying to call a stored function in postgres 12 DB which takes 1 parameter of json type and returns result of json type. The function is like this: The function must take a json with keys: “id” – for id of request sender, “token” – for it’s secret and &#8220…

Create and Run Query in Postgresql Procedure

postgresql sql stored-procedures

I want to create a postgresql procedure which creates a query and run it. I tried and research on forums. But i can not solve my problem. Procedure should get user input as a parameter and use it in query. My code like that : And this is the error : edit: form_field is a table. create statement : Answer

Psql query : Union is not preserving the order of rows coming from cte

common-table-expression postgresql sql sql-order-by union

I have this query: cte1 is ordered by salary column but cte2 is not. I want the results of cte2 to get appended to the results of cte1 while preserving the order of results from cte1. But it’s not happening. If I run the above query without union with the second cte, the results are displayed in the exp…

Select from cross tables in postgres problem

left-join postgresql sql subquery where-clause

Lets imagine i have a table A that contains rows X,Y and Z, and i have another table that relate elements from A to another table B. A_B : element_A, element_B, and i want a query that for element b in B returns for all elements a in A return True if {a, b} exist in the table A_B and