Thread: Default Value in Table Setup Help

Hello all,

I am working on setting up a table that will append a sequence to the end
of the value inserted.
Example;
INSERT INTO test (test) VALUES ('abcd');
And have the data in the database be;
abcd0001

Now I do have things setup else where were the default value for the field
is such:
default ('abcd'::text || lpad(text(nextval('test_sequence'::text)), 4,
'0'::text))

But i want the "abcd" or what erver to be added in the insert?

What am I missing to make this happen?

At 02:57 PM 8/18/03 -0400, Dev wrote:
>Hello all,
>
>I am working on setting up a table that will append a sequence to the end
>of the value inserted.
>Example;
>INSERT INTO test (test) VALUES ('abcd');
>And have the data in the database be;
>abcd0001
>
>Now I do have things setup else where were the default value for the field
>is such:
>default ('abcd'::text || lpad(text(nextval('test_sequence'::text)), 4,
>'0'::text))
>
>But i want the "abcd" or what erver to be added in the insert?
>
>What am I missing to make this happen?

I don't think you can do what you want to do with a column
default.  Instead you want to look at the rewrite rules.  You can find
information about them here:

http://www.postgresql.org/docs/7.3/interactive/sql-createrule.html

If I'm wrong I'm sure someone here will correct me :).

--- Adam Kavan
--- akavan@cox.net

On Mon, 18 Aug 2003, Dev wrote:

> Hello all,
>
> I am working on setting up a table that will append a sequence to the end
> of the value inserted.
> Example;
> INSERT INTO test (test) VALUES ('abcd');
> And have the data in the database be;
> abcd0001
>
> Now I do have things setup else where were the default value for the field
> is such:
> default ('abcd'::text || lpad(text(nextval('test_sequence'::text)), 4,
> '0'::text))
>
> But i want the "abcd" or what erver to be added in the insert?
>
> What am I missing to make this happen?

You don't want to use a default (since that'll be ignored if you actually
pass in a value for the column).  You probably want a before trigger that
alters the value that was inserted before the insertion actually happens.

On Mon, Aug 18, 2003 at 14:57:12 -0400,
  Dev <dev@umpa-us.com> wrote:
> Hello all,
>
> I am working on setting up a table that will append a sequence to the end
> of the value inserted.
> Example;
> INSERT INTO test (test) VALUES ('abcd');
> And have the data in the database be;
> abcd0001
>
> Now I do have things setup else where were the default value for the field
> is such:
> default ('abcd'::text || lpad(text(nextval('test_sequence'::text)), 4,
> '0'::text))
>
> But i want the "abcd" or what erver to be added in the insert?
>
> What am I missing to make this happen?

I think you want to use a trigger to do this. The default function only
gets used if you don't supply a value.