Re: Issue with insert

Here is the table DDL,

To test you can either use the record below or make up anything you want.
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 = 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
>>  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
>
> When you display it, do you use pgadmin or psql?  Does psql do the same
thing?
>
> 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?
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>