Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [null]

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

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

In Snowflake, default value NULL on a not-nullable column?

In Snowflake, what are the implications of having a default value of null on a non-nullable column? Also, when I describe a particular table in Snowflake, I see a default value of NULL in all columns. ...
Jimbo's user avatar
  • 65
0 votes
1 answer
57 views

How does a NULL value get created in the various DBMS?

I'm wondering if the creation of a NULL value is standardized across the various DBMS and, if not, how it differs across them.
Andrea Nerla's user avatar
0 votes
1 answer
55 views

Update selected columns containing 0 with NULL

I want to update all columns named with suffix '[]' in the table geochem if they have zeros (0) with NULL. This query returns all relevant column names: select c.COLUMN_NAME from information_schema....
kitchenprinzessin's user avatar
0 votes
1 answer
45 views

JSON_Value problem when adding persisted Computed column

i want to add a persisted column and i get an error: Msg 8116, Level 16, State 8, Line 3 Argument data type NULL is invalid for argument 2 of JSON_QUERY function. here is my script: ALTER TABLE [...
dexon's user avatar
  • 47
3 votes
6 answers
4k views

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

I have the following query where I'm looking for any rows that are not equal to 1 for the column istrue. However, the results only include records with 0 and omit those with null. While I am aware ...
lifeisajourney's user avatar
-1 votes
2 answers
64 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(...
Java_Beginner's user avatar
0 votes
1 answer
98 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 ...
Andrey's user avatar
  • 3
0 votes
1 answer
44 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(...
Kasbolat Kumakhov's user avatar
-1 votes
1 answer
42 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 ...
Valentin's user avatar
0 votes
1 answer
56 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 ...
Ken Lyle's user avatar
1 vote
1 answer
1k 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 ...
Manngo's user avatar
  • 2,839
3 votes
1 answer
123 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: ...
Imad Ahmad's user avatar
0 votes
1 answer
114 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&...
Matt Ross's user avatar
2 votes
2 answers
756 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 | ...
nick314's user avatar
  • 147
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 ...
Juergen's user avatar
  • 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 ...
DaringAardvark's user avatar
0 votes
1 answer
270 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?
Saransh's user avatar
1 vote
1 answer
48 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 ...
NickR's user avatar
  • 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/...
Aleksey Vitsko's user avatar
2 votes
2 answers
13k 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 ...
AmQ7's user avatar
  • 53
6 votes
1 answer
15k 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" ...
ogirginc's user avatar
  • 265
-1 votes
2 answers
5k 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(...
Nasir Hussain's user avatar
4 votes
1 answer
11k 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, ...
jo-so's user avatar
  • 53
6 votes
5 answers
22k 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. ...
Klappoklang's user avatar
0 votes
1 answer
4k 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 ...
Déjà vu's user avatar
  • 473
1 vote
1 answer
4k 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 ...
Kevin Cox's user avatar
  • 111
0 votes
1 answer
701 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(...
Ziqi Liu's user avatar
  • 115
0 votes
1 answer
43 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-...
beginsql's user avatar
14 votes
4 answers
11k 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", &...
user16508174's user avatar
2 votes
1 answer
685 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 ...
Alireza Hosseinitabar's user avatar
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, ...
André Christoffer Andersen's user avatar
1 vote
2 answers
629 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 ...
Evan Carroll's user avatar
  • 63.5k
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 ...
Evan Carroll's user avatar
  • 63.5k
-2 votes
1 answer
305 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 ...
Legion's user avatar
  • 113
-1 votes
1 answer
1k 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 ...
user15080516's user avatar
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 ...
Sampisa's user avatar
  • 131
2 votes
1 answer
3k 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 ...
Stefano Linguari's user avatar
1 vote
2 answers
59 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 ...
KNejad's user avatar
  • 113
1 vote
2 answers
5k 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 ...
vabatta's user avatar
  • 23
1 vote
2 answers
215 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 ...
Drahcir's user avatar
  • 13
1 vote
1 answer
67 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 +------+...
Neil Lombardo's user avatar
-1 votes
3 answers
369 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 ...
User1974's user avatar
  • 1,467
4 votes
1 answer
4k 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 ...
mylktea's user avatar
  • 41
2 votes
1 answer
3k 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 ...
rdnobrega's user avatar
  • 123
1 vote
1 answer
32k 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 ...
Antsamotady's user avatar
-1 votes
1 answer
251 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 |...
Ganesh Jadhav's user avatar
-1 votes
1 answer
84 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 ...
Meettu's user avatar
  • 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 ...
Fiona Daniel's user avatar
-1 votes
1 answer
327 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` ...
Alex Gusev's user avatar
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 ...
axdna's user avatar
  • 111

1
2 3 4 5
7