Re: Query (view) question

On Mon, 2007-02-12 at 12:00 +0100, Joris Dobbelsteen wrote:
> >-----Original Message-----
> >From: pgadmin-support-owner(at)postgresql(dot)org
> >[mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Art Fore
> >Sent: maandag 12 februari 2007 11:40
> >To: Pgadmin-Support
> >Subject: [pgadmin-support] Query (view) question
> >
> [snip]
> >I tried this as a view with pgadmin with slight mods, This one
> >below works
> >
> >SELECT "ALL_PARTS"."PARTNUM", "ALL_PARTS"."Datasheet",
> >"ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"
> > FROM "ALL_PARTS" "ALL_PARTS"
> > WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;
> >
> >But, if I do the following
> >
> >SELECT "ALL_PARTS"."PARTNUM", "datasheet_address","sheet" ||
> >"ALL_PARTS"."Datasheet", "ALL_PARTS"."Outline", "ALL_PARTS"."Footprint"
> > FROM "ALL_PARTS" "ALL_PARTS", "datasheet_address"
> >"datasheet_address"
> > WHERE "ALL_PARTS"."PARTNUM"::text ~~ '021-%'::text;
>
> Notice the comma between "datasheet_address","sheet". I doesn't seem
> intentional.
>
> Try that, otherwise look up the column types and try casting them.
>
> With parsers (especially compilers) there seems to be a relation between
> clariness of the error messages and how widespread the software is.
> For Postgres there are three options:
> 1) Its not widespread, justifying the error messages
> 2) Its error messages indicate its not widespread
> 3) It has defied the odds.
>
> Slight off-topic note:
> Furthermore, you can get rid of all the ugly " if you name everything
> with only lowercase letters. When not quoting the names Postgres seems
> to do some name mangling (convert to lowercase and search for it in a
> case-sensative way with the unconverted names).
>
> - Joris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Comma was a type in the email, which I edited since the view did not
save my edits, it was not in the orginal that I tried from PGadmin. The
comma causes another error though.

What do you mean by "casting"? I am no database expert and have not
encountered that before. Could I have an example? I did have a problem
with data types, but changed the datasheets_address xolumns to vchar(64)
to mach the column types in All_Part. which seemed to fix that problem.

Also a note, I tried find/replace in 1.7 and that did not work for
modifying the All_Parts table. Tried Rekall, it opens the database and I
can see the tables and views, etc., but anything I try to view data or
even look at the properties, it crashes. Tried Knode, but its
find/replace also did not work. Ended up using a make table query in
Access from the linked postgres database, doing a search/replace on it,
then updating the table with an update query. That did not work at
first, but changed the ODBC driver from UTF-8 to ANSI, then it worked.
Don't understand why since the database is UTF-8. If I had discovered
this earlier, I might could have done the search/replace direcctly on
the linked table instead of the work around with Access.

Sure would be nice and helpful for postgres if there was an Access like
application for postgres that worked as well as Access it does, although
I do not like MS or Access, for my purposes, it does work. On the other
hand, the application that uses the database only as read only,
DxExchange from Mentor Graphics, is about 10 to 100 times times faster
using postgres rather than Access, also, Access has a limit on
simultaneous users.

Art