Thread: convert 'foobar'::text to the string foobar

Hello,

I want to retrieve the default value of a TEXT column of a table.
So far, I had a hard time writing this query and after looking at
postgresql doc I have looked in phppgadmin source code, and am
finally using:

   SELECT adef.adsrc AS value
     FROM pg_catalog.pg_attribute a
     LEFT JOIN pg_catalog.pg_attrdef adef
            ON a.attrelid = adef.adrelid
           AND a.attnum = adef.adnum
    WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = ?)
      AND a.attname = ?

when used with a table with a default value set by:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'foobar';

displaying that way with \d:

 column_name   | text                   | not null default 'foobar'::text

retrieved as a String in Java, I end up with "'foobar'::text"
where I'd need just the string "foobar" (without any quote).

Is there something I'm doing wrong?

Thank you.

--
Guillaume Cottenceau

On Fri, 13 May 2005, Guillaume Cottenceau wrote:

> I want to retrieve the default value of a TEXT column of a table.

You might want to consider DatabaseMetaData.getColumns() and using the
COLUMN_DEF column.

> ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'foobar';
>
> displaying that way with \d:
>
>  column_name   | text                   | not null default 'foobar'::text
>
> retrieved as a String in Java, I end up with "'foobar'::text"
> where I'd need just the string "foobar" (without any quote).
>

That's how it is stored internally and there's not really much we can do
about it.  If you know it's going to be 'xxx'::sometype it would be easy
to parse, but the driver can make no such assumptions.

Kris Jurka

Kris Jurka <books 'at' ejurka.com> writes:

> On Fri, 13 May 2005, Guillaume Cottenceau wrote:
>
> > I want to retrieve the default value of a TEXT column of a table.
>
> You might want to consider DatabaseMetaData.getColumns() and using the
> COLUMN_DEF column.

Ah, yes, this is cleaner. Thanks Kris.

--
Guillaume Cottenceau