Re: derive the sequence name of a column

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Mel Jamero <mel(at)gmanmi(dot)tv>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: derive the sequence name of a column
Date: 2003-10-27 03:45:27
Message-ID: 20031027034527.GB11800@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Fri, Oct 24, 2003 at 15:41:00 +0800,
Mel Jamero <mel(at)gmanmi(dot)tv> wrote:
> Hi!

Please don't reply to messages to start new threads.

>
> Can anyone please tell me exactly how the name of a sequence a field is
> using (manually created or generated by a serial) could be derived
> programmatically (using libpq or through SQL)?

When creating a sequence for a serial type, for short names the format is
tablename underline columnname underline seq . If the resulting name is longer
than 63 characters then something else is used so as to get a shorter name.
If you have control over the table and column names than you can make sure the
names are always short enough so that the simple algorithm is used.

For manually created references to sequences, you will probably need to
parse the default value. I don't know how to do this, but using the
-E on pgsql and using \d sampletable should show you what query to use.

>
> Thus:
>
> CREATE SEQUENCE an_unknown_sequence_name;
> CREATE TABLE test (
> test_id integer default nextval('an_unknown_sequence_name'),
> useless_redundant_test_id serial
> );
>
> How do I programmatically extract that column 'test_id' in table 'test'
> is using 'an_unknown_sequence_name'

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Paul Ganainm 2003-10-27 11:56:21 Using older machines with PostgreSQL?
Previous Message Moshe Vardi 2003-10-26 21:32:47 Deadlock Detection