Questions tagged [null]
A database concept used to represent missing, unknown or inapplicable data.
338
questions
0
votes
0
answers
17
views
Effect of correlated subquery used in an IN clause on the outer query?
I'm trying to understand in what way a correlated subquery affects the outer query when used in an IN clause.
This requires some prep code, so here goes:
Prep code
We have category and product ...
- 425
1
vote
2
answers
59
views
PostgreSQL WHERE IS NULL causes bad query plan
I have a PostgreSQL table with the following schema and indexes
+---------+---------+-----------+----------+--------------+-------------+
| Column | Type | Modifiers | Storage | Stats target | ...
- 117
3
votes
0
answers
123
views
Difference between To_Number (Null) and Cast (Null as Number) in Oracle
There is a difference in behavior between these two variants to create a view with a null number column and then a table from that view: One fails and one succeeds.
At the same time there is no ...
- 76
0
votes
1
answer
199
views
Access Search Form is not including null values
Sorry if this is an overly easy question, I don't use access often but was asked to make a search form for a table our company uses to track remake parts. I have a form created with a series of ...
0
votes
0
answers
106
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
137
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
1
vote
1
answer
35
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
601
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/...
- 5,054
2
votes
2
answers
3k
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
...
- 53
3
votes
1
answer
4k
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
2k
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(...
3
votes
1
answer
2k
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, ...
- 33
4
votes
4
answers
6k
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.
...
- 41
0
votes
1
answer
2k
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 ...
- 431
1
vote
1
answer
2k
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 ...
- 111
0
votes
1
answer
409
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
39
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-...
- 55
12
votes
4
answers
6k
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", &...
- 267
2
votes
1
answer
251
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 ...
2
votes
2
answers
908
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
298
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 ...
- 59k
10
votes
1
answer
957
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 ...
- 59k
-3
votes
1
answer
117
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
101
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 ...
- 467
2
votes
2
answers
996
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
551
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
52
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
2k
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
161
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
26
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
324
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,437
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
280
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 ...
- 101
0
votes
1
answer
16k
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 ...
- 13
-1
votes
1
answer
146
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
35
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
3k
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 ...
- 21
-1
votes
1
answer
99
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` ...
- 99
0
votes
1
answer
987
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
55
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 ...
user214700
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 ...
- 331
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 ...
- 541
1
vote
2
answers
5k
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
584
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 ...
- 115
-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
9
votes
2
answers
4k
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 ...
- 605
0
votes
2
answers
38
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
195
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
1k
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 ...
- 3