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-...
