Questions tagged [null]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
22 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 ...
user avatar
  • 33
3 votes
1 answer
91 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" ...
user avatar
  • 235
-1 votes
1 answer
33 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(...
user avatar
1 vote
1 answer
31 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, ...
user avatar
  • 13
0 votes
0 answers
83 views

MySQL 8 falsely giving "column x cannot be null" error on update

I'm running the below query on a MySQL 8 managed server hosted at DigitalOcean! UPDATE games_codes SET result=5 WHERE id=1; When I run this query, I'm getting the following error. Column field ...
user avatar
2 votes
2 answers
551 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. ...
user avatar
0 votes
0 answers
282 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 ...
user avatar
  • 395
0 votes
0 answers
30 views

Grouping null and not null together

I'm trying to GROUP BY fingerprint & email, managing nulls emails values. I must aggregate ids of null emails by fingerprint as they are probably the same user. So this: +----+-------------+-------...
user avatar
  • 101
0 votes
1 answer
441 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 ...
user avatar
  • 101
0 votes
1 answer
105 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(...
user avatar
  • 113
0 votes
1 answer
31 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-...
user avatar
9 votes
3 answers
3k 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", &...
user avatar
2 votes
1 answer
91 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 ...
user avatar
0 votes
2 answers
216 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, ...
user avatar
1 vote
2 answers
145 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 ...
user avatar
  • 55.7k
10 votes
1 answer
797 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 ...
user avatar
  • 55.7k
-3 votes
1 answer
76 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 ...
user avatar
  • 103
-1 votes
1 answer
45 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 ...
user avatar
2 votes
2 answers
627 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 ...
user avatar
  • 131
1 vote
1 answer
61 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 ...
user avatar
1 vote
2 answers
48 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 ...
user avatar
  • 113
1 vote
2 answers
523 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 ...
user avatar
  • 23
1 vote
2 answers
86 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 ...
user avatar
1 vote
1 answer
25 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 +------+...
user avatar
-1 votes
3 answers
254 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 ...
user avatar
  • 1,375
2 votes
1 answer
1k 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 ...
user avatar
  • 21
2 votes
1 answer
665 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 ...
user avatar
  • 123
0 votes
0 answers
164 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 ...
user avatar
0 votes
1 answer
8k 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 ...
user avatar
-1 votes
1 answer
74 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 |...
user avatar
-1 votes
1 answer
31 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 ...
user avatar
  • 1
2 votes
2 answers
2k 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 ...
user avatar
-1 votes
1 answer
61 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` ...
user avatar
0 votes
1 answer
586 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 ...
user avatar
  • 91
-3 votes
1 answer
49 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
1 vote
1 answer
1k 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 ...
user avatar
  • 321
0 votes
1 answer
3k 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 ...
user avatar
  • 531
1 vote
2 answers
3k 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),...
user avatar
0 votes
0 answers
324 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 ...
user avatar
-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 =...
user avatar
  • 121
6 votes
2 answers
2k 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 ...
user avatar
0 votes
2 answers
24 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. ...
user avatar
  • 5
0 votes
3 answers
54 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 ...
user avatar
  • 67
0 votes
1 answer
651 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 ...
user avatar
1 vote
2 answers
71 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 ...
user avatar
  • 169
0 votes
0 answers
17 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 ...
user avatar
1 vote
1 answer
239 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 = '...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
4 votes
2 answers
291 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 ...
user avatar
1 vote
1 answer
215 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: ...
user avatar

1
2 3 4 5
7