Note: Asking to improve the working code. My data column is as follows In the above data, I want to set available to false and price to zero. To do this I Am using the below code. My question is it possible to update both values in a single query? Thanks. Answer Sure:
Tag: postgresql
Calculating totals and percentages for each row, in a time boxed window, for a relation
Ok, so I’ve got two tables: jobs, and job runs. I’m using Postgres. I want to look at 2 periods. 7 days ago until now, and 14 days ago to 7 days ago. For each job, I want a total of the number of runs, and a percentage of successful and unsuccessful runs for each period. I’ve cooked up this
Multi-column IN/ANY postgres query
Sample table schema: Requirement: Fetch rows that match any of the tuples (col1, col2) presented in an array. SQL: I want to write this query in Go using the “database/sql” package with “github.com/lib/pq” driver, and that is where I am facing the problem. I can easily do this for sing…
Changing a column value to several records in a consecutive fashion using clever statements in SQL
I have a Postgres table that has an order view field in her, I’m using Nodejs with express This order view is of type INTEGER and has the constraints of NOT NULL and UNIQUE Inserting a new record may bring problems with that, because, if the existing records have view orders of, say, [1, 2, 3, 4, 5], an…
How to insert result of select query (with default if return no rows) as part of Postgres Function?
For my Nextjs website, I am configuring the backend to automatically add user information to a public.profiles table whenever a row is inserted into the auth.users table (automatically done through Supabase’s authentication). I have a public.college_emails table that contains the names of over 1700 coll…
PostgreSQL dynamic query: find most recent timestamp of several unrelated tables
I have a number of tables, and many of them have a timestamp column. I can get a list of every table with a timestamp column: I can get the hightest timestamp in the sharks table as follows I would like to get a table like I’m suspecting this requires dynamic SQL, so I’m trying something like But …
How to look for a specific thing within a table with postgres/nodejs
I’m currently using postgres, accessing it through a node server. I’m feeling stuck on how I would search for a specific term within a table in order to make a change. I know I can SELECT certain items, but I don’t know how to search within the items. say my table is: animal cuddly scary Med…
Multi-column Index on a filter & sort by query
I am executing an query of type: Query: Table: entity also has an index (Btree) on it as such – Index: But it seems that this type of index is of no use on the above query. Can anyone suggest what kind of index would better serve this type of use-case? Some Additional Information – The Table is qu…
How to select records from a Postgres table using an ORDER BY statement on a jsonb column that points to another table
I have two tables, characteristics and students. characteristics students The characteristics table has three columns where the id column is an auto-increment field. The name denotes the name of a specific characteristic and the value indicates the respective information about the characteristic. The students…
I can not convert the data to the required form
Hi hi I have a Nodejs server and I am using PostgreSQL, the bottom part of my database. How can I select data and represent it like this (in one sql query): I wrote a request but it is far from what I need: Answer You can use a cte to first build the question JSON data, and then aggregate