A database concept used to represent missing, unknown or inapplicable data.
1
vote
2answers
20 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
0answers
27 views
returning records with null [closed]
This will only return records with values. I need all records to display, even if they are zeros. I though coalesce would work but maybe I'm putting in the wrong place.
select district_id, school_id,...
-2
votes
2answers
62 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
30 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
17 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
17 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
35 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
104 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
30 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
76 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
249 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
178 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
77 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 ...
1
vote
4answers
309 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
190 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
36 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
178 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 ...
5
votes
1answer
315 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
18 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
46 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
1k 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
519 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
34 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 ...
3
votes
2answers
1k 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
158 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
355 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
59 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
40 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
83 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
83 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
419 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
69 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
71 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
54 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
59 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-...
1
vote
1answer
221 views
How to handle null value number DataWarehouse [duplicate]
I have a question.
I'm developing a Data Warehouse, I retrieve the data from my reconciled level, this in order to keep updated my Data Warehouse, but i am not sure how to handle some numeric fields ...
1
vote
3answers
82 views
Array to string sometimes concatenates fewer values
array_to_string(array_agg(array_to_string(array[
highlights,exceptions,inter_note,inter_status::text
,comp_note,comp_status::text,hard_note,hard_status::text]
,'|#|'::...
4
votes
1answer
33 views
Why does null and true evaluate to null but null or true evaluates to true?
Why does null and true evaluate to null in PostgreSQL but null or true evaluates to true?
I would have thought using null with and or or would've evaluated to null in both cases?
5
votes
2answers
102 views
Replacing NULL values with one string from provided list
I am trying to replace NULL values in table by using SELECT with one (randomized?) string from the set that I provide to the compiler.
Example:
|id | date |
+---+------+
| 1 | 2017 |
| 2 | NULL |...
1
vote
1answer
50 views
Having two entities that are the same except for 1 distinct attribute
In an example scenario for a database design assignment, a company provides hotel rooms (split into two entities - Hotel and Room) and apartments. So the database will contain the following entities:
...
3
votes
1answer
61 views
SSIS / SSAS: Handling Age Columns
I have some columns representing Age in a table, taking values from 0 to 100, but also -1 for 'Missing Value'. I don't want to store them as strings to be able to find average age etc., but SSAS turns ...
3
votes
1answer
405 views
SSIS - SSAS: Missing data handling
I have a solution in SSDT with a SSIS and a SSAS project, with missing values in both numeric and text columns.
Is it a good practice converting all the missing values of these columns to NULL during ...
5
votes
2answers
145 views
How to capture missing information in SQL Server tables
I have a few tables with nullable columns in a SQL Server database. Those nullable attributes are either not supplied at the time of row creation or they are not applicable in some cases.
I realized ...
0
votes
1answer
91 views
MsMerge_genhistory has alot of rows with pubid = null
I have a merge replication and I am worried that the cleanup of metadata might not be enough. I have a retention period of 60 days and I can see thet the metadatacleanup-job do remove rows i ...
0
votes
1answer
1k views
Concatenate with NULL values ( Should not replace NULL )
After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is:
Table:
create table nulos ( nome varchar(max))
alter table nulos add ...
3
votes
1answer
2k views
How do I select arrays that are not empty?
Why is this so tricky, what is token set to that it isn't equal to null nor an empty string?
SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword';
lexemes
---------
{}
{}
{}...
8
votes
1answer
166 views
Can't SQL Server store NULLs in fixed-length columns?
I came across this statement in the official Oracle documentation:
In Microsoft SQL Server, only columns with variable-length data types
can store NULL values. When you create a column that ...
-1
votes
1answer
41 views
Combining two tables with keys that contain null
I have two tables that I am joining
|id |profit|
| 1 | 1234 |
| 2 | 1345 |
| 4 | 1454 |
| 5 | 1254 |
and another table
| id | x loss | y loss |
| 2 | 34312 | 4354 |
| 3 | 35614 | 4365 |
| 4 ...
3
votes
2answers
567 views
CHECK constraint for array column to verify length > 0
I'm playing with postgres table validation rules and trying to set a CHECK constraint for an array column. An idea is to allow only arrays with length > 0.
Here is how I want to implement it:
create ...
3
votes
1answer
58 views
Why does NULL ordering impact a btree scan in PostgreSQL?
In this answer jjanes says that NULL ORDERING contrary to the index declaration ordering can force a quick scan. Turns out he's right. I want to know why that is though. If you a btree with nulls last,...
