FAQ
Hi Everyone,

I've inherited an ancient Redhat 7.3 machine hosting the data for one of our
applications in a Postgres 7.2.4 database.

I must confess, I come from a MySQL/MS-SQL background so I'm a bit of a
Postgres newbie :)

I've setup a brand-new server running RHEL4 which is supplied with Postgres
7.4.8. I've followed all the advice I can find with regard to dumping the
existing data and re-importing it to the new server. All goes well except
for a few of the SQL statements during the import fail.

For example, one of the CREATE VIEW statements contains:
WHERE (u.username = "varchar"(u.customer_id))

Another starts with:
CREATE VIEW "sub_domains_view" as
SELECT (a.domain_name || evalstring(
(SELECT (' => '::"varchar" || da.domain_name) FROM account da WHERE....

These result in the following type of error:

ERROR: function varchar(integer) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

I've tried creating a cast using...

CREATE CAST (int4 AS varchar) WITH FUNCTION varchar(int4);

...but this fails - According to my Googling it appears the varchar()
function has been removed from Postgres sometime around version 7.3.

I've asked this question elsewhere and have been given a suggestion to
modify these statements to use a cast, eg: WHERE (u.username =
cast(u.customer_id as string))
Unfortunately I don't have access to the application source, so I'm not sure
if this type of change will have any impact on the application itself.

I was hoping I might be able to re-create the varchar() cast function(s) but
googling has only come up with examples of failed attempts.

Does anyone have any advice/ideas?

TIA,
Sean

Search Discussions

  • Tom Lane at Jun 22, 2005 at 2:19 pm

    "Sean Cardus" <postgres@cardus.com> writes:
    For example, one of the CREATE VIEW statements contains:
    WHERE (u.username = "varchar"(u.customer_id))
    If you can change "varchar" to "text" it should still work.

    regards, tom lane
  • Sean Cardus at Jun 22, 2005 at 3:49 pm

    Tom Lane <> wrote the following on 22 June 2005 15:20:

    "Sean Cardus" <postgres@cardus.com> writes:
    For example, one of the CREATE VIEW statements contains:
    WHERE (u.username = "varchar"(u.customer_id))
    If you can change "varchar" to "text" it should still work.
    Thanks for the advice Tom - I've tested this and the database does import
    correctly. I'm just concerned that the application might be expecting a
    varchar column type in the returned data.

    I've contacted the application developers and I'm still waiting for a
    reply...

    *crosses fingers*

    Thanks again,
    Sean

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 22, '05 at 1:55p
activeJun 22, '05 at 3:49p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Sean Cardus: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase