Currently I'm trying to develop a PostgreSQL database schema, it has only 2 tables, one of which contains usernames. The difficulty is that for certain reasons I cannot store the username directly, so I have to store the hash (SHA256).…
In PostgreSQL, I want to run two tests: if there is a table in a specific schema which is not in the required_tables table if there is a table in the required_tables table which is not in the specific schema…
What is the syntax to get the epoch from a timestamp column with time zone. I can use with time zone in the following way and it works as expected select extract(epoch from timestamp with time zone '1970-01-01 00:00:00.000 +00')…
In my postgres database I have a table I'll call users with a timestamptz type column updated_time. When I execute a select statement on this field I will get something like this - '2024-07-01 12:30:30.821 -0500'. However, if I turn…
I have an ASP.NET Web API project, and have implemented repository and unitOfWork patterns. I currently have a big SQL query that I want to implement in code, it includes a lot of tables and joins. My current solution is…
i need to get rows counts for specific columns with null and insert the same rows count output into a table with result set of the query. For this requirement, i tried with below pl/sql code block in postgres but…
I am dealing with a PostgreSQL (v14) query of this kind SELECT ..., EXISTS ( SELECT 1 FROM table2 t2 WHERE t2.fk = t1.id AND LOWER(t2.table_name) = 't1' ) AS t2_record_exists FROM table1 t1; and was hoping to extract the…
When I run the below query it executes correctly. But when called as a procedure throws an error Exception assignment source returned 2 columns 42601 From the analysis I found it's coming from here. ( SELECT string_agg(BB.File_N, ',' ORDER BY…
I have two tables. table 1 Column A Column B rgz 1 dry 1 vgy 2 chy 3 pom 2 table 2 Column C Column D tuv 10 tuv 11 chx 22 lmn 34 I want the following result shown…
For reasons outside of my control I am trying to build a graph-like data structure in PostgreSQL. The business requirement is that everytime a node changes, its edges need to be re-computed. Also, there is the possibility of stale reads…