Questions tagged [null]

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

Filter by
Sorted by
Tagged with
-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(...
Java_Beginner's user avatar
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 ...
Andrey's user avatar
  • 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(...
Kasbolat Kumakhov's user avatar
-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 ...
Valentin's user avatar
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 ...
Ken Lyle's user avatar
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 ...
Manngo's user avatar
  • 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: ...
Imad Ahmad's user avatar
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&...
Matt Ross's user avatar
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 ...
HeyJude's user avatar
  • 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 | ...
nick314's user avatar
  • 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 ...
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
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?
Saransh's user avatar
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 ...
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
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 ...
AmQ7's user avatar
  • 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" ...
ogirginc's user avatar
  • 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(...
Nasir Hussain's user avatar
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, ...
jo-so's user avatar
  • 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. ...
Klappoklang's user avatar
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 ...
Déjà vu's user avatar
  • 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 ...
Kevin Cox's user avatar
  • 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(...
Ziqi Liu's user avatar
  • 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-...
beginsql's user avatar
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", &...
user16508174's user avatar
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 ...
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
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 ...
Evan Carroll's user avatar
  • 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 ...
Evan Carroll's user avatar
  • 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 ...
Legion's user avatar
  • 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 ...
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
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 ...
Stefano Linguari's user avatar
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 ...
KNejad's user avatar
  • 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 ...
vabatta's user avatar
  • 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 ...
Drahcir's user avatar
  • 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 +------+...
Neil Lombardo's user avatar
-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 ...
User1974's user avatar
  • 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 ...
mylktea's user avatar
  • 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 ...
rdnobrega's user avatar
  • 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 ...
Antsamotady's user avatar
-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 |...
Ganesh Jadhav's user avatar
-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 ...
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
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` ...
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
-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 ...
user avatar
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 ...
daniel9x's user avatar
  • 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 ...
PhilHibbs's user avatar
  • 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),...
Dimitrios Desyllas's user avatar

1
2 3 4 5
7