Questions tagged [null]

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

Filter by
Sorted by
Tagged with
-2
votes
1answer
22 views

Joining 3 tables together and selecting the null elements without using is not null

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 =...
2
votes
2answers
75 views

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 postgres can (hopefully) use the index while doing the table scan while locking the table, so that the ...
0
votes
2answers
19 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
48 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
34 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
44 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
12 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
17 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
34 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 ...
3
votes
2answers
278 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 ...
0
votes
0answers
18 views

How to get latest record of partition when Field1 & Field2 is not null or get latest record where one is null and 2nd latest where same field isn't?

Hard question to verbalize in the title with character constraint, sorry. I have Table1 with the fields PartitionField, ImportantField1, ImportantField2, DateField. ImportantField1 or ImportantField2 ...
1
vote
0answers
25 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
33 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
58 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
17 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
92 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
73 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 ...
0
votes
1answer
56 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
31 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
23 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 ...
3
votes
1answer
98 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
59 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
154 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
38 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 ...
-1
votes
1answer
21 views

null value in postgres functions

I am new in postgres and i found this issue with this simple function CREATE OR REPLACE FUNCTION "public"."TEST"("disponibile" int2=0, "extra" int2=0, "ordini" int2=0, "prenotata" int2=0) RETURNS "...
2
votes
0answers
54 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
1
vote
3answers
151 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
6
votes
2answers
574 views

NULL value causes blank row in SELECT results for text concatenation

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || ...
3
votes
2answers
141 views

How to understand “NULL NOT IN” when the set is empty?

The SQL in this question is for Oracle. But you can simply remove FROM dual to make it work on SQL Server (which returns the same result as Oracle). I have the following SQL: SELECT 1 FROM dual ...
7
votes
1answer
395 views

Multiple BIT datatype columns in the same table

Consider the following information coming from Microsoft SQL docs pages: The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the ...
1
vote
1answer
44 views

Why null is getting compared with null in the join condition in SQL Server 2016?

It's a simple self join I am trying to do on below data and the O/P I am getting after running following query (This is not the complete output, just showing the null part of it) select a.col1, a....
12
votes
3answers
730 views

Increase speed altering column on large table to NON NULL

I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
0
votes
2answers
811 views

MySQL 8.0 cannot insert NULL values to date field

I'm trying to insert a data from file to MYSQL 8.0 using the InnoDB engine. I'm using this query: LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat' INTO TABLE Item FIELDS TERMINATED BY '|'; ...
0
votes
1answer
49 views

Comparing nullable column values using “IS NULL” versus “= NULL”

I'm dealing with nullable values in a column of a table, i'm wondering why this: with cte as ( select 'c1'::text as c1,null::text as c2 union select 'c2'::text as c1,null::text as c2 ) ...
1
vote
1answer
1k views

LEFT JOIN and handling NULL Values [closed]

I have two tables. Table 1: A B ------ 1 'A' 2 'B' 3 'C' Table 2: A C ---------- 2 15 3 20 I'm using the following query: SELECT table1.A, table1.B, table2.C FROM table1 ...
0
votes
3answers
138 views

MySQL, IF statement, variables, and NULL

After struggling for too long with something that should have been simple, I had a question to ask, but while writing it, I kept looking for solutions and I finally found the solution. I am posting it ...
-1
votes
1answer
52 views

Replace NULLs with value

Here is my temporary table: CREATE TABLE #test123( c1 float, c2 float, c3 float, c4 float, c5 float ); INSERT #test123(c1,c2,c3,c4,c5) VALUES (2,3,NULL,1,2), (2,NULL,NULL,...
13
votes
3answers
4k views

How to get the last not-null value in an ordered column of a huge table?

I have the following input: id | value ----+------- 1 | 136 2 | NULL 3 | 650 4 | NULL 5 | NULL 6 | NULL 7 | 954 8 | NULL 9 | 104 10 | NULL I expect the following ...
2
votes
2answers
342 views

How can the concatenatation of a text and a NULL value return a non-null result?

Assert 1 The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the ...
0
votes
1answer
107 views

Display all fields with null values in a particular row in SQL Server

I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table. Some thing like: select 'all columns with NULL' from table_xyz where ...
2
votes
1answer
292 views

Conditional ORDER BY based on NULL values in the column

I have a table like this. table(student_name nvarchar(20) not NULL, grades int). When I write select * from table order by grades asc It shows this student_name | grades --------------------- ...
10
votes
1answer
3k views

Why do I need to cast NULL to column type?

I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this: (Both the active and core fields are of type boolean) UPDATE fields as t set "active" =...
0
votes
3answers
19 views

MYSQL: Display NULL value when foreing key is NULL

I have the following database structure in MYSQL: CREATE TABLE xs_bugtracking ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `product` VARCHAR(64) NOT ...
2
votes
1answer
1k views

PostgreSQL performance with (col = value or col is NULL)

This question deals with PostgreSQL 9.5 query performance. The table is: CREATE TABLE big_table ( id integer NOT NULL, flag bigint NOT NULL, time timestamp with timezone NOT NULL, val ...
0
votes
1answer
517 views

WHEN MATCHED clause fails if there's a NULL in the ON columns [duplicate]

I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns: MERGE PEPS AS peps USING (Lots of Cols.. ...
1
vote
0answers
38 views

Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] ...
1
vote
0answers
350 views

Allow Null Columns vs separate 1-to-many tables

Perhaps similar questions have been asked, but this is re having null columns. I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better ...
3
votes
1answer
417 views

Is there a function which the opposite of NULLIF?

I am aware of the NULLIF function which compares two values and returns NULL if they match. Is there a function which will compare two values and return NULL if they don’t match? That is filter out ...
1
vote
2answers
112 views

Replace Null values with values from the same column by matching values in a different column

I have a table with some null values in column "date": platform date id --------------------------- web 2018-10-10 1 mob 1 mob 1 web ...
-2
votes
2answers
88 views

How to display null records/rows from table

For ex: I have a record with name = "sga_target", and no record exists where name = "_exafusion_enabled". I still want to display a row with "_exafusion_enabled" as name and NULL as value, without ...

1
2 3 4 5 6