36 votes
Accepted

'Column reference is ambiguous' when upserting element into table

From the docs, conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in ...
Evan Carroll's user avatar
  • 62.7k
10 votes
Accepted

Postgres not returning data on array_agg query as below

A LEFT JOIN can solve it, like Laurenz provided. But I suggest an ARRAY constructor in a LATERAL subquery instead: SELECT l.name, b.book_list FROM library l CROSS JOIN LATERAL ( SELECT ARRAY( ...
Erwin Brandstetter's user avatar
10 votes

Why does SELECTing sum() return null instead of 0 when there are no matching records?

This is specified by the SQL standard in section 4.16.4 Aggregate functions: : If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the null ...
mustaccio's user avatar
  • 25.6k
6 votes

'Column reference is ambiguous' when upserting element into table

This answer helped me solve a slightly different ambiguous column problem. I have a table where we do daily roll-ups into the same table multiple times per day. We need to re-calculate the daily roll-...
rgacote's user avatar
  • 161
6 votes
Accepted

Create index for WHERE COALESCE() condition

Strip the incorrect pair of parentheses: CREATE INDEX idx_test_c ON test_c(COALESCE(updt_dttm, load_dttm)); db<>fiddle here The way you had it was effectively indexing the composite value (...
Erwin Brandstetter's user avatar
5 votes

MySQL Coalesce date missing results, why?

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.
5 votes
Accepted

Why does COALESCE function not work for this query?

Answer to question COALESCE would be utterly pointless where you placed it. The aggregate function count() never returns NULL. And logic dictates that num_60_79 can never be lower than 1 in the ...
Erwin Brandstetter's user avatar
5 votes

Pick the most non-default value from a selection of values

I'm not sure that your MCVE fully represents the problem that you have, but I'll answer the question as given. This question is about performance, so having just a few rows in the table won't cut it. ...
Joe Obbish's user avatar
  • 32.1k
5 votes
Accepted

How to display queries of separate tables in adjacent columns?

You are basically building two tables, which then has to be joined on the date column. In your query you miss the join condition. One clear way of solving this is building the two result sets as ...
András Váczi's user avatar
5 votes

Postgres not returning data on array_agg query as below

You need a left join: ... FROM library LEFT JOIN books ON library.id = books.library Then you will also get libraries with no books.
Laurenz Albe's user avatar
  • 50.3k
4 votes

How to display queries of separate tables in adjacent columns?

Using @dezso's solution, which is the accepted answer above, here is the amended query and result for reference. WITH pc AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as ...
Alex Willison's user avatar
4 votes
Accepted

Ignore the joins without any result in SQL Server

I've been reading and re-reading the original question. What I have come to is this: Return records from tblProjecten where matches exist for parameters that have been supplied (are not null). ...
Kent Chenery's user avatar
4 votes
Accepted

Default values for crosstab results

More importantly, if ... some of the results have no entries You need crosstab(text, text) - the variant with 2 input parameters instead of crosstab(text) you are using now to avoid incorrect ...
Erwin Brandstetter's user avatar
3 votes
Accepted

Merging continous date range using T-SQL

Assuming hours are added to table in chronological order. If not, sort them before inserting them! First we need to relate each record to successive records. You say that is done by matching their ...
Rob M's user avatar
  • 146
3 votes

Pick the most non-default value from a selection of values

Given an index exactly like (note Fee is the only included column): CREATE INDEX [IX dbo.FeeTest ClientId, Val, Val2 (Fee)] ON dbo.FeeTest ( ClientId, Val, Val2 ) INCLUDE ( Fee ); ...
Paul White's user avatar
  • 83.7k
3 votes
Accepted

Outer join on a view with calculated columns performs calculation for missing ("outer") records

If the two queries: SELECT * FROM foo f LEFT JOIN baz b ON f.x = b.y; and SELECT * FROM foo f LEFT JOIN (SELECT * FROM baz) b ON f.x = b.y; return different results, then it's a ...
ypercubeᵀᴹ's user avatar
3 votes

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

The actual standard says that all of the WHEN clauses (as well as the ELSE clause) have to be parsed to determine the data type of the expression as a whole. I'd really have to get out some of my old ...
Joe Celko's user avatar
3 votes
Accepted

Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far?

For obvious reasons (laziness) I used the example for creating the table and index from this post: https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-...
Balazs Papp's user avatar
  • 40.6k
3 votes
Accepted

COALESCE still returning null values

In addition to what Akina said about your subquery possibly returning an empty rowset, which will produce a NULL. You're also turning around and concatenating the result of the SELECT right back to a ...
Brendan McCaffrey's user avatar
2 votes

UPDATE and CASE problem

I think you want to do something like UPDATE sciana1 SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0); COALESCE() will return you the first non-NULL parameter. Then max(id) is NULL for ...
András Váczi's user avatar
2 votes

COALESCE on a single table

This is the generally accepted standard for how to concatenate values contained over multiple rows. SELECT ID, LEFT(brands, LEN(brands) -2) brands FROM ( SELECT DISTINCT ID, ...
BZN_DBer's user avatar
  • 180
2 votes

How can I group the null results in this FULL OUTER JOIN with non-null responses?

All this approach does in to make your original query a derived table then group by pub_id. SET hive.auto.convert.join = TRUE; SELECT max(DEAL_ID) as DEAL_ID , EXCHANGE_ID , PUB_ID , ...
RMathis's user avatar
  • 472
2 votes
Accepted

SELECT COALESCE with subselects is throwing error 'Operand should contain 1 column'

SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.journalentry_id = 1 ORDER BY FIND_IN_SET(jetc.language_id, '1,2') DESC LIMIT 1
Akina's user avatar
  • 19.6k
2 votes
Accepted

coalesce more than 1 column in sql server

Looks like you need an OUTER APPLY select wi.productSerial, wi.productName isnull(pp.price, ppe.price) as baseprice, isnull(pp.description, ppe.description) as description from warehouseItems ...
Charlieface's user avatar
  • 12.4k
1 vote

MySQL Coalesce date missing results, why?

Don't use BETWEEN for checking a date or datetime range; it is inclusive, so you get the second (usually midnight) at both ends. For checking DATE or DATETIME against one "day", do this: WHERE col &...
Rick James's user avatar
  • 77.6k
1 vote

Calculation showing wrong result

I built a fiddle with your initial data ... http://sqlfiddle.com/#!17/ab139/1 @Verace the '%' is added to the result to make it appear a percentage number still I don't get your result ... I assume ...
eagle275's user avatar
  • 684
1 vote
Accepted

COALESCE with cast

NULLIF, like you already found: SELECT NULLIF(raw_record->>'ObjectCount','')::int ... .. to replace the empty string with NULL. Strictly speaking, your question asks for more: Is there a ...
Erwin Brandstetter's user avatar
1 vote

COALESCE with cast

I was on the right track but the wrong train. Here's what ended up working: select COALESCE(NULLIF(raw_record->>'ObjectCount',''),0)::int from resources where record_id = '274015000000'
user101289's user avatar
1 vote

Merging continous date range using T-SQL

SQL Server 2008. So far I've been able to fill in the enddtes by joining on ROW_NUMBER -1 and using COALESCE ;with cte as ( select row_number() over(order by startdte) as ROWNO, * from #hours) ...
user149513's user avatar
1 vote

MySQL - Why is my BASH Script insert/update query not working correctly?

Solution: Reboot server I hate my life...
Adam Kennedy's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible