Re: pg_catalog.pg_get_serial_sequence() returns NULL

[Jim Nasby <decibel(at)decibel(dot)org>]

On Jun 26, 2007, at 10:28 AM, Sergey Karin wrote:
> I use PG 8.1.5
>
> I execute in psql next comands:
>
> create table t_table (gid serial, name varchar);
>
> select pg_catalog.pg_get_serial_sequence('t_table', 'gid');
>  pg_get_serial_sequence
> ------------------------
>  public.t_table_gid_seq
>
> create table t_table_1() inherits (t_table);
>
> \d t_table_1
>                              Table "public.t_table_1"
>  column |       Type        |                     Модификаторы
> ---------+------------------- 
> +-------------------------------------------------------
>  gid     | integer           | not null default nextval 
> ('t_table_gid_seq'::regclass)
>  name    | character varying |
> Inherit: t_table
>
> select pg_catalog.pg_get_serial_sequence('t_table_1', 'gid');
>  pg_get_serial_sequence
> ------------------------
> <NULL>

The issue is that t_table_1.gid isn't actually a SERIAL column...  
it's an int that pulls a default value from the t_table_gid_seq  
sequence. Note that that sequence is the one defined for t_table.

In this case for right now you're stuck hard-coding the sequence name  
in, or referring to the parent table :(.

> How I can get name of sequence used by gid column of partition  
> table? And why for t_table_1 pg_get_serial_sequence() returns NULL?
> It returns NULL also if I create new sequence manually and use  
> ALTER TABLE ALTER COLUMN <column> SET DEFAULT nextval 
> (<my_new_sequence>::regclass).
>
> In http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE- 
> SERIAL I can read that type SERIAL is equivalent to
> CREATE SEQUENCE tablename_colname_seq;
> CREATE TABLE tablename (
> colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
> );Thanks in advance
>
> Sergey Karin

--
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)