In my app customers can build up credit in different categories and then spend it on things the relevant table looks like this: There is one entry for every category in which a user has credit There are 2 constraints, one to make sure the amount is never negative and there is only one user category connection…
Tag: postgresql
Create a function that accepts a string and returns multiple rows
I’m being required to create a function that transforms a single column’s value based on the user’s input. I need some help on the syntax for doing so. Here is the query I’m currently performing to get the rows: some pseudocode on what I’m trying to do: I’ve been trying to …
How to check for column attributes in postgreSQL?
I am new to SQL. Let say that when someone created a table like this in database named test1 of PostgreSQL: I make a query to the database and do not know how data in columns are stored. Is there a query to return the stored definition of the columns in the users table or a method to check this
Postgresql conditional script
I need to update the column (vendor_type) in the table depending on values in other columns. Table name = “vendor” Columns = “client_id”(varchar), “coach_id”(varchar), “exm_vendor”(boolean), “vendor_type”(varchar) And this is what I want to do with p…
Problem with PostgreSQL function parameter of type “name”
I’m writing some PostgreSQL functions to compile a list of search terms for records in a table called name. Since the search terms come from multiple columns on the name table and from multiple columns on other tables, a simple generated column isn’t sufficient. Here’s a simplified version o…
PostgreSQL join table on json property and get oldest first from result with nulls first
I got 2 tables; domains and events. Im trying to create a query that returns a list of distinct domains that is ordered by the oldest events (for that domain) with nulls first and distinct domain. Basically this query will do the job: But the output is not distinct on ‘domain’. When using ‘d…
Obtain Name Column Based on Value
I have a table that calculates the number of associated records that fit a criteria for each parent record. See example below: note – morning, afternoon and evening are only weekdays What I am trying to achieve is to determine which columns have the lowest value and get their column name as such: Here i…
Postgresql OVER
I have data like: id user index 1 aaa 0 2 bbb 0 3 aaa 1 4 bbb 1 5 aaa 2 6 ccc 0 How to get only the latest index of each user ? Like this result id user index 4 bbb 1 5 aaa 2 6 ccc 0 Answer Looks like a simple DISTINCT ON: This will
PostgreSQL – Does a ‘RETURNING ELSE’ statement exist for an UPDATE?
I have this query: When the update is successful, it returns the following: client_id username isSuccess 1 test_name 1 When the update doesn’t execute, it returns client_id, username, and isSuccess, but their values are blank. What I’m having trouble with is customizing what returns when NO update…
how to insert pandas dataframe into IN operator of SQL
I have pandas dataframe with unique number of user: I want to pass this column to sql query where I use IN operator: I have tried doing this which would retrun whith this ‘1qw3,2wed,3das,4frr,533ew,612w’ and then something like WHERE users in STRING_SPLIT(data_frame, ‘,’) but this one …