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 Bids Table bid_id (PK)
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 type because input has type unknown From here
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 service_log are the same as service, plus a few other fields
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 register. I’ve made use of the distinct command because
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′. I need if found one client with content ‘2’,’3′,’4′ dont show this client completely Answer NOT IN only removes the rows
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 same result is applied to smallint.
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 authorizedUserNumber. In the event
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: id started paused killed 1 2022-01-15 05:12:36 2022-04-14 09:12:50 2022-04-27