PostgreSQL – How to Create Sequence and Table Simultaneously

postgresqlsequence

I need to create a table with a sequence associated to the gid column that would start from a given value, say 300. I'd like to know if there is a shorter way to achieve that:

CREATE TABLE mytable (gid INTEGER NOT NULL, size INTEGER);

CREATE SEQUENCE mytable_gid_seq MINVALUE 300;

ALTER TABLE mytable ALTER COLUMN gid SET DEFAULT nextval('mytable_gid_seq'::regclass);

Thanks for help!

Best Answer

You can create the sequence implicitely through the serial (pseudo) type and then change its current value:

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER); 
select setval('mytable_gid_seq', 100);

or alternatively using

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER); 
alter sequence mytable_gid_seq restart with 100;

This is slightly different than defining a minvalue - but that would only matter if the sequence was set to wrap. This version also creates a link between the sequence and the table, so that the sequence is automatically dropped when the table is dropped (in your script you would need to alter sequence mytable_gid_seq owner to mytable.gid);

Related Solutions

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

How about using with recursive

test view:

create view v as 
select *
from ( values ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) as baz ("name", "int");

query:

with recursive t("name", "int") as ( select "name", "int", 1 as span from v
                                     union all
                                     select "name", v."int", t.span+1 as span
                                     from v join t using ("name")
                                     where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
      from ( select "name", "int", max(span) as span 
             from t
             group by "name", "int" ) z
      group by "name", ("int"-span+1) ) z;

result:

 name | start | end | span
------+-------+-----+------
 foo  |     2 |   4 |    3
 foo  |    13 |  13 |    1
 bar  |     1 |   3 |    3
 foo  |    10 |  11 |    2
(4 rows)

I'd be interested to know how that performs on your billion row table.

How to Determine if a Column is Defined as Serial Data Type in PostgreSQL

SERIAL and BIGSERIAL are kind of pseudo-types. As you noticed, they are really just INT and BIGINT internally.

What happens behind the scenes is that PostgreSQL creates a sequence and sets up a dependency on it to the table. You can search pg_class for the sequence name and how it relates to the table.

pg_class: http://www.postgresql.org/docs/9.2/static/catalog-pg-class.html

SQL Fiddle: http://sqlfiddle.com/#!12/dfcbd/6

Sequence Functions: http://www.postgresql.org/docs/9.2/static/functions-sequence.html

This StackOverflow post might be helpful: https://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql

UPDATE: You can also use pg_depend to figure out which sequences relate to the table /column you are interested in: http://www.postgresql.org/docs/9.2/static/catalog-pg-depend.html

Best Answer

Related Solutions

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

How to Determine if a Column is Defined as Serial Data Type in PostgreSQL

Related Question