X-Antivirus-mail.weatherbeeta.com.au-Mail-From: 
 phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au
X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):.
	Processed in 0.465081 secs Process 3518)
Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155)
	by mail.weatherbeeta.com.au with SMTP; 30 Jan 2007 16:32:22 +1100
From: "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
To: <pgsql-sql@postgresql.org>
Subject: Differentiate Between Zero-Length String and NULL Column Values
Date: Tue, 30 Jan 2007 16:32:22 +1100
Organization: Weatherbeeta Pty Ptd
Message-ID: <016d01c74430$043b1b90$9b0014ac@wbaus090>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_016E_01C7448C.37AB9390"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-Scanned-By-wbeetaau1: Virus scan performed by network-box
X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1170135144.856-20520-000
X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in
	0.05/0.22 secs)
X-Scanned-By-wbeetaau1: Spam-Check-Result: No,
	hits=0 required=7 tests= autolearn=no version=2.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.091 tagged_above=0 required=5 tests=AWL, BAYES_40,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200701/152
X-Sequence-Number: 27304

This is a multi-part message in MIME format.

------=_NextPart_000_016E_01C7448C.37AB9390
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi All,

Small problem with 8.2.1, I have a temp table of basic stock details:
	CREATE TEMP TABLE tmpstk
	(
	  code varchar(6),
	  description varchar(38),
	  grp varchar(4),
	  brand text,
	  style text,
	  supplier varchar(6),
	  supplier_code text,
	  wholesale numeric(10,2),
	  retail numeric(10,2),
	  ean varchar(13)
	)
	WITHOUT OIDS;

This table is populated using a COPY query =96 works OK. If I try the
following query:
	SELECT * FROM tmpstk
I get what I expect, almost 8000 rows including rows similar to the
following:
"401514","EQUINADE_SHOWSILK_SHAMPOO_5L","3209","EQUINADE","SHAMPOO","EQUE=
ST"
,"401514","0.00","0.00","0.00","0.00","10.00","9329028056594"
"401600","A/DRESS_SHAMPOO_TEA_TREE_OIL_500ML","3208","AUSTRALIAN_DRESSAGE=
","
SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00",""

The second row (401600) is what I=92m interested in for this particular
problem. The problem is when I try and add a WHERE clause:
	SELECT * FROM tmpstk WHERE ean =3D '';
	SELECT * FROM tmpstk WHERE TRIM(ean) =3D '';
	SELECT * FROM tmpstk WHERE ean =3D NULL;
None of the above queries return any rows.

What am I doing wrong? It should return the 401600 row above, along with
several hundred other rows. I have attached the bash script that =
actually
does this processing.

Help is much appreciated.
Cheers,
-p


Phillip Smith
IT Coordinator
Weatherbeeta P/L
AUSTRALIA
=A0
E. phillip.smith@NO-SPAM.weatherbeeta.com.au


=0A*******************Confidentiality and Privilege Notice*******************=
=0A=0AThe material contained in this message is privileged and confidential to=
=0Athe addressee.  If you are not the addressee indicated in this message or=
=0Aresponsible for delivery of the message to such person, you may not copy=0Aor=
 deliver this message to anyone, and you should destroy it and kindly=0Anotify=
 the sender by reply email.=0A=0AInformation in this message that does not relat=
e to the official business=0Aof Weatherbeeta must be treated as neither given =
nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contractors or as=
sociates shall not be liable=0Afor direct, indirect or consequential loss aris=
ing from transmission of this=0Amessage or any attachments=0A

------=_NextPart_000_016E_01C7448C.37AB9390
Content-Type: application/x-sh;
	name="import-wb-stock.sh"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="import-wb-stock.sh"

#!/bin/bash

#
# Start by selecting the details from the WB Stock File into
# a temporary table, then exporting that table to file
#
TMPFILE=3D`mktemp -t wbstk.XXXX`
TMPTABLE=3D"tmpstock$$"

SQL_COMMAND=3D"
SELECT	stock.code,
	SUBSTR(stock.description, 1, 38) AS description,
	CASE	WHEN stock.supplier LIKE '__/__ ' THEN '0001'
		WHEN stock.supplier =3D 'ABSEC ' THEN '0001'
		ELSE stock.grp
	END AS grp,
	stock.brand,
	stock.style,
	'EQUEST'::varchar(6) AS supplier,
	stock.code AS supplier_code,
	(stock.wholesale_cost - ((stock.wholesale_cost / 100) * 15) + =
(((stock.wholesale_cost - ((stock.wholesale_cost / 100) * 15)) / 100) * =
10))::NUMERIC(10,2) AS wholesale,
	stock.retail_price,
	stock.ean
INTO TEMP TABLE $TMPTABLE
FROM	stock
WHERE	stock.supplier <> 'BATES '
 AND	stock.grp <> '0001'
-- AND	kit_pack =3D 'N'
 AND	send_to_hl =3D 'Y'
;
COPY $TMPTABLE TO '$TMPFILE'
;"

/usr/local/bin/psql wbau -c "$SQL_COMMAND" > /dev/null
RETVAL=3D$?
if [ "$RETVAL" -ne 0 ] ; then
	exit 1
fi

#
# Copy that table into a temp table in the Horseland DB
# then sync that with the Horseland Stock table
#
SQL_COMMAND=3D"
CREATE TEMP TABLE $TMPTABLE
(
  code varchar(6),
  description varchar(38),
  grp varchar(4),
  brand text,
  style text,
  supplier varchar(6),
  supplier_code text,
  wholesale numeric(10,2),
  retail numeric(10,2),
  ean varchar(13)
)=20
WITHOUT OIDS;

-- Import the file from WB Database
COPY $TMPTABLE FROM '$TMPFILE';

-- Clear out all stock in the live stock table that is in the new import
DELETE FROM stock WHERE code IN (SELECT code FROM $TMPTABLE);

-- Rebuild all indexes on the stock table so we don't get any false =
errors on unique constraints etc
REINDEX TABLE stock;

-- Insert the new codes into the live table
INSERT INTO stock (	code,
			description,
			grp,
			brand,
			style,
			supplier,
			supplier_code,
			wholesale,
			discount,
			hl_wholesale,
			retail,
			tax_rate,
			ean
		)
	SELECT	DISTINCT ON (ean)
		code,
		description,
		grp,
		brand,
		style,
		supplier,
		supplier_code,
		wholesale,
		0.00,
		0.00,
		retail,
		10.00,
		CASE WHEN ean =3D '' OR valid_barcode(ean) =3D false THEN null ELSE =
ean END AS ean
	FROM	$TMPTABLE
	WHERE	code NOT IN (SELECT code FROM stock_deleted)
;"
/usr/local/bin/psql horseland -c "$SQL_COMMAND" > /dev/null
RETVAL=3D$?
if [ "$RETVAL" -ne 0 ] ; then
        exit 1
fi

rm -f $TMPFILE

exit 0

------=_NextPart_000_016E_01C7448C.37AB9390--