Questions tagged [null]

A database concept used to represent missing, unknown or inapplicable data.

Filter by
Sorted by
Tagged with
0 votes
0 answers
8 views

Can an ER relationship have `NULL`able attributes?

Texts out there about the entity-relationship model seem to only mention NULLable attributes on entity types and not relationships. Is there a concrete saying whether such attributes are allowed to ...
0 votes
1 answer
56 views

Syntax Error in Boolean Null values

I have a csv that contains Boolean variables (yes,no,blanks). How can I recode blanks so that it'd integrate in the data type. Or fo i have to change data type for blanks to be readable?
1 vote
1 answer
31 views

Database Design: Year of Birth /Year of Death best practice - Postgres

I have a table of people (authors), and columns for Year_of_Birth and Year_of_Death. These are integers, as I wish to be able to compute things like current_age and age_at_death and ...
  • 121
1 vote
1 answer
263 views

Storage space used for NULL values

According to below links, NULL values take some storage space: https://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server https://www.sqlservercentral.com/forums/topic/...
1 vote
1 answer
427 views

SELECT only IS NOT NULL columns with LEFT JOIN

I have a query where i need to select product options from multi tables. SELECT p.option_id, p.product_id, p.sku, p.qty, p.price, c.color_label, s.size_label, m.material_label, v.variation_label ...
  • 43
3 votes
1 answer
2k views

Is it safe to use default value with not null when adding a new column?

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below: ALTER TABLE "blogs" ADD "published" ...
  • 235
-1 votes
1 answer
579 views

how to show return zero if no row return?

I spent my half day on it, but still did not find solution, I tried COALESCE,IFNULL, IS NULL, IF conditions, <=>, IS NOT NULL etc but nothing work for me. The query is SELECT COALESCE(SUM(...
2 votes
1 answer
605 views

Difference between NULL and empty text

I'm using Postgres. Is there any significant difference in storage or performance between using val text NULL CHECK(val <> '') (or varchar) and val text NOT NULL? The text can't be empty. Hence, ...
  • 23
0 votes
0 answers
382 views

MySQL 8 falsely giving "column x cannot be null" error on update

I'm running the below query on a MySQL 8 managed server hosted at DigitalOcean! UPDATE games_codes SET result=5 WHERE id=1; When I run this query, I'm getting the following error. Column field ...
3 votes
4 answers
3k views

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

This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be. ...
0 votes
1 answer
990 views

MySQL 8.0 timestamp CURRENT_TIMESTAMP sets date to '0000-00-00 00:00:00' (was ok in 5.7)

The table T has this field +---------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra ...
  • 397
0 votes
0 answers
69 views

Grouping null and not null together

I'm trying to GROUP BY fingerprint & email, managing nulls emails values. I must aggregate ids of null emails by fingerprint as they are probably the same user. So this: +----+-------------+-------...
  • 101
0 votes
1 answer
897 views

Postgres advantages of BOOLEAN NOT NULL vs BOOL with TRUE/NULL

I want to add a flag to a frequently accessed table in my database. It is logically a boolean that is set for a very small portion of the records <0.1%. I want to ensure that I am not causing a ...
  • 101
0 votes
1 answer
236 views

postgres set null will improve performance in sparse data?

I have ~50 mil data entries, while most of them(maybe 80%) have null values in a column(currently I assigned a dummy value to those null entries). However, I'm doing frequent searching on non-null(...
  • 115
0 votes
1 answer
35 views

How to re-design my relational model if there will be NULLs in one column?

I'm trying to build database which allow me store job postings from scraped websites and now I'm in phase of creating a relational model. Getting straight to the point I want to know how should I re-...
10 votes
3 answers
4k views

Why doesn't my UNIQUE constraint trigger?

I have this UNIQUE constraint: ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE ("col1", "col2", "col3", "col4", "col5", "col6", &...
2 votes
1 answer
165 views

Why my not-null columns become nullable in my CTE recursive query?

I have a simple hierarchical table. Let's say it has these columns: Id, [Guid], ParentId, Title Only ParentId is nullable. Other columns are not nullable. I have created a recursive query to show me ...
1 vote
2 answers
578 views

How to "succinctly" detect changed values in the PostgreSQL's upsert (on conflict) where clause while supporting null changes?

We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, ...
1 vote
2 answers
193 views

Does SQL Server's is_nullable mean anything at all?

SQL Server has a type flag called is_nullable you can see it on sys.types. Currently, (SQL Server 2019) it's only set to FALSE two types, sys.timestamp and sys.sysname. On sys.timestamp this type ...
  • 57.3k
10 votes
1 answer
879 views

Why does PostgreSQL allow NULLs in domains that prohibit NULL?

PostgreSQL allows NULLs in domains marked as NOT NULL. Why is this, the docs say this about it, It is possible for a column that is nominally of the domain type to read as null despite there being ...
  • 57.3k
-3 votes
1 answer
88 views

Why do we have to use IS NULL instead of = NULL? [duplicate]

I don't know if this is the case for all database implementations but it is for SQL Server and MySQL which are the two I've worked with. I've read articles about the topic and the manuals of SQL ...
  • 103
-1 votes
1 answer
61 views

Why does PostgreSQL apparently ignore null cells whenever using NOT LIKE?

This set me back for a long time, puzzled by why the entirely wrong record was returned: SELECT * FROM bookkeeping WHERE category = 'Food' AND "our description" NOT LIKE 'Online ...
2 votes
2 answers
781 views

MySQL best way to define a couple of columns as both null or both not-null

On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
  • 131
1 vote
1 answer
245 views

Mysql update table from another table with condition

In mysql i have 2 table. First wp_postmeta_import Second wp_postmeta_temp These tables have these columns: meta_id post_id meta_key meta_value I need to update only my column meta_value of ...
1 vote
2 answers
51 views

Using a NULL value to signify that the value does not mattter. Not that it isn't known [closed]

Say I have a table with computers where one column is a boolean "fan". If true the computer has a fan, if false it does not. It can never be null. Now I have another table with a list of ...
  • 113
1 vote
2 answers
1k views

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data. product_id variant_id 123-123 ABC 123-123 NULL 456-456 ABC I cannot ...
  • 23
1 vote
2 answers
119 views

EAV - COUNT()/SUM() on column and row values with and without NULLs

Assume that there is a table demo with these data: id userID title data 1 1 DATE 01-01-2021 2 1 COLOR RED 3 2 DATE 4 2 COLOR YELLOW 5 3 DATE 04-01-2021 6 3 COLOR RED 5 4 DATE 04-01-2021 6 4 ...
  • 13
1 vote
1 answer
25 views

Displaying null fields as 0 in mySQL select [duplicate]

I have a database that collects statistics from Usenet. When I do a basic select such as: select day, count(day) from statistics where month = 12 and year = 2020 group by day; I get the result +------+...
-1 votes
3 answers
285 views

Function: if either x or y are null, return null

I'm looking for an Oracle 18c function that evaluates to null if either of two variables is null. The Constraints: (details here) The function/expression can't contain spaces (so CASE won't work). I ...
  • 1,387
2 votes
1 answer
2k views

Find tables with columns with empty and NULL values in Postgresql

After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or ...
  • 21
2 votes
1 answer
1k views

PostgreSQL's record type returning null?

I don't understand why this result is possible. Can someone explain why the first assert returns true and the second false? generate_token() is a function that generates a random bytea and inserts it ...
  • 123
0 votes
0 answers
219 views

Disallow empty strings in text columns (Aurora / PostgreSQL)

We have a Django application connected to an Oracle database running in production and we are planning to create a new instance on AWS. Both instances would coexist. On AWS we would like to use Aurora ...
0 votes
1 answer
11k views

How to set default value as empty string for all rows at once in MySQL while creating the table

In MySQL, while creating a table, I'like to have empty string '' as value for all needed columns instead of 'NULL'. Is it possible, is it safe and how can I do to do that at once? I have the following ...
-1 votes
1 answer
102 views

Get records where no column is null [duplicate]

I've a table with 100+ (nullable) columns and 10,000+ rows. I'd like to fetch rows that have no columns null. id|f001|f002|f003|....f100 1 |null|aa | ss |sada 2 |sdad|aa | ss |sada 3 |as |aa | ss |...
-1 votes
1 answer
32 views

Replacing Null value with existing value

How would it be possible to replace the null value from Order - column with the above existing value that has the the same Document-number? Document Value Value2 Order 1234567 4 7 ...
  • 1
2 votes
2 answers
2k views

Count missing values in each column of the table in PostgreSQL

Question: Which column of fortune500 (a table) has the most missing values? To find out, I have checked each column individually mentioned below, which is very tedious. SELECT SUM(CASE WHEN ...
-1 votes
1 answer
71 views

Nullable integer as part of composite primary key

I want to store phone numbers as integers and I've created this SQL: DROP TABLE `user_id_phone`; CREATE TABLE `user_id_phone` ( `user_id` int unsigned NOT NULL, `country_code` ...
0 votes
1 answer
748 views

What is the best way to store optional values in table?

I have a large table that will be partitioned (about 300 million rows). There are currently 57 columns in the table. At the moment we also have an 'Option' column, which is to hold values ​​that would ...
  • 101
-3 votes
1 answer
53 views

Why am I getting errors which cannot logically happen? [duplicate]

I'm ready for the nuthouse at this point. This error makes me want to stab my eyes out after staring at the screen and testing stuff for literally MONTHS now! The offending code, which looks to me ...
's user avatar
1 vote
1 answer
2k views

Postgres 11 - Range Partitioning on a Date Column with NULL values

We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we ...
  • 321
0 votes
1 answer
4k views

Testing a Hive array for IS NULL says not null

I have a table containing an array, and I want to check if it is empty or NULL. It appears that I cannot check the NULL directly! Can anyone shed light on why the NULL check isn't working? create ...
  • 531
1 vote
2 answers
4k views

Why does my SELECT query not return null values? [duplicate]

I have the following SQL script: CREATE temporary table if not EXISTS the_values ( key SERIAL, value INTEGER NULL ); insert into the_values(value) values (null),(1),(null),(2),(3),(4),(5),(6),(10),...
0 votes
0 answers
470 views

MySql view returns a single NULL value for a NOT NULL column, why?

I am working with a database built by a vendor, which I have remote access to. I noticed that the field modify_date on one of the views is not null, according to DBeaver. the tick indicates "not ...
-1 votes
1 answer
50 views

Joining 3 tables together and selecting the null elements without using is not null [closed]

select Name from Passagerare left join EB on Passagerare.Personnr = EB.Personnr left join MM on Passagerare.Personnr = MM.Personnr where Passagerare.Personnr = EB.Personnr or Passagerare.Personnr =...
  • 121
7 votes
2 answers
3k views

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

(former question was: Will Postgres use a multi-column index when setting multiple colums not null?) Typically, when I set a column not null, if it doesn't have an index then I add it first, so that ...
0 votes
2 answers
27 views

How to check if staff member has end date with a Null value

I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish. ...
  • 5
0 votes
3 answers
71 views

Problem with IF statement [closed]

I am having an issue with this stored procedure. When the user selects "Yes", it should do what it says in the if statement for the "Yes" but it does not do anything, it does not insert anything into ...
  • 67
0 votes
1 answer
887 views

JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas. Basically, what I want to do seems like a UNION where records are "collapsed" in ...
1 vote
2 answers
130 views

Should fields that are set to Not Null also have a constraint refusing empty strings?

Any field that I would expect a value (Like an orderNumber or customerNumber) I would set as not nullable. If I'm expecting the value of the field to be not null, shouldn't I also always have a ...
  • 169
0 votes
0 answers
17 views

What is the commonly-accepted practice to denote the different meaning of NULLs?

I am thinking about what's the meaning of NULL in my database systems. In some columns it mean unknown / missing data. In some columns it mean a not-applicable field. In some columns it mean the ...

1
2 3 4 5
7