How to set SEQUENCE as DEFAULT in Postgres-XL?

Question

I use Postgres-XL 10R1. I created table packs and sequence packs_id_seq same SQL:

CREATE TABLE packs (
    id integer NOT NULL,
    name varchar(10) NOT NULL
) DISTRIBUTE BY REPLICATION;

CREATE SEQUENCE packs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

I tried to use packs_id_seq as default value for id column.

ALTER TABLE packs ALTER COLUMN id SET DEFAULT (nextval('packs_id_seq'));

But have some problem

INSERT INTO packs (name) VALUES ('test');
INSERT 0 0

Why rows didn't insert?

So, I change clause for DISTRIBUTE.

ALTER TABLE packs DISTRIBUTE BY ROUNDROBIN;
ALTER TABLE packs DISTRIBUTE BY MODULO(id);
ALTER TABLE packs DISTRIBUTE BY HASH(id);

But have same problem again.

Any ideas? Thanks.

score 0 · Answer 1 · answered Dec 18 '18 at 13:02

You can use "serial" datatype here, so your create table will be like

CREATE TABLE packs (
    id serial NOT NULL,
    name varchar(10) NOT NULL
) DISTRIBUTE BY REPLICATION;

and now when inserting records,

INSERT INTO packs (name) VALUES ('test');

it will have record like

id | name 
1  | test

How to set SEQUENCE as DEFAULT in Postgres-XL?

1 Answers1