pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with insert
5+ messages / 3 participants
[nested] [flat]
* Issue with insert
@ 2010-02-27 17:14 Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 17:50 ` Re: Issue with insert Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Gould @ 2010-02-27 17:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with insert
2010-02-27 17:14 Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2010-02-27 17:50 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-02-27 20:25 ` Re: Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2010-02-27 17:50 UTC (permalink / raw)
To: Michael Gould <mgould@intermodalsoftwaresolutions.net>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with insert
2010-02-27 17:14 Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 17:50 ` Re: Issue with insert Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-02-27 20:25 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 20:58 ` Re: Issue with insert Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Gould @ 2010-02-27 20:25 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with insert
2010-02-27 17:14 Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 17:50 ` Re: Issue with insert Scott Marlowe <scott.marlowe@gmail.com>
2010-02-27 20:25 ` Re: Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2010-02-27 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-27 21:02 ` Re: Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2010-02-27 20:58 UTC (permalink / raw)
To: Michael Gould <mgould@intermodalsoftwaresolutions.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
Michael Gould <mgould@intermodalsoftwaresolutions.net> writes:
> Here is the table DDL,
Can't really test this since you didn't supply the source code for
those triggers:
> CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE
> ON "iss"."citystateinfo" FOR EACH ROW
> EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"();
> CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE
> ON "iss"."citystateinfo" FOR EACH ROW
> EXECUTE PROCEDURE "iss"."timestampfunc"();
Personally I'm suspicious that the BEFORE INSERT trigger is changing the
data.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with insert
2010-02-27 17:14 Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 17:50 ` Re: Issue with insert Scott Marlowe <scott.marlowe@gmail.com>
2010-02-27 20:25 ` Re: Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 20:58 ` Re: Issue with insert Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-02-27 21:02 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Gould @ 2010-02-27 21:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
Tom,
Thanks, that's exactly what it was.
Best Regards
Mike Gould
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Michael Gould <mgould@intermodalsoftwaresolutions.net> writes:
>> Here is the table DDL,
>
> Can't really test this since you didn't supply the source code for
> those triggers:
>
>> CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE
>> ON "iss"."citystateinfo" FOR EACH ROW
>> EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"();
>
>> CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE
>> ON "iss"."citystateinfo" FOR EACH ROW
>> EXECUTE PROCEDURE "iss"."timestampfunc"();
>
> Personally I'm suspicious that the BEFORE INSERT trigger is changing the
> data.
>
> regards, tom lane
>
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-02-27 21:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-27 17:14 Issue with insert Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 17:50 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-02-27 20:25 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
2010-02-27 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-27 21:02 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox