I am trying to get a report to run through crystal using our postgres databse. My SQL looks like SELECT slcnote.note_date, slcnote.account, customer.name, slcnote.reference, …
Tag: postgresql
PostgreSQL – Subtraction based on column data simplifying query
I’m trying to subtract data from the table based on another column which represent their category. For example: +—-+——–+———-+ | ID | Amount | Category | +—-+——–+———-+ | 1 |…
Update jsonb column with value from row_to_json()
I have a table containing a data that look like: col1 col2 col3 col4 json_data —————————————————- a b c d {“mock”:”…
Change empty string to NULL when column has DATE constraint
This might be impossible but I was wondering if someone more experienced knew if this is possible to do in postgresql. I have a column in my create statement CREATE table IF NOT EXISTS (other cols, …
Get totals from difference between rows
I have a table, with the following structure: ( id SERIAL PRIMARY KEY, user_id integer NOT NULL REFERENCES user(id) ON UPDATE CASCADE, status text NOT NULL, created_at timestamp with …
How do I compare two SQL queries to run on Postgres
I need to compare two queries that will run in my Postgres database. How do I know the execution time and any other statistics of them so I can produce a reliable benchmark between them? Answer I can think of two interesting data points to collect and compare: The execution time. For that, simply execute the query using psql connected
PostgreSQL – Help comparing two tables against three specific columns
I need to match three columns across two tables. A query should select a row in t1 and search for any row in t2 where ALL three columns listed below match. tbl_staged_documentation (t1 for reference)…
Race Condition between SELECT and INSERT for multiple columns
Note: This is a question which is a follow up of this solution. You need to read the link to get context for this question. Also, this is for postgres v9.4 If we want to return multiple columns now …
Displaying one row with one timestamp and a corresponding x-integer array as x rows with x timestamps and one corresponding integer
Apologies for the poorly worded question, I don’t know how to succinctly describe my problem. I am working with data from a sensor that scans a room every 10 minutes to see how many people are in it. …
Best way to count occurrences in a table?
I have a table with some key values occurring in one or more columns. Each value can occur zero or more times in each row. How do I efficiently calculate number of occurrences of each value in the …