Skip to main content
The 2024 Developer Survey results are live! See the results
34 votes
Accepted

PostgreSQL UPSERT issue with NULL values

Clarify ON CONFLICT DO UPDATE behavior Consider the manual here: For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by ...
Erwin Brandstetter's user avatar
30 votes
Accepted

How can I set a column to NOT NULL without locking the table during a table scan?

Another answer from another postgresql contributor. PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented. Proper implementation ...
Melkij's user avatar
  • 3,259
29 votes

How do I order by NULLS LAST if my database doesn't support it?

There is a (closed) connect item requesting support for NULLS LAST in SQL Server. A couple of other ways would be ORDER BY CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, FullName, ...
Martin Smith's user avatar
  • 85.4k
24 votes

Why doesn't my UNIQUE constraint trigger?

NULL is the culprit, because two NULL values are considered distinct in a UNIQUE constraint - in accordance with the SQL standard. Postgres 15 or newer Postgres 15 adds an option to change this ...
Erwin Brandstetter's user avatar
23 votes
Accepted

Conditional string concatenation in PostgreSQL

The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored: select concat_ws('//', owner_addr1, owner_addr2, owner_addr3) Test: red=...
ypercubeᵀᴹ's user avatar
22 votes
Accepted

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

I'll share how I have done this in the past. It is designed to solve the specific limitation of pre-deployment scripts that you call out in your second point: Adding the column in a pre-...
Josh Darnell's user avatar
  • 29.4k
21 votes

Why does COUNT() aggregate return 0 for 'NULL'?

This is by design. COUNT(<expression>) counts rows where the <expression> is not null. COUNT(*) counts rows. So, if you want to count rows, use COUNT(*).
ypercubeᵀᴹ's user avatar
21 votes
Accepted

(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL

You have to distinguish two situations: you compare one COLUMN against NULL, or you compare the whole ROW (RECORD) against NULL. Consider the following query: SELECT id, txt, txt ...
joanolo's user avatar
  • 13.5k
20 votes
Accepted

Why Is Null Ignored In Query Results

SQL uses ternary (three-valued) logic and comparisons with NULL are tricky, counter intuitive. Regarding the condition: WHERE valid NOT IN ('Green', 'Blue', 'White') it expands to: WHERE valid <&...
ypercubeᵀᴹ's user avatar
20 votes
Accepted

Why do I need to cast NULL to column type?

This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here: SELECT pg_typeof(NULL); pg_typeof ─────────── unknown This changes when a VALUES table comes into ...
András Váczi's user avatar
19 votes
Accepted

Not equal to operator is not returning NULL values in SQL Server

Consider checking documentation: NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or ...
Yano_of_Queenscastle's user avatar
18 votes
Accepted

Does PostgreSQL index null values?

Since at least PostgreSQL 8.3, PostgreSQL indexes NULL using a bitmap on the index. That means "yes" it's indexed. Except it takes a bit, rather than a byte[s]. There is no speed advantage insofar as ...
Evan Carroll's user avatar
  • 63.9k
17 votes
Accepted

How to set a NOT NULL column into NULL in PostgreSQL?

You can simply drop NOT NULL ALTER TABLE app_client_users ALTER COLUMN client_id DROP NOT NULL;
Shakeer Mirza's user avatar
14 votes
Accepted

mysql - unique index with nulls - actual solution anyone?

[This is not tested as it requires MySQL 5.7.6 or above] While I agree with the comments above, I still have an idea that can be tried, which I think is not the best in terms of performance, but it ...
Jehad Keriaki's user avatar
14 votes
Accepted

Check constraint on nullable data, how to approach?

Use a CHECK constraint. CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to UNKNOWN and the row is accepted. Only ...
ypercubeᵀᴹ's user avatar
14 votes
Accepted

Carry over long sequence of missing values with Postgres

Form groups with the window function count() and then take the first value in each group: SELECT foo_label , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS ...
Erwin Brandstetter's user avatar
14 votes
Accepted

Count NULL values per row

1. You know the column names For Postgres 9.6 or later, use num_nulls() SELECT id, num_nulls(a,b,c,d,e) FROM tbl; Returns your desired result exactly, for any mix of data types. The manual: ...
Erwin Brandstetter's user avatar
13 votes

Using IS NULL in CASE Expression in WHERE Clause

The problem is likely the comparison to NULL, as explained in David Spillett's answer above. When @UserRole = 'Analyst', the comparison SupervisorApprovedBy = NULL will give UNKNOWN (and the row won't ...
ypercubeᵀᴹ's user avatar
13 votes
Accepted

How to get the last not-null value in an ordered column of a huge table?

A common solution to this type of problem is given by Itzik Ben-Gan in his article The Last non NULL Puzzle: DROP TABLE IF EXISTS dbo.Example; CREATE TABLE dbo.Example ( id integer PRIMARY KEY, ...
Paul White's user avatar
  • 87.1k
13 votes
Accepted

Multiple BIT datatype columns in the same table

For a fixed length field (such as a bit value), the field will always take up the same amount of space - in your case one bit. The NULL bitmap is in the data row itself - this stored whether a ...
George.Palacios's user avatar
13 votes

Why does PostgreSQL allow NULLs in domains that prohibit NULL?

Well, for starters look at this, CREATE DOMAIN mydomain AS int; CREATE TABLE foo(bar) AS SELECT 42::mydomain; SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar) FROM foo AS f1 ...
Evan Carroll's user avatar
  • 63.9k
12 votes

Increase speed altering column on large table to NON NULL

Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single ...
Joe Obbish's user avatar
  • 32.5k
11 votes
Accepted

Is there a function which the opposite of NULLIF?

NULLIF(expr1, NULLIF(expr1, expr2)) does what you want.
Balazs Papp's user avatar
  • 40.8k
11 votes

NULL value causes blank row in SELECT results for text concatenation

Use COALESCE function to return the current value or an empty string. SELECT ' (' || obj_id || ', ''' || coalesce(obj_type, '') || '''),' FROM objects; | ?column? | | :-------------- | | ...
McNets's user avatar
  • 23.8k
10 votes

How to get the last not-null value in an ordered column of a huge table?

I expected t-sql to optimize it out - on a block/record level, the task to do is very easy and linear, essentially a for loop ( O(n) ). That's not the query that you wrote. It may not be equivalent ...
Joe Obbish's user avatar
  • 32.5k
10 votes

Why doesn't my UNIQUE constraint trigger?

Your problem stems from the NULL values. From the documentation (emphasis added) In general, a unique constraint is violated if there is more than one row in the table where the values of all of the ...
gwaigh's user avatar
  • 259

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