Ошибка при обработке запроса null - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

Автор Allen Browne, ноябрь 1999 г.
http://allenbrowne.com/casu-12.html
Перевод: А. Артамонов, октябрь 2011г.

Вот некоторые распространенные ошибки обращения с Null -ами, которые допускают новички. Если у вас нет ясности насчет Null’ов, сначала прочитайте Nulls: Do I need them?

Ошибка № 1: Null-ы в критериях

Если вы вводите условии отбора под полем в конструкторе запросов, он возвращает только совпадающие с образцом записи. Null-ы при этом исключаются из отбора.
Например, скажем, у вас есть таблица компаний и адресов. Вам нужны два запроса: один, который выдает местные компании, другой ― все остальные. В строке условий отбора в первом запросе под полем Город вы вписываете:
«Бобруйск»
и во втором запросе:
Not «Бобруйск»
Неправильно! Ни один запрос не включит записи, у которых город Null.

Решение

Используйте Is Null. Во втором запросе, чтобы достичь желаемого результата, условия отбора должны выглядеть как:
Is Null Or Not «Бобруйск»
Примечание: Запросы DDL (языка определения данных) обращаются с Null-ами иначе. Например, Null-ы учитываются в таком типе запроса:
ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (99 < (SELECT Count(*) FROM Table2 WHERE Table2.State <> ‘TX’));

Ошибка № 2: Null-ы в выражениях

Результатом вычислений, включающих Null, обычно является Null. Например, новички иногда вводят выражение в источник данных текстового поля, чтобы вывести остаток к оплате:
=[СуммаКОплате] — [СуммаУплаченная]
Проблема в том, что если не было оплачено ничего, СуммаУплаченная является Null-ом, и в текстовом поле ничего не отображается.

Решение

Используйте функцию Nz(), чтобы указать значение для Null-а:
= Nz([СуммаКОплате], 0) — Nz([СуммаУплаченная], 0)

Ошибка № 3: Null-ы во внешних ключах

В то время, как Аксесс запрещает Null-ы в первичных ключах, он разрешает Null-ы во внешних. В большинстве случаев стоит явно запрещать эту возможность, чтобы избежать висящих ссылок.
В типичной таблице для накладных, строки накладной хранятся в таблице НакладнаяСтроки, соединенной с таблицей Накладные по НакладнаяКод. Вы создаете связь между Накладные.НакладнаяКод и НакладнаяСтроки.НакладнаяКод с поддержкой ссылочной целостности. Этого недостаточно!
Если вы не установили свойство Обязательное поля НакладнаяКод в таблице НакладнаяСтроки на Да, Аксесс разрешает Null-ы. Чаще всего это случается, когда пользователь начинает добавлять строки в подформе, не создав сперва саму накладную в основной форме. Так как у этих записей нет соответствующей им записи в основной форме, эти висящие записи больше нигде не показываются снова. Пользователь уверен, что программа их удалила, хотя на самом деле они находятся в таблице.

Решение

Всегда выставляйте значение свойства Обязательное на Да в конструкторе таблиц, если только вам не нужна возможность иметь Null-ы во внешних ключах.

Ошибка № 4: Null-ы и типы, отличные от Variant.

В Visual Basic единственным типом данных, который может содержать Null является Variant. Когда бы вы ни присваивали значение поля в переменную с типом не-Variant, всегда нужно учитывать возможность, что в поле может содержаться Null. Посмотрите, что в этом коде модуля формы может пойти не так:
       Dim strName as String
       Dim lngID As Long
       strName = Me.Отчество
       lngID = Me.КодКлиента
Когда поле Отчество содержит Null, попытка присвоить Null переменной строкового типа породит ошибку.
Аналогично, присвоение значения КодКлиента числовой переменной может вызвать ошибку. Даже если КодКлиента является первичным ключом, код небезопасен: первичный ключ содержит Null в новой записи.

Решения

(a) Используйте тип Variant, если вам необходимо работать с Null-ами.
(b) Используйте функцию Nz(), чтобы указать значение вместо Null. Например::
strName = Nz(Me.Отчество, «»)
lngID = Nz(Me.КодКлиента, 0)

Error 5: Сравнение с Null

Выражение:
If [Фамилия] = Null Then
будет бессмысленно, так как никогда не может быть истинно. Даже если фамилия является Null-ом, VBA полагает, что вы спросили::
Является ли Неизвестное равным Неизвестному?
и всегда отвечает: “Откуда мне знать, равны ли ваши неизвестные друг другу? Здесь мы опять наблюдаем распространение Null: результат не является ни истинным ни ложным.

Решение

Используйте функцию IsNull():
If IsNull([Фамилия]) Then

Ошибка № 6: Забыли, что Null ни Истина ни Ложь.

Выполняют ли эти две конструкции одну и ту же работу?
(a)     If [Фамилия] = «Иванов» Then
           MsgBox «Это Иванов”
       Else
           MsgBox «Это не Иванов»
       End If

(b)     If [Фамилия] <> «Иванов» Then
           MsgBox «Это не Иванов»
       Else
           MsgBox «Это Иванов”
       End If
Когда фамилия является Null-ом, эти два куска кода противоречат друг другу. В обоих случаях, первая часть If не срабатывает, и начинается выполнение Else, что приводит к противоречащим друг другу сообщениям.

Решения

(a) Учитывайте все три возможных результата сравнения — True, False, и Null:
       If [Фамилия] = «Иванов» Then
           MsgBox «Это Иванов”
       ElseIf [Фамилия] <> «Иванов» Then
           MsgBox «Это не Иванов”
       Else
           MsgBox «Мы не знаем, Иванов это или нет»
       End If
(b) В некоторых случаях, функция Nz() позволит обработать два случая разом. Например, рассматривать Null и нулевую строку одинаково:
       If Len(Nz([Фамилия],»»)) = 0 Then

Источник

Время на прочтение
8 мин

Количество просмотров 20K

Навскидку многим кажется, что они знакомы с поведением NULL-значений в PostgreSQL, однако иногда неопределённые значения преподносят сюрпризы. Мы с коллегами написали статью на основе моего доклада с PGConf.Russia 2022 — он был полностью посвящён особенностям обработки NULL-значений в Postgres.

NULL простыми словами

Что такое SQL база данных? Согласно одному из определений, это просто набор взаимосвязанных таблиц. А что такое NULL? Обратимся к простому бытовому примеру: все мы задаём друг другу дежурный вопрос: «Как дела?». Часто мы получаем в ответ: «Да ничего…» Вот это «ничего» нам и нужно положить в базу данных — NULL: неопределённое, некорректное или неизвестное значение.

Допустим, вы суммируете две колонки, и в сотой по счёту записи наткнулись на NULL. Что тогда делать? Или возвращать ошибку, потому что так нельзя, или всё-таки как-то выполнить сложение и идти дальше. Сообщество решило в пользу второго варианта и закрепило это в стандартах языка SQL. Также договорились, что данные любого типа могут оказаться NULL, написали специальные функции и операции для обработки NULL-значений.

NULL может оказаться в столбце с любым типом данных и попасть на вход к любому оператору или функции. Соответственно, все операторы и функции как-то обрабатывают NULL, но результат обработки иногда оказывается неожиданным.

Какие значения не являются NULL?

Давайте теперь посмотрим, что не есть NULL. Ноль — это просто ноль, не NULL. Пустая строка — это пустая строка в Postgres, в отличие от Oracle. Пустой массив, пустой JSON, массив NULL-значений, пустой диапазон — это не NULL. Сложные типы, включающие NULL, уже не являются NULL.

Есть, правда, одно исключение: запись, собранная из NULL-значений, является NULL. Это сделано для совместимости со стандартом языка SQL. Однако, «под капотом» Postgres функции и операторы считают запись, состоящую из NULL-значений, NOT NULL. Ниже приведены результаты обработки такой записи для некоторых из них:

сount(row(NULL)) посчитает такую запись;

num_nulls(row(NULL)) выдаст ноль;

row(NULL) IS DISTINCT FROM NULL выдаст TRUE.

Ещё удивительнее пример с записями, содержащими NULL:

row(NULL::int, ‘Bob’::TEXT) IS NULL ожидаемо выдаст FALSE, но

row(NULL::int, ‘Bob’::TEXT) IS NOT NULL тоже выдаст FALSE!

Тем не менее, это поведение не является багом и описано в документации.

Операции с NULL

Почти все бинарные операции с NULL — сложить, вычесть, умножить, конкатенировать — дают на выходе NULL. С этим стоит быть осторожнее. Если вы к строке или к JSON конкатенируете что-то, оказавшееся NULL, то получаете на выходе NULL. А если вы ещё и сделали UPDATE в базу данных, выйдет совсем нехорошо.

Тем не менее, логическая операция TRUE OR NULL на выходе даёт TRUE. FALSE AND NULL даёт в результате FALSE. То есть существуют некоторые исключения из общего правила.

Операции сравнения

Операции сравнения — больше, меньше, больше или равно — c NULL на выходе дают NULL. При этом и сам NULL не равен самому себе. Впрочем, в PostgreSQL есть параметр transform_null_equals, который по умолчанию выключен. Если его включить, то NULL будет равен NULL.

Для проверки любого значения на NULL в Postgres предусмотрен специальный оператор — … IS NULL, … IS NOT NULL. Также может быть непривычно, что при сравнении булевых переменных с NULL или при применении оператора равенства помимо значений TRUE и FALSE возможно ещё и неизвестное значение. При этом оператор IS (NOT) UNKNOWN — это аналог IS (NOT) NULL для булевых переменных.

Операторы IS TRUE или IS FALSE для булевых переменных дают или TRUE, или FALSE. NULL в результате их применения получиться не может. Использование оператора IS TRUE позволяет писать более надёжный код, чем обычное сравнение = TRUE, которое может выдать не учтённое программистом NULL-значение и пойти «не туда».

Что если нам нужно сравнить два значения X и Y, считая, что NULL-значения равны друг другу? Можно самому написать конструкцию из логических операторов, но существует уже готовый оператор X IS (NOT) DISTINCT FROM Y. Правда, планировщик PostgreSQL плохо понимает этот оператор и может выдавать долгие планы выполнения для запросов с ним.

Cпециальные функции для работы с NULL

Обратимся к специальным функциям для работы с NULL. Всем известная coalesce возвращает первый NOT NULL аргумент. Есть nullif, есть num_nulls — этой функции можно дать сколько угодно аргументов, она посчитает количество NULL-значений. С помощью функции num_nonnulls можно посчитать NOT NULL значения.

Как правило, функции с произвольным числом аргументов игнорируют NULL. Такие функции, как greatest, concat его просто проигнорируют. При этом функция создания массивов включит NULL-значение во вновь образованный массив, за этим надо следить.

NULL и агрегатные функции

Что касается агрегатных функций, то array_agg, json_agg включают NULL в агрегат, а конкатенация строки не может вставить NULL-значение в середину строки, и поэтому она NULL игнорирует.

Статистические функции min, max, sum игнорируют NULL, а вот с выражением Count всё хитро. Count по конкретному полю посчитает только строки, где выражение NOT NULL, а вот Count со звёздочкой посчитает всё, включая NULL-значения.

Что со всем этим делать? Можно почитать в справке или потестировать, как функция обрабатывает NULL-значения. А лучше использовать выражение FILTER и в явном виде исключить все NULL-значения.

NULL и пользовательские функции

Теперь о пользовательских функциях. При создании пользовательской функции по умолчанию включен режим CALLED ON NULL INPUT, то есть при наличии NULL среди аргументов функция вызовется и будет обрабатывать это значение. Если вам это не нужно, можно использовать RETURNS NULL ON NULL INPUT либо STRICT — в этом случае функция, обнаружив NULL хотя бы в одном аргументе, сразу возвращает NULL и дальше вообще не думает — для экономии времени.

Многие системные функции в PostgreSQL определены именно как STRICT, поэтому стали возможны некоторые математические казусы. Например, NULL можно разделить на ноль, и в результате вы получите NULL — вместо ошибки деления на ноль. NULL в степени ноль тоже является NULL, хотя в математике любое число в нулевой степени, даже если это сам ноль, даёт единицу. Непонятно, правильно ли такое поведение с философской точки зрения, но вроде пока никто не жаловался.

Группировка и сортировка

Если говорить о группировке, то она считает все NULL-значения одинаковыми, так как это делает оператор IS NOT DISTINCT FROM. При сортировке есть специальные подвыражения, в которых можно указать NULLS FIRST или NULLS LAST. По умолчанию выбирается NULLS LAST, то есть считается, что неопределённые значения больше всех остальных чисел.

Сортировка работает так при создании выборки, индекса, в агрегатных функциях и оконных функциях.

NULL и записи

Когда мы формируем запись из нескольких значений, то сравниваются все NOT NULL значения. Если найдётся различие, то результат будет FALSE. Если все NOT NULL значения совпадают, и нашёлся NULL, то будет NULL.

Сравнение на больше/меньше выполняется по другим правилам. Как только попадётся не совпадающее значение, тогда оно будет больше или меньше, а если обнаружится NULL, то будет NULL.

NULL и диапазоны

С бинарными операциями разобрались, но что если у нас тернарная операция? Например, SELECT NOW BETWEEN NULL AND NULL. Получится, ожидаемо, NULL.

Однако, точно такое же выражение, сформулированное через диапазоны, неожиданно даёт TRUE. Да, с точки зрения Postgres здесь и сейчас мы находимся в неопределённом промежутке времени!

Согласно стандарту SQL, все диапазонные типы — не только временные, а все вообще —воспринимают границу NULL как бесконечность.

Я полюбопытствовал и выяснил, что промежуток от минус бесконечности до плюс бесконечности входит в промежуток от NULL до NULL, а обратное — неверно.

Выходит, что NULL здесь даже несколько больше, чем бесконечность.

Также я попытался проверить, входит ли NULL в промежуток от минус бесконечности до плюс бесконечности. Оказалось, что это неизвестно. Это контринтуитивный для меня момент: мне казалось, что полный диапазон значений от минус до плюс бесконечности должен включать в себя любое значение, в том числе и неопределённое. Но нет, в PostgreSQL это не так.

Откуда в запросах появляются NULL-значения?

Во-первых, они попадают в базу данных при вставке или обновлении и продолжают храниться в столбцах таблиц.

Во-вторых, они записываются в базу как результат подзапроса не нашедшего ни одной строки — в этом случае подзапрос вернёт вам NULL.

В-третьих, NULL-значения могут появляться в результате операции объединения LEFT JOIN.

В-четвёртых, NULL-значения появлются как результат некоторых функций при некоторых условиях.

В-пятых, их можно создать вручную, например, при использовании конструкции CASE. В каком-то хитром запросе вы можете указать, что при определённых условиях получится неизвестное значение.

Структура базы данных и NULL

Во-первых, можно запретить хранение NULL-значений в столбце. Есть специальное ограничение (constraint) NOT NULL. Крайне рекомендую так и поступать — всегда запрещать хранение NULL-значений, если только вы не планируете хранить и обрабатывать NULL именно в этом столбце.

При определении ограничения (constraint) тоже есть одна особенность: если условие возвращает NULL, это считается допустимым, и такая запись может быть вставлена.

Например, ограничение Foreign key позволяет в дочерней таблице вставить запись со ссылкой, которая является NULL. Это будет допустимо.

Ограничение CHECK (price > 0) даст вам вставить в таблицу поле для Price со значением, равным NULL.

Ограничение unique позволяет создать несколько записей со значением NULL. Правда, в PostgreSQL 14 уже появилось специальное «заклинание», которое может запретить несколько записей с NULL.

Как NULL хранится внутри записи БД?

NULL вообще не хранится среди полей записи, но если там есть хотя бы одно NULL-значение, то создаётся битовая карта неопределённых значений, которая называется t_bits. Стоит запомнить, что самое первое NULL-значение влечёт за собой создание такой карты и расход некоторого количества места.

Правда, все дополнительные NULL-значения в этой записи вы уже храните бесплатно и достаточно компактно. Если вы действительно экономите каждый байт в своей базе данных, хранение NULL — правильное занятие.

NULL и индексы

Postgres хранит NULL-значения в btree-индексах. Этим он отличается от Oracle. Также Postgres может использовать такой индекс при поиске записей по NULL-значению.

Тем не менее, хранение NULL-значений в индексе для вас бесполезно, если у вас нет такого типа запросов (они довольно редки, и их можно проверить в представлении pg_stats_statements).

Также пользы не будет при большом количестве NULL-значений в индексе, и следовательно, плохой селективности. В этом случае будет дешевле сделать последовательное сканирование таблицы, а не возиться с индексом.

Вот пример случая с большим числом NULL-значений в таблице. У вас есть внешний ключ (foreign key) на какую-то родительскую таблицу, но реальная ссылка используется редко, и в основном в дочерней таблице NULL-значения.

Или же у вас может быть какой-то хитрый функциональный индекс, который часто возвращает NULL. Здесь у нас пример по JSONB-ключу key1, а если у вас в JSON этот ключ встречается нечасто, то и большинство значений будет NULL.

Если у вас NULL-значений много, то вам поможет перестроение индекса на частичный с условием WHERE <ваше поле или выражение> IS NOT NULL. То есть мы просто выкидываем такие значения из нашего индекса. Это принесёт ряд улучшений:

во-первых, сокращается размер индекса на дисках, в том числе на репликах и бэкапах;
во-вторых, уменьшится количество записей в журнал предзаписи (WAL);
в-третьих, освободится место в оперативной памяти и улучшится кэширование.

В моей практике были случаи, когда реально удавалось сократить размер индекса на порядок.

В поиске таких индексов поможет представление pg_stats. В нём есть поле null_frac, которое показывает долю NULL-значений в столбцах таблиц. То есть с помощью этого представления можно определить, есть ли у вас кандидаты на оптимизацию.

Сценарий аккуратного переезда вполне очевиден:

создаёте новый частичный индекс;
по представлению pg_stat_user_indexes убеждаетесь, что запросы переехали на новый индекс;
удаляете старый индекс.

Выводы

Значение NULL может преподнести некоторые сюрпризы, если вы к нему не готовы.
Стоит проверить, как работают с NULL вызываемые вами функции и ваш код.
Запрещайте NULL там, где вы не планируете его использовать явным образом.
Проверяйте ваши индексы на наличие NULL-значений — возможно, за счёт оптимизаций удастся сэкономить некоторое количество памяти и ресурсов процессора.

Полезные ссылки

В статье Хаки Бенита рассматриваются как раз такие переполненные NULL-значениями индексы, есть SQL запрос для их поиска в вашей базе данных и практический результат перестроения.

Классическая статья Брюса Момжиана (Bruce Momjian) под названием «NULLs Make Things Easier?» доступна здесь.

Также рекомендуем ознакомиться с книгой Егора Рогова «PostgreSQL 14 изнутри».

Источник

I’m using the following query:

INSERT INTO role (name, created) VALUES ('Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())

However, I’m not specifying the primary key (which is id). So my questions is, why is sql server coming back with this error:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'id', table 'CMT_DEV.dbo.role'; column does not allow nulls. INSERT fails.
The statement has been terminated.

asked Apr 4, 2012 at 14:33

I’m assuming that id is supposed to be an incrementing value.

You need to set this, or else if you have a non-nullable column, with no default value, if you provide no value it will error.

To set up auto-increment in SQL Server Management Studio:

Open your table in Design
Select your column and go to Column Properties
Under Indentity Specification, set (Is Identity)=Yes and Indentity Increment=1

answered Apr 4, 2012 at 14:36

CurtisCurtis

101k65 gold badges269 silver badges351 bronze badges

use IDENTITY(1,1) while creating the table
eg

CREATE TABLE SAMPLE(
[Id]     [int]  IDENTITY(1,1) NOT NULL,
[Status] [smallint] NOT NULL,

CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)

Nick

1,1583 gold badges24 silver badges36 bronze badges

answered Jan 9, 2018 at 19:41

If the id column has no default value, but has NOT NULL constraint, then you have to provide a value yourself

INSERT INTO dbo.role (id, name, created) VALUES ('something', 'Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())

answered Apr 4, 2012 at 14:40

Andy IrvingAndy Irving

2,6571 gold badge14 silver badges11 bronze badges

Encountered the same issue. This is something to do with your table creation. When you created table you have not indicate ‘ID‘ column to be Auto Increment hence you get this error. By making the column Primary Key it cannot be null or contain duplicates hence without Auto Increment pretty obvious to throw column does not allow nulls. INSERT fails.

There are two ways you could fix this issue.

1). via MS SQL Server Management Studio

Got to MS SQL Server Management Studio
Locate your table and right click and select Design
Locate your column and go to Column Properties
Under Indentity Specification: set (Is Identity)=Yes and Indentity
Increment=1

2). via ALTER SQLs

ALTER TABLE table DROP COLUMN id; // drop the existing ID
ALTER TABLE table ADD id int IDENTITY(1, 1) NOT NULL; // add new column ID with auto-increment
ALTER TABLE table ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id); // make it primary key

answered Dec 15, 2022 at 7:02

Du-LacosteDu-Lacoste

11.3k2 gold badges67 silver badges50 bronze badges

You either need to specify an ID in the insert, or you need to configure the id column in the database to have Identity Specification = Yes.

answered Apr 4, 2012 at 14:38

JupiterP5JupiterP5

3181 silver badge10 bronze badges

As id is PK it MUST be unique and not null.
If you do not mention any field in the fields list for insert it’ll be supposed to be null or default value.
Set identity (i.e. autoincrement) for this field if you do not want to set it manualy every time.

answered Apr 4, 2012 at 14:38

quzaryquzary

2851 silver badge4 bronze badges

You need to set autoincrement property of id column to true when you create the table or you can alter your existing table to do this.

answered Apr 4, 2012 at 14:44

you didn’t give a value for id. Try this :

INSERT INTO role (id, name, created) VALUES ('example1','Content Coordinator', GETDATE()), ('example2', 'Content Viewer', GETDATE())

Or you can set the auto increment on id field, if you need the id value added automatically.

answered May 9, 2017 at 2:04

I had a similar problem and upon looking into it, it was simply a field in the actual table missing id (id was empty/null) — meaning when you try to make the id field the primary key it will result in error because the table contains a row with null value for the primary key.

This could be the fix if you see a temp table associated with the error. I was using SQL Server Management Studio.

answered Nov 1, 2019 at 15:22

WARNING! Make sure the target table is locked when using this method
(As per @OnurOmer’s comment)

if you can’t or don’t want to set the autoincrement property of the id, you can set value for the id for each row like this:

INSERT INTO role (id, name, created)
SELECT 
      (select max(id) from role) + ROW_NUMBER() OVER (ORDER BY name)
    , name
    , created
FROM (
    VALUES 
      ('Content Coordinator', GETDATE())
    , ('Content Viewer', GETDATE())
) AS x(name, created)

answered Apr 16, 2018 at 2:13

robotikrobotik

1,8081 gold badge20 silver badges25 bronze badges

RULE: You cannot IGNORE those colums that do not allow null values, when inserting new data.

Your Case

You’re trying to insert values, while ignoring the id column, which does not allow nulls. Obviously this won’t work.
Gladly for you the «Identity Specification» seems to automatically fill the not nullable id values for you (see selected answer), when you later execute the insert query.

My Case

The problem (while using SSMS): I was having this error when trying to add a new non-nullable column to an already existing table with data. The error I’d got was:

Cannot insert the value NULL into column ‘id_foreign’, table ‘MyDataBase.dbo.Tmp_ThisTable’; column does not allow nulls. INSERT fails.
The statement has been terminated.

The solution:
1. I created the column I needed id_foreign, allowing nulls.
2. I edited/inserted all the required values for id_foreign.
3. Once the values where in place, I went back and unchecked the «Allow Nulls» checkbox. Now the error was gone.

answered Jul 26, 2022 at 14:54

carloswm85carloswm85

1,25813 silver badges21 bronze badges

Источник

I use postgresql and yii2 framework.
Well I got a very interesting error message:

SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 1, null, null, null, null, 1, Demo, , , , 1998-01-01, , , , 345345435453453, , , , , 1, , , f, f, f, f, 10, f, 1, f, f, f, null, null, null, 1470477479, 1470477479, null).

But I checked my Insert command, and there is not «id» column there!

INSERT INTO "advertiser" ("languages", "type", "name", "display_name", "title", "about", "birthday", "gender", "country_id", "county_id", "city_id", "city_part", "street", "house_number", "phone", "public_email", "public_url", "motto", "message", "im_facebook", "im_skype", "has_viber", "has_whatsapp", "has_sms_response", "visible_birthday", "is_checked", "status", "version", "user_id", "created_at", "updated_at") VALUES (NULL, 1, 'Demo', '', '', '', '1998-01-01', 1, NULL, NULL, NULL, '', '', '', '345345435453453', '', '', '', '', '', '', FALSE, FALSE, FALSE, FALSE, FALSE, 10, NULL, 1, 1470477479, 1470477479) RETURNING "id"

So I really cannot understand this error message. I do not find that the Postgres or Yii try to insert a null ID value or what.

By the way here you can find the structure

                                                    Table "public.advertiser"
        Column         |          Type          |            Modifiers            | Storage  | Stats target | Description 
-----------------------+------------------------+---------------------------------+----------+--------------+-------------
 id                    | integer                | not null                        | plain    |              | 
 user_id               | integer                |                                 | plain    |              | 
 country_id            | integer                |                                 | plain    |              | 
 county_id             | integer                |                                 | plain    |              | 
 city_id               | integer                |                                 | plain    |              | 
 district_id           | integer                |                                 | plain    |              | 
 type                  | smallint               |                                 | plain    |              | 
 name                  | character varying(255) | not null                        | extended |              | 
 display_name          | character varying(255) | default NULL::character varying | extended |              | 
 title                 | character varying(255) | default NULL::character varying | extended |              | 
 about                 | text                   |                                 | extended |              | 
 birthday              | date                   | not null                        | plain    |              | 
 city_part             | character varying(255) | default NULL::character varying | extended |              | 
 street                | character varying(255) | default NULL::character varying | extended |              | 
 house_number          | character varying(20)  | default NULL::character varying | extended |              | 
 phone                 | character varying(15)  | not null                        | extended |              | 
 public_email          | character varying(255) | default NULL::character varying | extended |              | 
 public_url            | character varying(255) | default NULL::character varying | extended |              | 
 motto                 | character varying(255) | default NULL::character varying | extended |              | 
 message               | text                   |                                 | extended |              | 
 gender                | smallint               | not null default 1              | plain    |              | 
 im_facebook           | character varying(255) | default NULL::character varying | extended |              | 
 im_skype              | character varying(255) | default NULL::character varying | extended |              | 
 has_viber             | boolean                | not null default false          | plain    |              | 
 has_whatsapp          | boolean                | not null default false          | plain    |              | 
 has_sms_response      | boolean                | not null default false          | plain    |              | 
 visible_birthday      | boolean                | not null default false          | plain    |              | 
 status                | smallint               | not null default 10             | plain    |              | 
 is_checked            | boolean                | not null default false          | plain    |              | 
 geo_latitude          | double precision       |                                 | plain    |              | 
 geo_longitude         | double precision       |                                 | plain    |              | 
 languages             | integer[]              |                                 | extended |              | 
 created_at            | integer                |                                 | plain    |              | 
 updated_at            | integer                |                                 | plain    |              | 
 version               | bigint                 | default 0                       | plain    |              | 
Indexes:
    "advertiser_pkey" PRIMARY KEY, btree (id)

What is your advice? Where should I looking for the problem?

Источник

При работе со значениями NULL необходимо использовать операторы IS и NOT, а не операторы сравнения (=, <, > или <>). Поле со значением NULL — это поле без значения. NULL указывает на отсутствие данных, поэтому сравнение по значению будет ошибкой.

Сравнение с NULL

Сравнение значения с NULL всегда даст результат NULL, даже если значение сравниваемой переменной является NULL. Поэтому для проверки наличия NULL значения в MySQL следует использовать оператор IS NULL, а не оператор =.

-- выбрать все заказы, где не указан адрес (IS NULL)
SELECT
	*
FROM
	`orders`
WHERE 
	`address` IS NULL;


-- выбрать все заказы с указанным адресом (NOT NULL)
SELECT
	*
FROM
	`orders`
WHERE 
	`address` NOT NULL;

Замена NULL значения на другое значение

В MySQL можно заменить NULL значения на другое значение с помощью функции COALESCE(). Функция COALESCE() принимает несколько аргументов и возвращает первое значение, которое не является NULL.

Например, чтобы выбрать имя и возраст пользователя, а если возраст равен NULL — заменить его на 18, можно использовать следующий запрос:

SELECT 
	`name`, 
	COALESCE( `age`, 18) as `age`
FROM 
	`users`;

Использование NULL в выражениях

Если при выполнении операции с участием NULL значения результат не может быть определен, то результатом операции будет NULL. Например, если вы попытаетесь сравнить NULL значение с числом, результатом будет NULL.

В MySQL есть функция IFNULL(), которая возвращает второй аргумент, если первый равен NULL. Это может быть полезно при выполнении арифметических операций, чтобы избежать ошибок.

Например, чтобы выбрать все записи из таблицы orders, в которых значение столбца price больше 100, можно использовать следующий запрос:

SELECT 
	* 
FROM 
	`orders` 
WHERE 
	IFNULL( `price`, 0 ) > 100;

В этом примере мы используем функцию IFNULL() для замены NULL значения на 0 при выполнении операции сравнения.

Использование NULL в операторах JOIN

В операторах JOIN, NULL значения могут создавать некоторые особенности при выборке данных из нескольких таблиц. Если один из столбцов таблицы содержит NULL значение, соответствующая строка из другой таблицы может быть пропущена.

Чтобы решить эту проблему, в MySQL можно использовать оператор LEFT JOIN, который возвращает все строки из первой (левой) таблицы и соответствующие строки из второй (правой) таблицы. Если нет соответствующих строк во второй таблице, соответствующие столбцы будут заполнены NULL значениями.

Например, если у нас есть таблица users и таблица orders, и мы хотим выбрать всех пользователей и все их заказы (включая тех, у которых нет заказов), можно использовать следующий запрос:

SELECT 
	`users` . `name`, 
	`orders` . `order_id`
FROM 
	`users`

LEFT JOIN 
	`orders` 
ON 
	`users`. `user_id` = `orders` . `user_id`;

В этом примере мы используем оператор LEFT JOIN, чтобы вернуть все строки из таблицы users, включая те, у которых нет соответствующих строк в таблице orders. Если нет соответствующих строк в таблице orders, столбец orders.order_id будет содержать значение NULL.

Обработка NULL значений в приложении

При обработке данных в приложении необходимо учитывать возможность наличия NULL значений и выполнять соответствующие проверки перед выполнением операций. Например, перед выполнением арифметических операций с NULL значениями необходимо проверить, что значение не равно NULL.

Также следует учитывать, что некоторые функции могут вернуть NULL значения, если аргументом функции является NULL. Например, функция CONCAT() возвращает NULL, если ей передан хотя бы один NULL аргумент.

Поэтому, при написании приложений, необходимо учитывать особенности работы с NULL значениями и обеспечивать соответствующую обработку данных, чтобы избежать ошибок и неожиданного поведения приложения.

Использование индексов в таблицах с NULL значениями

В MySQL индексы не включают строки с NULL значениями, поэтому использование NULL значений в таблицах может оказывать влияние на производительность запросов, особенно при работе с большими таблицами.

Если таблица содержит много NULL значений в столбцах, используемых в запросах, может быть эффективнее заменить NULL значения на какие-то другие значения, например, на пустые строки для строковых типов данных или на 0 для числовых типов данных.

Однако, необходимо учитывать, что замена NULL значений на другие значения может изменить семантику данных и привести к ошибкам, если они не будут обработаны корректно.

Разрешение конфликтов при использовании NULL

При работе с NULL значениями может возникать конфликт, когда необходимо определить, как обрабатывать NULL значения в выражениях.

В MySQL для разрешения конфликтов при использовании NULL значений можно использовать следующие функции:

IFNULL( expr1, expr2 ): возвращает значение expr1, если оно не равно NULL, иначе возвращает значение expr2.
COALESCE( expr1, expr2, …, exprN ): возвращает первое значение, которое не равно NULL, начиная с expr1.
NULLIF( expr1, expr2 ): возвращает NULL, если expr1 равно expr2, иначе возвращает значение expr1.

Например, чтобы выбрать имена пользователей и количество заказов, можно использовать следующий запрос, который использует функцию IFNULL() для замены NULL значения на 0:

SELECT 
	`users` . `name`, 
	IFNULL( COUNT( `orders` . `order_id` ), 0 ) as `orders_count`
FROM 
	`users`
LEFT JOIN 
	`orders` 
ON 
	`users` . `user_id` = `orders` . `user_id`
GROUP BY 
	`users` . `user_id`;

В этом примере мы используем функцию IFNULL() для замены NULL значения на 0 при подсчете количества заказов для каждого пользователя.

MySQL

05.08.2022

Просмотров: 691

Источник