Questions tagged [null]
A database concept used to represent missing, unknown or inapplicable data.
341
questions
-1
votes
2
answers
58
views
SQL breaking my query where there is NULL value
Here is my sql statement and the error I am facing:
SQL> INSERT INTO COUNTRY(COUNTRY_CODE)
select unique(country_code) from AIRPORTDEMODATA where country_code IS NOT NULL;
2 INSERT INTO COUNTRY(...
0
votes
1
answer
36
views
Cassandra dsbulk loading data with null values doesnt implement it
I have table with 2 fields: id (primary key) fld_1 text, i have 1 row in target table, for example 1 'hello world', and i have source tsv file with 1 row: 1\t. After loading in target table i expect ...
- 3
0
votes
1
answer
23
views
Why is PostgreSQL `coalesce` not working as expected/hoped inside a function? [closed]
A simple function that looks like this:
CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text)
RETURNS tsvector
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
RETURN
to_tsvector(...
-1
votes
1
answer
39
views
Design and normalization issue : type of FK depends of an other field
I have a CapitalIncrease table, which contains a list of available payment methods. For each payment method, I need to put some data (SepaDocument OR Iban OR Crypto).
Here is a mermaidjs graphic of ...
- 1
0
votes
1
answer
55
views
Is UPSERT feature complete?
Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value.
Say we have a table of businesses and some ...
- 33
1
vote
1
answer
439
views
When does Oracle still treat empty strings as NULL?
I was looking for the difference between VARCHAR and VARCHAR2 and learned, among other things, that Oracle’s VARCHAR2 treats empty strings as NULL, or the other way round. Also, apparently I should be ...
- 2,759
3
votes
1
answer
92
views
Selecting second highest value using two SQL queries returns different results
I have the following tables from which I am to select the second highest salary:
Table 1:
id
salary
1
100
2
200
3
300
Table 2:
id
salary
1
100
I wrote two SQL queries to achieve this:
Query 1:
...
- 133
0
votes
1
answer
57
views
MYSQL: Return a predefined value if result of join is null
Before all, sorry my very bad english level.
Here's my problem.
If i have two tables:
'Products'
|id | product |
|---|---------|
|1 | "Fork" |
|2 | "Spoon" |
|3 | "Knife&...
- 23
0
votes
0
answers
28
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 ...
- 437
1
vote
2
answers
406
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 | ...
- 127
3
votes
0
answers
2k
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
1k
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
1
answer
241
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
43
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
2
votes
2
answers
2k
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,472
2
votes
2
answers
10k
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
6
votes
1
answer
12k
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" ...
- 265
-1
votes
1
answer
4k
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(...
4
votes
1
answer
8k
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, ...
- 53
6
votes
6
answers
17k
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.
...
- 61
0
votes
1
answer
3k
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 ...
- 463
1
vote
1
answer
3k
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
669
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
42
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
14
votes
4
answers
9k
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", &...
- 287
2
votes
1
answer
564
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 ...
4
votes
2
answers
2k
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
546
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 ...
- 62.2k
11
votes
1
answer
1k
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 ...
- 62.2k
-2
votes
1
answer
210
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 ...
- 113
-1
votes
1
answer
682
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 ...
- 627
2
votes
2
answers
2k
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
2k
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
55
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
4k
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
210
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
48
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
347
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,447
4
votes
1
answer
3k
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 ...
- 41
2
votes
1
answer
2k
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
1
answer
28k
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
225
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
66
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
3
votes
2
answers
4k
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 ...
- 31
-1
votes
1
answer
245
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
2k
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 ...
- 111
-3
votes
1
answer
59
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
3
votes
1
answer
3k
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 ...
- 361
0
votes
1
answer
5k
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 ...
- 539
1
vote
2
answers
8k
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),...
