Thread: isnull() function in pgAdmin3
Hi, I am trying to emulate the isnull() function used in MySQL into postreSQL. I have tried different ways such is null but it's not producing the results desired. I am doing a data migration from MySQL into postgreSQL and need help with the isnull() in pgAdmin3. Any assistance provided would be greatly appreciated. Thanks, In MySQL: (case when ((`s`.`Funding_Date` = '') and (isnull(`s`.`Actual_Close_Date`) or (`s`.`Actual_Close_Date` = ''))) then 'RPG_INV' when ((isnull(`s`.`Funding_Date`) or (`s`.`Funding_Date` <> '')) and ((`s`.`Actual_Close_Date` = '') or isnull(`s`.`Actual_Close_Date`))) then 'Builder_Inventory' else 'Owner_Inventory' end) AS `Lot_Status`, -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of dudedoe01 Sent: Tuesday, September 27, 2016 1:05 PM To: pgsql-general@postgresql.org Subject: [GENERAL] isnull() function in pgAdmin3 Hi, I am trying to emulate the isnull() function used in MySQL into postreSQL. I have tried different ways such is null but it'snot producing the results desired. I am doing a data migration from MySQL into postgreSQL and need help with the isnull()in pgAdmin3. Any assistance provided would be greatly appreciated. Thanks, In MySQL: (case when ((`s`.`Funding_Date` = '') and (isnull(`s`.`Actual_Close_Date`) or (`s`.`Actual_Close_Date` = ''))) then 'RPG_INV' when ((isnull(`s`.`Funding_Date`) or (`s`.`Funding_Date` <> '')) and ((`s`.`Actual_Close_Date` = '') or isnull(`s`.`Actual_Close_Date`))) then 'Builder_Inventory' else 'Owner_Inventory' end) AS `Lot_Status`, __________________________________________________________________________________________________________ isnull(`s`.`Actual_Close_Date`) in MySQL is equivalent to: s.actual_close_date IS NULL in Postgres. What exactly didn't work for you? Regards, Igor Neyman
On 09/27/2016 10:04 AM, dudedoe01 wrote: > Hi, > > I am trying to emulate the isnull() function used in MySQL into postreSQL. I > have tried different ways such is null but it's not producing the results > desired. I am doing a data migration from MySQL into postgreSQL and need > help with the isnull() in pgAdmin3. > > Any assistance provided would be greatly appreciated. > > Thanks, > > In MySQL: > > (case > when > ((`s`.`Funding_Date` = '') > and (isnull(`s`.`Actual_Close_Date`) > or (`s`.`Actual_Close_Date` = ''))) > then > 'RPG_INV' > when > ((isnull(`s`.`Funding_Date`) > or (`s`.`Funding_Date` <> '')) > and ((`s`.`Actual_Close_Date` = '') > or isnull(`s`.`Actual_Close_Date`))) > then > 'Builder_Inventory' > else 'Owner_Inventory' > end) AS `Lot_Status`, > > aklaver@test=> select 1 is null; ?column? ---------- f (1 row) aklaver@test=> select null is null; ?column? ---------- t or ^ aklaver@test=> select 1 isnull; ?column? ---------- f (1 row) aklaver@test=> select null isnull; ?column? ---------- t (1 row) > > -- > View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Sep 27, 2016 at 12:04 PM, dudedoe01 <marsalanaq@gmail.com> wrote: > I am trying to emulate the isnull() function used in MySQL into postreSQL. In the future, please describe the results you are trying to achieve or at least describe the semantics of the function from elsewhere that you are trying to emulate. I had no idea what the ISNLL() function of MySQL does; on a quick web search, it looks like you can replace: isnull(expression) with: (expression) IS NULL > In MySQL: > > (case > when > ((`s`.`Funding_Date` = '') > and (isnull(`s`.`Actual_Close_Date`) > or (`s`.`Actual_Close_Date` = ''))) > then > 'RPG_INV' > when > ((isnull(`s`.`Funding_Date`) > or (`s`.`Funding_Date` <> '')) > and ((`s`.`Actual_Close_Date` = '') > or isnull(`s`.`Actual_Close_Date`))) > then > 'Builder_Inventory' > else 'Owner_Inventory' > end) AS `Lot_Status`, In PostgreSQL perhaps: (case when (("s"."Funding_Date" = '') and (("s"."Actual_Close_Date") is null or ("s"."Actual_Close_Date" = ''))) then 'RPG_INV' when ((("s"."Funding_Date") is null or ("s"."Funding_Date" <> '')) and (("s"."Actual_Close_Date" = '') or ("s"."Actual_Close_Date") is null)) then 'Builder_Inventory' else 'Owner_Inventory' end) AS "Lot_Status", -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks Kevin, Igor and Adrian. In MySQL with the isnull() function I get all three values RPG INV, Owner Inventory, and Builder Inventory showed up in the view while when I do the is null function in postgreSQL I only see Builder Inventory and Owner Inventory show up in the view. I don't know why the RPG_INV is not showing up. In MySQL: Owner Inventory is 4481 rows of data Builder Inventory is 1312 rows of data RPG_Inv is 374 rows of data gives the total of 6167 rows In postgreSQL: Owner Inventory is 4521 rows of data Builder inventory is 1646 rows of data gives the total of 6167 rows create view si_model as select c."DCAD_Prop_ID" as DCAD_ID, m."Address" as Address, m."Addition" as Addition, m."Block" as Block, m."Lot" as Lot, m."Lot_Size" as Lot_Size, m."Lot_Type" as Lot_Type, l."Funding_Date" as Lot_Sale_Date, h."Actual_Close_Date" as Home_Closing_Date, m."District" as District, g."nhs_builder" as Builder, g."nhs_sale_date" as NHSContractDate, '' as "Banks & Ind. Owned Lots", '' as "Repurchased", m."Reserved_Lots" as Reserved, d."Permit__" as "Permit #", d."Permit_Date" as "Permit Date", d."Foundation_Date" as "Foundation Date", d."Frame_Date" as "Frame Date", d."HCS" as HCS, '' as "Notes_Comments", l."Lot_Status" as "Lot Funding Status", '' as "Property Description", '' as "GIS Map", d."Project_ID" as Project_ID, (case when ((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null or (h."Actual_Close_Date" = ''))) then 'RPG Inventory' when (((l."Funding_Date") is null or (l."Funding_Date" <> '')) and ((h."Actual_Close_Date" = '') or (h."Actual_Close_Date") is null)) then 'Builder Inventory' else 'Owner Occupied' end) AS "Lot_Status", ((case when c."DCAD_Prop_ID" = m."DCAD_Prop_ID" then 'YES' else '' end)) as "Home Sale", ((case when m."Address" = s."Address_of_Inventory_Home" then 'C Spec' else '' end)) as "Current Specs", ((case when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and g."nhs_build_spec" = 'Build' then 'Build' when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and g."nhs_build_spec" = 'Spec' then 'Spec' else '' end)) as "Build/Spec" from "Calculations" c left join "MasterLotList" m on ((c."DCAD_Prop_ID" = m."DCAD_Prop_ID")) left join "HomeClosings" h on ((h."Address" = m."Address")) left join "GrossNewHomeSales" g on ((g."nhs_address" = m."Address")) left join "HCSTable" d on ((d."DCAD_Prop_ID" = c."DCAD_Prop_ID")) left join "LotSales" l on ((l."DCAD_Prop_ID" = c."DCAD_Prop_ID")) left join "CurrentSpecs" s on ((s."Address_of_Inventory_Home" = m."Address")) Any help provided would be greatly appreciated. -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923162.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
The tables I migrated from MySQL into postgreSQL have exactly the same amount of rows of data so the problem is inside the view being created. -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923164.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Maybe your are after IS NOT DISTINCT FROM NULL
https://www.postgresql.org/docs/current/static/functions-comparison.html
https://www.postgresql.org/docs/current/static/functions-comparison.html
On 09/27/2016 01:10 PM, dudedoe01 wrote: > Thanks Kevin, Igor and Adrian. In MySQL with the isnull() function I get all > three values RPG INV, Owner Inventory, and Builder Inventory showed up in > the view while when I do the is null function in postgreSQL I only see > Builder Inventory and Owner Inventory show up in the view. I don't know why > the RPG_INV is not showing up. You don't say what Postgres version you are on, but if it is 9.5+ then this may come into play: https://www.postgresql.org/docs/9.5/static/release-9-5.html " Adjust operator precedence to match the SQL standard (Tom Lane) The precedence of <=, >= and <> has been reduced to match that of <, > and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to be just below these six comparison operators. Also, multi-keyword operators beginning with NOT now have the precedence of their base operator (for example, NOT BETWEEN now has the same precedence as BETWEEN) whereas before they had inconsistent precedence, behaving like NOT with respect to their left operand but like their base operator with respect to their right operand. The new configuration parameter operator_precedence_warning can be enabled to warn about queries in which these precedence changes result in different parsing choices. " Also from your original post: (case when ((`s`.`Funding_Date` = '') and (isnull(`s`.`Actual_Close_Date`) or (`s`.`Actual_Close_Date` = ''))) then 'RPG_INV' when ((isnull(`s`.`Funding_Date`) or (`s`.`Funding_Date` <> '')) and ((`s`.`Actual_Close_Date` = '') or isnull(`s`.`Actual_Close_Date`))) then 'Builder_Inventory' else 'Owner_Inventory' end) AS `Lot_Status`, and from below: (case when ((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null or (h."Actual_Close_Date" = ''))) then 'RPG Inventory' when (((l."Funding_Date") is null or (l."Funding_Date" <> '')) and ((h."Actual_Close_Date" = '') or (h."Actual_Close_Date") is null)) then 'Builder Inventory' else 'Owner Occupied' end) AS "Lot_Status", I would try something like: ((l."Funding_Date" = '') and ((h."Actual_Close_Date" is null) or (h."Actual_Close_Date" = ''))) > > In MySQL: > > Owner Inventory is 4481 rows of data > Builder Inventory is 1312 rows of data > RPG_Inv is 374 rows of data > > gives the total of 6167 rows > > In postgreSQL: > > Owner Inventory is 4521 rows of data > Builder inventory is 1646 rows of data > > gives the total of 6167 rows > > create view si_model as select > c."DCAD_Prop_ID" as DCAD_ID, > m."Address" as Address, > m."Addition" as Addition, > m."Block" as Block, > m."Lot" as Lot, > m."Lot_Size" as Lot_Size, > m."Lot_Type" as Lot_Type, > l."Funding_Date" as Lot_Sale_Date, > h."Actual_Close_Date" as Home_Closing_Date, > m."District" as District, > g."nhs_builder" as Builder, > g."nhs_sale_date" as NHSContractDate, > '' as "Banks & Ind. Owned Lots", > '' as "Repurchased", > m."Reserved_Lots" as Reserved, > d."Permit__" as "Permit #", > d."Permit_Date" as "Permit Date", > d."Foundation_Date" as "Foundation Date", > d."Frame_Date" as "Frame Date", > d."HCS" as HCS, > '' as "Notes_Comments", > l."Lot_Status" as "Lot Funding Status", > '' as "Property Description", > '' as "GIS Map", > d."Project_ID" as Project_ID, > (case when > ((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null > or (h."Actual_Close_Date" = ''))) then 'RPG Inventory' > when > (((l."Funding_Date") is null or (l."Funding_Date" <> '')) > and ((h."Actual_Close_Date" = '') or > (h."Actual_Close_Date") is null)) > then > 'Builder Inventory' > else 'Owner Occupied' > end) AS "Lot_Status", > > ((case when c."DCAD_Prop_ID" = m."DCAD_Prop_ID" then 'YES' else '' end)) as > "Home Sale", > ((case when m."Address" = s."Address_of_Inventory_Home" then 'C Spec' else > '' end)) as "Current Specs", > ((case when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and > g."nhs_build_spec" = 'Build' then 'Build' > when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and > g."nhs_build_spec" = 'Spec' then 'Spec' else '' end)) > as "Build/Spec" > from "Calculations" c > left join "MasterLotList" m on ((c."DCAD_Prop_ID" = m."DCAD_Prop_ID")) > left join "HomeClosings" h on ((h."Address" = m."Address")) > left join "GrossNewHomeSales" g on ((g."nhs_address" = m."Address")) > left join "HCSTable" d on ((d."DCAD_Prop_ID" = c."DCAD_Prop_ID")) > left join "LotSales" l on ((l."DCAD_Prop_ID" = c."DCAD_Prop_ID")) > left join "CurrentSpecs" s on ((s."Address_of_Inventory_Home" = > m."Address")) > > Any help provided would be greatly appreciated. > > > > -- > View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923162.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
dudedoe01 schrieb am 27.09.2016 um 19:04: > I am trying to emulate the isnull() function used in MySQL into postreSQL. I > have tried different ways such is null but it's not producing the results > desired. I am doing a data migration from MySQL into postgreSQL and need > help with the isnull() in pgAdmin3. > > Any assistance provided would be greatly appreciated. > > Thanks, > > In MySQL: > > (case > when > ((`s`.`Funding_Date` = '') > and (isnull(`s`.`Actual_Close_Date`) > or (`s`.`Actual_Close_Date` = ''))) > then > 'RPG_INV' > when > ((isnull(`s`.`Funding_Date`) > or (`s`.`Funding_Date` <> '')) > and ((`s`.`Actual_Close_Date` = '') > or isnull(`s`.`Actual_Close_Date`))) > then > 'Builder_Inventory' > else 'Owner_Inventory' > end) AS `Lot_Status`, > Unrelated, but: why are you storing DATEs in VARCHAR columns? A migration might be a good moment to clean up the data model and store DATEs in a DATE (or TIMESTAMP) column.
On Tue, Sep 27, 2016 at 3:12 PM, dudedoe01 <marsalanaq@gmail.com> wrote: > The tables I migrated from MySQL into postgreSQL have exactly the same amount > of rows of data so the problem is inside the view being created. Have you actually confirmed that there are any cases where isnull(expression) yields a different result than (expression) is null when the expression gives the same value? I'm suspicious that an expression could be yielding a different result, perhaps based on join conditions handling comparisons between null values differently. Remember, for example, that in PostgreSQL NULL = NULL does not evaluate to TRUE. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
<http://postgresql.nabble.com/file/n5923277/Capture.png> This is with the DATE data type. I have 9.5 version installed. -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923277.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/28/2016 02:24 PM, dudedoe01 wrote: > <http://postgresql.nabble.com/file/n5923277/Capture.png> In future can you copy and paste the error. It makes it easier to deal with. Thanks. > > This is with the DATE data type. I have 9.5 version installed. The type really does not matter. The issue is as the error says, there is no isnull() function in Postgres: test=# select version(); version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.5.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit (1 row) test=# select isnull(); ERROR: function isnull() does not exist LINE 1: select isnull(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. > > > > -- > View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923277.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Select version(); shows postgreSQL 9.6beta3, compiled by Visual C++ build 1800, 64-bit -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923791.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/30/2016 08:18 AM, dudedoe01 wrote: > Select version(); shows > > postgreSQL 9.6beta3, compiled by Visual C++ build 1800, 64-bit There is no isnull() in 9.6 either, if that is what you are asking? Otherwise what is the question you want answered? -- Adrian Klaver adrian.klaver@aklaver.com
What is the most feasible way to emulate the below MySQL function into postgreSQL. Since the isnull() function is no longer supported in 9.6 version. I have tried every trick in the hat to get the desired results. Still 'RPG INV' doesn't show only the other two then options show up. (case when ((`s`.`Funding_Date` = '') and (isnull(`s`.`Actual_Close_Date`) or (`s`.`Actual_Close_Date` = ''))) then 'RPG_INV' when ((isnull(`s`.`Funding_Date`) or (`s`.`Funding_Date` <> '')) and ((`s`.`Actual_Close_Date` = '') or isnull(`s`.`Actual_Close_Date`))) then 'Builder_Inventory' else 'Owner_Inventory' end) AS `Lot_Status` -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5924161.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Mon, Oct 3, 2016 at 6:39 AM, dudedoe01 <marsalanaq@gmail.com> wrote:
What is the most feasible way to emulate the below MySQL function into
postgreSQL. Since the isnull() function is no longer supported in 9.6
version. I have tried every trick in the hat to get the desired results.
Still 'RPG INV' doesn't show only the other two then options show up.
(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`
Hi. Over the years I have gotten a lot of patient assistance from this mailing list, for which I am abundantly grateful. In light of that, I offer the following "translations," which are specifically in response to your question about emulating the SQL above:
and (isnull(`s`.`Actual_Close_Date`)
--> and (`s`.`Actual_Close_Date` IS NULL)
((isnull(`s`.`Funding_Date`)
--> (`s`.`Funding_Date` IS NULL)
or isnull(`s`.`Actual_Close_Date`)))
-->or (`s`.`Actual_Close_Date` IS NULL)))
HTH, and Cheers.
Ken
--
View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3- tp5923122p5924161.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On 10/03/2016 06:39 AM, dudedoe01 wrote: > What is the most feasible way to emulate the below MySQL function into > postgreSQL. Since the isnull() function is no longer supported in 9.6 One more time, Postgres does not have an isnull() function in any version AFAIK. You need to use IS NULL: https://www.postgresql.org/message-id/MWHPR07MB28777547180DC028EF812E10DACC0%40MWHPR07MB2877.namprd07.prod.outlook.com https://www.postgresql.org/message-id/80becd5e-2fcf-5660-574b-82bcb040e18a%40aklaver.com https://www.postgresql.org/message-id/CACjxUsOkFgiGKRjmhWonE3yfiPpsrn9dsmHW%2B9KaZSh9RWB_ow%40mail.gmail.com > version. I have tried every trick in the hat to get the desired results. > Still 'RPG INV' doesn't show only the other two then options show up. > > (case > when > ((`s`.`Funding_Date` = '') > and (isnull(`s`.`Actual_Close_Date`) > or (`s`.`Actual_Close_Date` = ''))) > then > 'RPG_INV' > when > ((isnull(`s`.`Funding_Date`) > or (`s`.`Funding_Date` <> '')) > and ((`s`.`Actual_Close_Date` = '') > or isnull(`s`.`Actual_Close_Date`))) > then > 'Builder_Inventory' > else 'Owner_Inventory' > end) AS `Lot_Status` If I am following correctly: ( CASE WHEN (`s`.`Funding_Date` = '') AND ( (`s`.`Actual_Close_Date` IS NULL) OR (`s`.`Actual_Close_Date` = '') ) THEN 'RPG_INV' WHEN ( (`s`.`Funding_Date` IS NULL) OR (`s`.`Funding_Date` <> '') ) AND ( (`s`.`Actual_Close_Date` IS NULL) OR (`s`.`Actual_Close_Date` = '') ) THEN 'Builder_Inventory' ELSE 'Owner_Inventory' END ) AS `Lot_Status` > > > > -- > View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5924161.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/10/16 14:39, dudedoe01 wrote: > What is the most feasible way to emulate the below MySQL function into > postgreSQL. Since the isnull() function is no longer supported in 9.6 > version. I have tried every trick in the hat to get the desired results. > Still 'RPG INV' doesn't show only the other two then options show up. > > (case > when > ((`s`.`Funding_Date` = '') > and (isnull(`s`.`Actual_Close_Date`) > or (`s`.`Actual_Close_Date` = ''))) <snip> Maybe I missed something earlier in the thread (only tuned in now), but what's wrong with ... and s.actual_close_date is null .... ? Ray.
What is the most feasible way to emulate the below MySQL function into
postgreSQL. Since the isnull() function is no longer supported in 9.6
version. I have tried every trick in the hat to get the desired results.
Still 'RPG INV' doesn't show only the other two then options show up.
(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`
If you include something like the following people are going to be more willing to provide help and better able to provide good help.
WITH dt (funding_date, actual_date) AS (
VALUES (null, null), ('X', 'X'),
(null, ''), (null, 'X'),
('', null), ('X', null)
)
SELECT funding_date, actual_date,
CASE WHEN funding_date IS NULL
AND actual_date IS NULL
THEN 'Both Null'
ELSE 'write more WHEN statements...'
END
FROM dt
;
That's a self-contained example and it does wonders for problem solving.
David J.