Date: Sat, 27 Feb 2010 14:25:54 -0600
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-sql@postgresql.org
From: Michael Gould <mgould@intermodalsoftwaresolutions.net>
Subject: Re: Issue with insert
Message-ID: <68f695e891db6c12e538e1a31a19a797@intermodalsoftwaresolutions.net>
In-Reply-To: <dcc563d11002270950l4ce7ee4bg248443ce957ee087@mail.gmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"

Here is the table DDL,

To test you can either use the record below or make up anything you =
want.=20
I've changed the isscontrib.citext to varchar and text data types and the
results are the same.

Best Regards

Michael Gould

CREATE TABLE "iss"."citystateinfo" (
"citystateinfoid" UUID DEFAULT isscontrib.uuid_generate_v4() NOT NULL,
"citystate" "isscontrib"."citext" NOT NULL,
"zipcode" CHAR(10) NOT NULL,
"cityname" "isscontrib"."citext" NOT NULL,
"statecode" VARCHAR(2) NOT NULL,
"latitude" NUMERIC(12,8),
"longitude" NUMERIC(12,8),
"countrycode" VARCHAR(2),
"activeflag" CHAR(1) DEFAULT 'Y'::bpchar NOT NULL,
"createdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
"createuser" "isscontrib"."citext" DEFAULT "current_user"(),
"editdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
"edituser" "isscontrib"."citext" DEFAULT "current_user"(),
CONSTRAINT "pk_citystateinfo" PRIMARY KEY("citystateinfoid"),
CONSTRAINT "citystateinfo_activeflag_check" CHECK (activeflag =3D ANY
(ARRAY['N'::bpchar, 'Y'::bpchar]))
) WITHOUT OIDS;

COMMENT ON TABLE "iss"."citystateinfo"
IS 'City State information for Mileage calculations';

COMMENT ON COLUMN "iss"."citystateinfo"."citystateinfoid"
IS 'Global Unique Identifier - CityStateInfo';

COMMENT ON COLUMN "iss"."citystateinfo"."citystate"
IS 'City State Combination in City, State';

COMMENT ON COLUMN "iss"."citystateinfo"."zipcode"
IS 'Postal Code';

COMMENT ON COLUMN "iss"."citystateinfo"."cityname"
IS 'City Name';

COMMENT ON COLUMN "iss"."citystateinfo"."statecode"
IS 'State Code';

COMMENT ON COLUMN "iss"."citystateinfo"."latitude"
IS 'Latitude';

COMMENT ON COLUMN "iss"."citystateinfo"."longitude"
IS 'Longitude';

COMMENT ON COLUMN "iss"."citystateinfo"."countrycode"
IS 'Country Code';

COMMENT ON COLUMN "iss"."citystateinfo"."activeflag"
IS 'Is Item Active?';

COMMENT ON COLUMN "iss"."citystateinfo"."createdatetime"
IS 'Time stamp record created';

COMMENT ON COLUMN "iss"."citystateinfo"."createuser"
IS 'Record Created by User';

COMMENT ON COLUMN "iss"."citystateinfo"."editdatetime"
IS 'Time stamp when record last changed';

COMMENT ON COLUMN "iss"."citystateinfo"."edituser"
IS 'Last User to change record';

CREATE INDEX "i_cityname" ON "iss"."citystateinfo"
USING btree ("citystate" "isscontrib"."citext_ops");

COMMENT ON INDEX "iss"."i_cityname"
IS 'Index by City Name';

CREATE INDEX "i_citystateinfozipcode" ON "iss"."citystateinfo"
USING btree ("zipcode");

COMMENT ON INDEX "iss"."i_citystateinfozipcode"
IS 'Index by ZipCode';

CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE
ON "iss"."citystateinfo" FOR EACH ROW
EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"();

COMMENT ON TRIGGER "insertcitystateinfo" ON "iss"."citystateinfo"
IS 'Setup cityname column';

CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE
ON "iss"."citystateinfo" FOR EACH ROW
EXECUTE PROCEDURE "iss"."timestampfunc"();


"Michael Gould" <mgould@intermodalsoftwaresolutions.net> wrote:
> I'm having a issue with a insert
>
> INSERT INTO
> iss.citystateinfo
> (
> citystateinfoid,
> citystate,
> zipcode,
> cityname,
> statecode
> )
> VALUES (
> '31344342-3439-4135-2d32-3044462d3433',
> 'Orange Park,FL',
> '32065',
> 'Orange Park',
> 'FL'
> );
>
> This inserts correctly, however when I view the data the citystate is
always
> displayed as
>
> Orange Park (FL). Every row is displayed the same. I've used PGAdmin to
> look at the data, EMS for PostGres and have exported the data to Excel =
and
> they all display the data incorrectly. This is a issue for us because we
> want the user to type in either the zipcode if known to bring back the
city
> and state, or the city,state to bring back the zipcode.
>
> Any idea's on why this could be happening. I'm running on Windows 7 x64
> using 8.4.2
>
> Best Regards
> --
> Michael Gould, Managing Partner
> Intermodal Software Solutions, LLC
> 904.226.0978
> 904.592.5250 fax
>

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax


Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> On Sat, Feb 27, 2010 at 10:14 AM, Michael Gould
> <mgould@intermodalsoftwaresolutions.net> wrote:
>> I'm having a issue with a insert
>>
>> INSERT INTO
>> =A0iss.citystateinfo
>> (
>> =A0citystateinfoid,
>> =A0citystate,
>> =A0zipcode,
>> =A0cityname,
>> =A0statecode
>> )
>> VALUES (
>> =A0'31344342-3439-4135-2d32-3044462d3433',
>> =A0'Orange Park,FL',
>> =A0'32065',
>> =A0'Orange Park',
>> =A0'FL'
>> );
>>
>> This inserts correctly, however when I view the data the citystate is
always
>> displayed as
>>
>> Orange Park (FL). =A0Every row is displayed the same. =A0I've used =
PGAdmin to
>> look at the data, EMS for PostGres and have exported the data to Excel
and
>> they all display the data incorrectly. =A0This is a issue for us =
because we
>> want the user to type in either the zipcode if known to bring back the
city
>> and state, or the city,state to bring back the zipcode.
>>
>> Any idea's on why this could be happening. =A0I'm running on Windows 7 =
x64
>> using 8.4.2
>=20
> When you display it, do you use pgadmin or psql?  Does psql do the same
thing?
>=20
> Can we see the ddl that created this table?  Can you create a
> self-contained test-case that others can run and see the same results?
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>=20