I have table in which data is like this Mapping Relationship 1 task_id is mapped to multiple task_detail_id (1 to Many) Task_detail_id Task_id Creation_date 1 20 2020-05-…
Tag: postgresql
Get all Many:Many relationships from reference/join-table
I am having difficulty querying for possible relationships in a Many:Many scenario. I present my schema: What I do know how to query with this schema is: All Bands that a given User belongs to. All …
SLOW running query in MS access
What causing this query to execute long time. Is sub query block creating a mess? Answer I believe that the problem is that for every record of the INNER JOIN you are looping over all the records of the WD table.
Subquery with WHERE clause => Column does not exist
I try to get the top 3 length per film for each actor. My query looks my this. SELECT fullname, ROW_NUMBER() OVER (PARTITION BY fullname ORDER BY length DESC) as f_rank, length FROM (…
Multithreading for queries in SQL Database
Suppose I have a database, for example, for messenger. My app is collecting messages and stores them in my database. It doesn’t matter what type of database it is (i want to know how it is usually embodied and does my database support it, and are there other databases that do if it doesn’t), but i…
Set ALL Values of PostgreSQL column to X
I have a PostgreSQL database table with same sample data whose schema is shown below. | UserId | XP | Level | ———————– | 123456 | 55 | 12 | | 134156 | 45 | 4 | | 526083 | 39 | 5 …
SELECT value depending on underlying table name
I want to set the value of a column based on the table being queried. Example: Let’s assume two tables: Transportation and Locations. And a SELECT statement with a column named type which shall hold …
Two foreign keys, only one can be not null at a time
I have a database table with two foreign keys pointing to two different tables. The business logic requires an “either or” relationship; only one foreign key can be NOT NULL at any given time. Here are the four possible states the foreign keys can hold based on the business logic: NULL, NULL ̵…
psycopg2 not escaping quotation from tuple arguments? Using sql.SQL to format arguments
I’m trying to dynamically create a table in postgres with psycopg2. It seems when the arguments are passed, pyscopg2 is not escaping quotations and understanding my query. Code looks like this: The error I’m getting: What print(create_table_str.as_string(conn)) outputs: Edit to show modified answe…
Avoid clashes between table name and keywords psql
I need to create a table in postgreSQL like the following but “USER” is a keyword and i am getting an error. Btw shouldn’t it work anyways since I am writing “User” instead of “USER”? Answer First, don’t use reserved words for identifiers. It is simple enough to…