I am working on a project where I want to fetch from my postgres database all the auctions where a particular user has not submitted any bid for the auctions. I am trying to write the query for this. The schema is as follows – Auction Table auction_id (PK) auction_data A1 a1_data A2 a2_data A3 a3_data B…
Tag: postgresql
Postgres: Update jsonb from other table column which is encrypted
I am migrating a data from an encrypted jsonb column (app_id field) in a table to another jsonb column in a different table. As said: origin table column is encrypted. Destination one is not. Right now I have the following query: Error response is: SQL Error [42804]: ERROR: could not determine polymorphic typ…
SQL – trimming values before bracket
I have a column of values where some values contain brackets with text which I would like to remove. This is an example of what I have and what I want: I have only come across approaches that use the number of characters or the position to trim the string, but these values have varying lengths. One way I was
Strange behavior from lag(), skipping over certain rows
I have a table we’ll call service, toy version is: The last five system fields are for database bookkeeping, and to enable soft (front end) deletion. There’s another table service_log which, together with service allows us to view a full revision history for each record in service. Fields of servi…
Update query with join and where clause
I’m currently confused with a (fairly easy) update statement that I’m trying to execute on a table. The two tables are as such: Customer table has the columns Loan table has the columns I would like to update the passworddisabled attribute to true if they are registered via a specific cash registe…
Postgresql update column based on set of values from another table
Dummy data to illustrate my problem: So the data looks like: Table 1: category_id unit is_valid 1 a true 2 z true Table 2: category_id unit 1 a 1 b 1 c 2 d 2 e I want to update the is_valid column in Table 1, if the category_id/unit combination from Table 1 doesn’t match any of the rows in
Where clause if true would not show at all
Have table where are two columns – client_id, content every client have +- 50 content rows. In WHERE i have this clause – where content NOT IN (‘2′,’3′,’4’) In result shows same clients but without rows where are ‘2’,’3′,’4′. …
Lower bound for Postgres integer type out of range?
Per Postgres documentation, an integer type is defined between -2147483648 and +2147483647. I thought that these boundaries were inclusive, but if I try: an Integer out of range error is raised. The upper bound seems to be casted properly: And if I increase the lower bound by one it works fine as well: The sa…
Sequelize: Avoid Race Condition When Updating Field with Unique Value?
I have a field in my postgres db called authorizedUserNumber. This field is set by default to 0, and does not auto-increment, because it is only asigned when a user has been fully onboarded. Okay, so let’s say a new user has been fully onboarded and I want to assign a unique number to the field authoriz…
How do I pivot values from rows into column names in Postgres?
I have a query in Postgres. It’s working well and returns exactly the rows I’m looking for. Here’s the query: This is what it returns: id status timestamp 1 started 2022-01-15 05:12:36 1 paused 2022-04-14 09:12:50 1 killed 2022-04-27 13:12:48 How can I pivot this table to return this exactly…