PostgreSQL - is it possible to create a table with a specific sequence value?

Question

I want to create a table in a PostgreSQL database on a given schema. The table is defined like this:

CREATE TABLE ventajon.altas_sorteo_crucero (
    id_alta serial NOT NULL,
    fecha timestamp without time zone NOT NULL,
    nombre character varying(100) NOT NULL,
    apellidos character varying(100) NOT NULL,
    cifnif character varying(50) NOT NULL,
    email character varying(320) NOT NULL,
    telefono character varying(50) NOT NULL
) WITH (
    OIDS=FALSE
);

I'm not a guru with PostgreSQL so, for now, if I want to restart the autoincremental value represented by the id_alta column, I open a SQL console and execute this:

ALTER SEQUENCE ventajon.altas_sorteo_crucero_id_alta_seq RESTART WITH 1;

However, I would like to know if it could be possible to set a predefined value for the sequence itself. What if I need for id_alta to start at, let's say, 50? Or... what if I want to tell this table to admit any insertion as long as the id_alta falls between 50 and 250?

It's perfectly ok if I need to accomplish this like I do right now, in two queries. Just... I would like to know if this could be accomplished in a single step.

Is it possible?

No, not necessarily. My interpretation of the second case is new rows would be allowed in as long as the ID falls between the given range. As soon as it's out of it, no row would be inserted. However, it's theory for me. I still have to test it. — Julio María Meca Hansen
– Julio María Meca Hansen, Commented Sep 29, 2015 at 13:59

user330315 · Accepted Answer · 2015-09-29 13:59:05Z

You can't influence the sequence that is created automatically when using the serial type. But you can achieve what you want by manually creating the sequence:

create sequence altas_sorteo_crucero_id_alta_seq
   start with 50
   minvalue 50
   maxvalue 250;

CREATE TABLE ventajon.altas_sorteo_crucero (
    id_alta integer NOT NULL default nextval('altas_sorteo_crucero_id_alta_seq'),
    fecha timestamp without time zone NOT NULL,
    nombre character varying(100) NOT NULL,
    apellidos character varying(100) NOT NULL,
    cifnif character varying(50) NOT NULL,
    email character varying(320) NOT NULL,
    telefono character varying(50) NOT NULL
) ;

However this will not prevent someone from manually providing a value for id_alta that is outside of the range 50-250. If you want to limit that as well, you need a check constraint:

alter table ventajon.altas_sorteo_crucero
  add constraint checK_id_alta check (id_alta between 50 and 205));

If you also want to associate the sequence with the column, so that the sequence is automatically dropped when you drop the table, you can do that using:

alter sequence altas_sorteo_crucero_id_alta_seq
   owned by altas_sorteo_crucero.id_alta;

Collectives™ on Stack Overflow

PostgreSQL - is it possible to create a table with a specific sequence value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related