CODEWITHSUNDEEP
postgresqlsequencecreate-table
Julio María Meca Hansen
Julio María Meca Hansen

Reputation: 1268

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

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?

Upvotes: 1

Views: 1116

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 3

Related Questions