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

learn more… | top users | synonyms

0
votes
0answers
21 views

Postgresql indexing for IS NULL

I have two similar queries, whose only difference (in the last two lines) is that one uses IS NULL while the other compares defined values. Query #1 takes only a few minutes: UPDATE ...
0
votes
1answer
148 views

mysql UNIQUE KEY multiple columns with one column as NULL

I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created. In following schema and I would finally want that family_relation should have ...
0
votes
1answer
28 views

Filling not applicable fields with N/A rather than leaving them empty or Null [duplicate]

I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several ...
0
votes
1answer
194 views

MySQL is inserting “” as 0 in decimal fields. How to stop that?

The fields are nullable, so they do allow nulls. Why would MySQL convert "" to 0 instead of NULL? Is there a way to force it to be NULL instead?
1
vote
2answers
73 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
0
votes
1answer
63 views

LEFT JOIN DEFAULT NULL VALUE

I had query simplified like this : SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id Which b is from group concat select (very dynamic column). Sometime there are 3 column in b, sometime ...
0
votes
3answers
760 views

Two nullable columns one required to have value

No-Explanation Question: Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have ...
0
votes
0answers
51 views

Null in Varchar datatype

I am using a SQL Server database. If NULL value is inserted to a VARCHAR datatype column, what will be the memory size used for the column?
-1
votes
1answer
76 views

SQL Server's aggregate functions ignore nulls. How do I correct this?

According to MS's knowledgebase, Except for COUNT, aggregate functions ignore null values. Does anyone know how I can get it to work correctly, that is, return a null if it attempts to ...
55
votes
8answers
18k views

When to use NULL and when to use an empty string?

I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general: Is there a logical scenario in which it would be useful to distinguish an empty string from NULL? What ...
27
votes
3answers
21k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
2
votes
1answer
64 views

Efficient manual cleanup of foreign keys in MySQL [closed]

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added ...
1
vote
1answer
41 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
0
votes
2answers
262 views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
52
votes
7answers
11k views

Why shouldn't we allow NULLs?

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though. All I can seem to ...
3
votes
3answers
20k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
8
votes
3answers
3k views

Comparing Columns that can contain NULLS - is there a more elegant way?

I know that you can't compare a value to NULL and expect a result without adding something like in the following code... SELECT * FROM A INNER JOIN B ON A.ID = B.ID WHERE A.STRING ...
1
vote
0answers
60 views

How to replace empty, multi-column result with single 'none'

If it is possible to make query in Oracle which will return single column/row with value "none" instead of empty, multi-column result? Otherwise return proper amount of columns with result. This is ...
4
votes
4answers
6k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
0
votes
1answer
32 views

multi-column FKs with NULL values

I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
6
votes
2answers
3k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
0
votes
1answer
30 views

Dealing with co-dependent not null columns

Say I have a table entities (subclassed by people and organizations tables), where each entry requires a primary_email_id. The entities table looks like this: entities ======== id INT AUTO_INCREMENT ...
2
votes
1answer
260 views

How to index a query `WHERE field IS NULL` (postgres)

So I basically have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, ...
1
vote
1answer
329 views

Populate one table with data from another one on selected cells

I have a problem with filling empty cells in one table with data from another one. I got a table with user emails - some of the fields are empty, some are already containing emails. I gathered the ...
0
votes
1answer
75 views

If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records?

I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and ...
1
vote
1answer
74 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
0
votes
0answers
250 views

Oracle 12c, exclude rows wher two columns are both null

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit I am trying to select rows with a non-null value in one of two columns. Data is being selected from a view (MY_VIEW) with both ...
2
votes
2answers
2k views

How do I include nulls during comparisons in SQL Server?

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL ...
1
vote
0answers
23 views

How do I include nulls during comparisons in SQLServer? [duplicate]

So basically in SQLServer a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL but ...
1
vote
3answers
5k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
-1
votes
1answer
96 views

Inserting null value without quotes postgresql

I'm creating a php application which relies on postgresql. Now i have this query: $query= 'INSERT INTO "users" ("name", "username", "email", "phone") VALUES (\''. $name .'\', \''. $username .'\', ...
0
votes
1answer
427 views

Return a default value in group by if selected column is null

I am trying to run a group by statement which works fine however, one of the "group" is null. How do I return "Unknown" for that row? For example: 10000 A 9999 B 9990 C 4 etc. First ...
0
votes
0answers
31 views

Import CSV script executes and returns NULL on localhost

I have been running this import script on localhost to import CSV fields to my database. The script executes and returns the rows as NULL and no data is inserted. It would be great to get another set ...
1
vote
1answer
30 views

cant order NULL value properly (mysql)

So I got this view in mysql : CREATE VIEW vote_question_totals AS SELECT question_id , SUM((vote_question_type)='1') question_total_plus , SUM((vote_question_type)='-1') question_total_minus , ...
0
votes
0answers
16 views

Avoid obvious potential NULL-values [duplicate]

I've been searching through the different threads discussing NULL values and how it is good whether using it or not. I cant find any concrete examples of how to design a relational data model to avoid ...
1
vote
1answer
128 views

How can I insert records 'into null' in Oracle?

I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL ...
1
vote
1answer
164 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
3
votes
2answers
1k views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
0
votes
1answer
312 views

TSQL change column constraint between null and not null

I am doing a exercise to change a column's constraint between null to not null. Table name: Production.categoriesTest. Please review my question and give me suggestion on what I did wrong and how to ...
3
votes
1answer
9k views

“Cannot insert the value NULL”, but there is no NULL values! [closed]

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table The Person.LastName column has a NOT NULL constraint. When I execute this code: CREATE ...
1
vote
1answer
300 views

Database Normalization: A Case Of The NULLs [duplicate]

I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL ...
0
votes
1answer
268 views

sql-server optimize query with lots of null checks

I have a query such as: select something from somewhere join stuff on condition join more_stuff on condition where somewhere.sometime is null and somewhere.someone is null and stuff.whatever is null ...
1
vote
1answer
546 views

How different clauses treat Null/Unknow in SQL Server 2012?

(relatively new to SQL and SQL Server) I am reading SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. Autor states that: SQL has different treatments for UNKNOWN in different language ...
11
votes
3answers
490 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
0
votes
1answer
573 views

MySQL warnings while loading integer data into table (SOLVED)

For some reason, after loading a large amount of data into the following table (from a single file), all of the values register as 0. CREATE TABLE secondTable ( freq int NULL, w1 int NULL, w2 int ...
0
votes
0answers
51 views

MySQL - query join returning zero recs from table with data

I've provided a file that has the table structure and some sample data that I'm working with. I don't understand why I'm getting nulls from a joined table, where there is data. Here's my query ...
1
vote
0answers
93 views

How to merge rows based on how much data they have?

I am cleaning data and I formed a table something like this: file | product | color | product_id --------------------------------------------- one.csv pipe blue null ...
0
votes
2answers
162 views

Accept null value on parent column in multipart relationship

EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer. ...
1
vote
0answers
74 views

Dealing with null values in non-nullable columns in a NoSQL database

If I have an existing table in a NoSQL database cluster and I want to add a new column that should never return null, how do I handle it in my application code? I can update all exist records and ...
1
vote
1answer
896 views

How to update where subquery value is null in SQL Server

I am attempting to update a column if another value from a subquery is less than a certain value or null: UPDATE Customer SET PriceClassID = 'A' FROM (SELECT custid, SUM(curybaldue) as last_sum FROM ...