Questions tagged [null]
A database concept used to represent missing, unknown or inapplicable data.
319
questions
2
votes
1answer
69 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 ...
0
votes
2answers
38 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
2answers
71 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 ...
10
votes
1answer
678 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 ...
-3
votes
1answer
63 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 ...
-1
votes
1answer
36 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
2answers
238 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 ...
1
vote
1answer
19 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
2answers
44 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 ...
1
vote
2answers
151 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 ...
1
vote
2answers
49 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
...
1
vote
1answer
22 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
3answers
216 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 ...
2
votes
1answer
332 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 ...
2
votes
1answer
99 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 ...
0
votes
0answers
71 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 ...
-1
votes
1answer
2k 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
1answer
65 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
1answer
29 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 ...
2
votes
2answers
604 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
1answer
36 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
1answer
217 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 ...
-3
votes
1answer
47 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 ...
1
vote
1answer
713 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 ...
0
votes
1answer
2k 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 ...
1
vote
2answers
1k 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
0answers
115 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
1answer
46 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 =...
5
votes
2answers
834 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
2answers
23 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.
...
0
votes
3answers
52 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 ...
0
votes
1answer
295 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
2answers
53 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 ...
0
votes
0answers
15 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
vote
1answer
123 views
In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL?
I have a SQL Server with numeric types for few columns.
When getting a table structure, I have the following results:
Query:
SELECT *
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '...
0
votes
1answer
865 views
How to remove NULL values in multidimensional array?
SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
4
votes
2answers
285 views
Why is the result of a totally deterministic and all-encompassing “case” in select query in view nullable?
I have a table, with Id as the primary key.
create table Anything
(
Id bigint not null primary key identity(1, 1)
)
When I look at this table in Object Explorer, I see this image of course:
As ...
1
vote
0answers
56 views
Why does PostgreSQL explicitly cast NULL to parameter data type in functions?
Let's say I create the following function:
create or replace function my_new_function(
p_something text default null
)
etc....
But if I go to check the function on PostgreSQL I see this instead:
...
2
votes
1answer
53 views
How to filter table 2 before joining to table 1 so that the result still includes all of table 1, even if no match in table 2?
The following returns all rows from table 1 where post_type = dlm_download. It groups like values from table 2 by the matching column in table 1 so that each row in table 1 has a maximum of one ...
1
vote
1answer
185 views
Database Design: Normalization and NULL values
I have normalized my table from 0NF to 2NF (hopefully correct).
How do I now go from 2NF to 3NF?
Also, ResponseToTransactionId is a recursive foreign key that will contain NULL values a lot of the ...
0
votes
2answers
45 views
Is there any way to force at least one column NOT NULL
I have a schedule_days table that is having id, day, date columns.
id - auto increment, primary key
day - weekday - enum(weekdays) default not null
date - a specific date - default not null
...
-2
votes
1answer
415 views
Working with NULL Date Fields [closed]
I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm ...
1
vote
3answers
89 views
NULL as a @variable within WHERE clasue
I would like to SET 'IS NULL' as a variable and use it in a WHERE clause.
I will be unioning many tables together that carry the same WHERE clause and would like flexibility to use a variable for my ...
-1
votes
1answer
77 views
Column values in SQL Server changes to null [closed]
There is one specific column whose value automatically changes to null after data is inserted into it.
I have tried to update the column from designer, update the column with query, delete the ...
1
vote
1answer
38 views
Null check condition in Where clause
DECLARE @SubDesc1 nvarchar(100)
SET @SubDesc1 = null
I would like to do something like that.
Query 1
SELECT *
From Table1
where Category = 'Category1'
AND [Description] ='ABC'
AND ...
0
votes
2answers
24 views
Work around average behaviour with nulls
I have two tables:
Product (PK product_id, brand, model)
Order (PK invoice_id, customer_name, customer_email, FK product_id)
I am trying to find the product(s) whose number of times that product is ...
6
votes
1answer
413 views
Why does ORDER BY NULLS LAST affect the query plan on a primary key?
Using PostgreSQL 11, I have the following table with around 450 million rows:
postgres=> \d+ sales
Table "public.sales"
...
-1
votes
1answer
60 views
Can't get this query to work properly
I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9
I have the following database:
CREATE TABLE user (
id INT NOT ...
1
vote
2answers
428 views
How to filter hstore where values are not null in a trigger?
The easiest way to explain the question is in the usage of an audit trigger.
However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). ...
2
votes
2answers
47 views
NULL rows and IS NULL
I've been writing some queries that look like this:
SELECT
...
FROM table_a a
LEFT JOIN table_b b
ON b.id = a.id
LEFT JOIN table_c c
ON c.id = a.id
In these queries only one of the joins can ...
