I have a use case where I need to: Update a row. Persist the changes to disk, such that a system crash will not reverse it. Update the same row that has not been modified by another transaction (a …
Tag: postgresql
Postgresql:Update text column with value from another column
I’m new to postgresql and need help with an update script where I can insert the corresponding ID between path2 and the filename if there is none.This will be done on multiple records. After update only IDs 111 and 333 will be affected and the expected is /path1/path2/111/file1.png /path1/path2/333/file…
Why PSQL create table returns [42P01] ERROR: relation does not exist
I’m new to SQL. I was trying to run sql schema, here is a part of code It returns[2020-07-03 15:28:19] [42P01] ERROR: relation “people” does not exist In fact all foreign key reference table reference not exist. When i remove the reference, the table can be created, can someone please help m…
Postgresql – create table with disjoint subclasses
I’m unsure how one can create a table on postgresql with disjoint subclasses. I have represented (a very simplified version of) my problem below in an ER diagram, showing the two subclasses and the attributes of each subclass. For the columns common to all rows (id, common1, common2), it’s clearly…
Logic to use in place of Aggregate function the WHERE clause
I am computing the frequency of values over a large table. since the result of the aggregate functions result in zero for some rows, I would like to simply skip such rows. I could have used NULLIF(..) but I do not want to return a zero value. And aggregate functions aren’t allowed in the WHERE clause. H…
How to use triple INNER JOIN with one empty table?
Given is the following table: A project can be either owned by a user (user_id), OR by an organization (org_id) So either field is 0. So I do an INNER JOIN on projects, users, and organizations, but if the project is owned by a user (and currently no organization does exist at all), the result is empty. Does …
How to return several columns for subquery added to select?
I have a query like this: select e.field1, e.field2, (select count(field3) from tbl1 where someField = e.field1 group By someType ) as count_1, (select count(field4) from tbl1 where …
How to insert and delete from and to the same table in the same query?
Is there any way to select something from table A then delete & insert those records from/to table B (kind of updating B with values from A)? Let’s say I have two following tables: I came up with this: This is working expect when it fails for “duplicate key value” error. However I though…
Order SQL results where each record referencing another record on the same table comes after the referenced record
I have the following data: Where parent_id is a reference to the SAME table. I need to sort these columns so that each record is after its parent (not necessarily immediately after). So I would expect this result: I’m guessing there is no clean efficient way to do this without any significant schema cha…
How do I do this without a subquery?
I need to make the same query, but without a subquery! I need to get the count of rows in which genre_id is equal to the :deletedGenreId and the org_type_id value of this line in this table is not unique Answer There is nothing wrong with sub-query. Sub-query actually can help boost performance and increase c…