Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [null]

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

-1
votes
0answers
15 views

Script to fix MySQL columns to work with strict-mode [on hold]

I'm trying to build a script to generate ALTER TABLE queries so that across whole schemas, columns are adjusted to be compatible with MySQL strict-mode.. I want it to be a simple as possible, making ...
0
votes
2answers
24 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 ...
0
votes
2answers
25 views

Mysql query wont work with null values in fields [closed]

My query works fine, it retrieves everything when there is no NULL values on the following fields, (company_id_fk, country_id_fk, province_id_fk, location_id_fk and division_id_fk), I have tried using ...
-1
votes
1answer
44 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,...
9
votes
3answers
1k 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 ...
1
vote
2answers
48 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
47 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 ...
1
vote
1answer
25 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 --------------------- ...
0
votes
0answers
21 views

Pagination using seek-method with nullable columns, sharing a composite index with ORDER BY

I have the following table: CREATE TABLE users( id text PRIMARY KEY, -- actually a uuid organization_id text NOT NULL, lname text NOT NULL, fname text NOT NULL -- other irrelevant fields ); ...
9
votes
1answer
251 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
17 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
72 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
57 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
84 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
121 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
35 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
70 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 ...
0
votes
1answer
38 views

MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls

I have the following table definition: CREATE TABLE `invite` ( `id` varchar(255) CHARACTER SET utf8 NOT NULL, `email` varchar(255) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `...
1
vote
1answer
26 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
0
votes
1answer
34 views

IS NOT DISTINCT FROM vs row-wise equality with =

Looking at the below query, SELECT null IS NOT DISTINCT FROM null AS indf, null = null AS eq; indf | eq ------+---- t | From this we can see the result of the IS NOT DISTINCT FROM is true, ...
0
votes
1answer
66 views

Using NULL values for foreign key with normalization

I am creating a table as follows: Building (PK: buildingID) Area (PK: areaID, FK: buildingID) Room (PK: roomID, FK: buildingID, FK: areaID) All rooms are in a building. Some rooms can be in an area ...
1
vote
3answers
164 views

Returning 0 if value is NULL

SELECT p.id , ig.NumReceived , og.NumShipped , coalesce( (p.StartingInventory - og.NumShipped + ig.NumReceived), p.StartingInventory ) as OnHand , p.ProductName , p....
0
votes
3answers
55 views

Difference of two columns with possible NULL cells

I need to sort a table by the difference of two columns as SELECT (first - second) AS dif FROM table1 ORDER BY dif DESC but the two columns can be NULL. In this case, it returns the error: ...
2
votes
3answers
91 views

Select the greatest value from each column, even if one of the values is null

I have a road_age_test table: create table road_age_test ( surface_year int, base_year int ); insert into road_age_test (surface_year, base_year) values (10,20); insert into road_age_test (...
1
vote
1answer
796 views

How to represent NULL in relational algebra?

In the 3rd part of the question below: Study the tables given below and write relational algebra expressions for the queries that follow. STUDENT(+ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR) ...
0
votes
1answer
2k views

Does PostgreSQL index null values? [closed]

If I have a table like so: Food ----------------- name | price | x Let's say I create an index on name & price, but the database has a bunch of null values for both name & price in some of ...
6
votes
2answers
90 views

Deriving a computed column based on several nullable columns

I'm a bit of an SQL novice. I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks. The syntax used to create the auto generated ...
2
votes
4answers
746 views

Can @parameter IS NULL slow down the execution of query?

Option 1: CREATE PROCEDURE [dbo].[GetStudents] @MinimumAge int = NULL AS select * from Students s where @MinimumAge is null or s.Age >= @MinimumAge Option 2: CREATE PROCEDURE [dbo].[GetStudents]...
5
votes
1answer
450 views

How to add a named constraint in Oracle SQL?

I have a table. I need to add a NOT NULL constraint to one of the columns. This is my best attempt at a solution: alter table clookup add CONSTRAINT lookup_9 NOT NULL (clookup_col) This produces ...
1
vote
1answer
349 views

SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records

The following was one of the practice questions in the online course i took on udemy for SQL. I have a table 'emp' and consider the below names in the query map to their corresponding columns in ...
1
vote
0answers
271 views

Apache Phoenix Upsert not working while trying to update null/empty columns

Step 1: Update a column with null value. Upsert into abc.tablename (Rowkeycombo1, Rowkeycombo2, columnname) values (123,to_date('1988-08-06'),null); This works fine and the column then appears to ...
6
votes
1answer
940 views

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

Due to business logic, we need a new column in a table that is critical to ensure is always populated. Updating existing references that insert/update is difficult but doable, but ensuring future code/...
-1
votes
1answer
19 views

Table for factory data along with user data

When creating master tables which are usually distinguished by a PK, say user_id, I'm often faced with the situation where I want to have a few pre-defined rows in the table. I want data from these ...
-2
votes
1answer
49 views

HOW to work with NULL in a NOT NULL column?

I have a column that is specifyed as (int, not null). I've been trying to insert NULL for the missing records of that column, can anyone give me a hint?
0
votes
3answers
3k views

SQL left join returns null column [closed]

I am trying to do a LEFT JOIN on two tables, where one table has a datetime column, and the other table has a date column. As I am working in SQL Server, I used CONVERT to change the datetime to date. ...
3
votes
2answers
975 views

Count NULL values per row

I would like to count the number of null's present per row in a table without enumerating column names. For example: WITH t as (VALUES (NULL ,'hi',2,NULL,'null'), ('' ,'hi',2,3,'test'), (NULL ,'...
1
vote
1answer
38 views

Not Null Fields and Unmatched Number of Columns

I am in charge of a task of migrating data from MySQL to PostgreSQL. I have already migrated the MySQL database to PostgreSQL with the help of pgloader. My problem is I need to insert the data from ...
5
votes
2answers
3k views

Conditional string concatenation in PostgreSQL

I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new ...
1
vote
1answer
482 views

Transpose Rows to column

Can anyone Please help me to transpose below result +------+------------+-------+ | SVR | dates | count | +------+------------+-------+ | SRV1 | 03/25/2018 | 1 | | SRV1 | 04/06/2018 | 59 ...
0
votes
2answers
967 views

COALESCE with cast

I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get: ...
0
votes
3answers
74 views

How to show a column as NULL if it doesn't exist in match?

I have a query as shown below. In this query I am trying to get the result of orders which don't have messages (which I thought as null). But I was expecting that this query would produce a NULL in ...
0
votes
1answer
61 views

How to get 0 in the null value fields

select * from ( select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster where date between '2016/12/31' and '2017/12/31' group by type,month(date) ) src ...
-1
votes
2answers
99 views

Sorting a Date Column in Microsoft SQL Server Management Studio

I have a table (master) with the following columns: Order # (integer) Location (varchar) Est Start Date (date) I want to return all rows. I want the rows sorted by Est Start Date, in ascending order,...
0
votes
1answer
409 views

Keep default value as 0.0 for 0 or NULL for numeric column

I have a numeric column Number(28,3) in which I have to insert value as 0.0 , whenever I encounter 0 or NULL/blank at the time of data insertion. I have tried below : NVL(col_name,0.0) but it's ...
4
votes
2answers
813 views

SQL Server: specifying a unique optional column

SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table. For example: create table employees ( id int identity(1,1) primary key, givenname nvarchar(...
2
votes
0answers
85 views

Inserting two NULLs values into an UNIQUE column in SQLite

I use: sqlite-jdbc-3.21.0.jar Error received: SQLite_CONSTRAINT_UNIQUE] A UNIQUE constraint failed: companies.ncf I ask a few things to the user, there is two fields that are optional to complete ...
6
votes
1answer
111 views

Join on NULL Key Column Optimizing as Table and Index Scans

I have a question about this query plan. We have a table in a test environment, Order_Details_Taxes, that has 11,225,799 rows. This table has a column, OrdTax_PLTax_LoadDtl_Key, which is NULL on ...
1
vote
1answer
69 views

Firebird: table and null data

I'm working with Firebird database. I have a big table with many thousand of rows. Each line records the status of an object at regular intervals for many objects. I need to register new properties ...
4
votes
1answer
97 views

Why does PostgreSQL create an additional NULL bitmap if all fields after the 8th are NOT NULL?

I don't refer to the 1 Byte null bitmap included in the row header that could be used to flag null values for any of the first 8 columns. See: https://stackoverflow.com/questions/12145772/do-...