reference to a sequence column (postgresql)

Question

I encountered a problem when creating a foreign key referencing to a sequence, see the code example below.
But on creating the tables i recieve the following error.
"Detail: Key columns "product" and "id" are of incompatible types: integer and ownseq"
I've already tried different datatypes for the product column (like smallint, bigint) but none of them is accepted.

CREATE SEQUENCE ownseq INCREMENET BY 1 MINVALUE 100 MAXVALUE 99999;  
CREATE TABLE products (  
id ownseq PRIMARY KEY,  
...);

CREATE TABLE basket (
basket_id SERIAL PRIMARY KEY,
product INTEGER FOREIGN KEY REFERENCES products(id));

Matthias · Accepted Answer · 2014-06-01 12:46:47Z

4

CREATE SEQUENCE ownseq INCREMENT BY 1 MINVALUE 100 MAXVALUE 99999;  
CREATE TABLE products (  
    id integer PRIMARY KEY default nextval('ownseq'),
    ...
);
alter sequence ownseq owned by products.id;

The key change is that id is defined as an integer, rather than as ownseq. This is what would happen if you used the SERIAL pseudo-type to create the sequence.

edited Jun 1, 2014 at 12:46

Matthias

12.3k4 gold badges53 silver badges97 bronze badges

answered Feb 28, 2013 at 14:17

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Claude · Accepted Answer · 2013-02-28 14:24:34Z

2

Try

    CREATE TABLE products (  
    id INTEGER DEFAULT nextval(('ownseq'::text)::regclass) NOT NULL PRIMARY KEY,  
    ...);

or don't create the sequence ownseq and let postgres do it for you:

    CREATE TABLE products (
    id SERIAL NOT NULL PRIMARY KEY
    ...);

In the above case the name of the sequence postgres has create should be products_id_seq.

Hope this helps.

answered Feb 28, 2013 at 14:24

Claude

1,8043 gold badges20 silver badges54 bronze badges

4 Comments

Clodoaldo Neto Over a year ago

primary key implies not null so the latter is not necessary.

Clodoaldo Neto Over a year ago

And yes, depending on the version a cast to regclass is necessary.

D Jake Over a year ago

Does this syntax creates the sequence within the "create table"statement? And would it be possible to set a minimum value and maximum value like the original statement?

Claude Over a year ago

@DJake Do you mean the serialsyntax? If yes see the documentation of serial. It is merely a notational convenience for creating unique identifier columns and its equivalent is a series of statements. One of those statements is a CREATE SEQUENCE tablename_colname_seq. Minimum or maximum values seem not to be possible here.

Chris Travers · Accepted Answer · 2013-03-02 01:57:06Z

PostgreSQL is powerful and you have just been bitten by an advanced feature.

Your DDL is quite valid but not at all what you think it is.

A sequence can be thought of as an extra-transactional simple table used for generating next values for some columns.

What you meant to do

You meant to have the id field defined thus, as per the other answer:

id integer PRIMARY KEY default nextval('ownseq'),

What you did

What you did was actually define a nested data structure for your table. Suppose I create a test sequence:

CREATE SEQUENCE testseq;

Then suppose I \d testseq on Pg 9.1, I get:

           Sequence "public.testseq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | testseq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

This is the definition of the type the sequence used.

Now suppose I:

 create table seqtest (test testseq, id serial);

I can insert into it:

 INSERT INTO seqtest (id, test) values (default, '("testseq",3,4,1,133445,1,1,0,f,f)');

I can then select from it:

select * from seqtest;
               test               | id 
----------------------------------+----
 (testseq,3,4,1,133445,1,1,0,f,f) |  2

Moreover I can expand test:

SELECT (test).* from seqtest;

select (test).* from seqtest;
 sequence_name | last_value | start_value | increment_by | max_value | min_value
 | cache_value | log_cnt | is_cycled | is_called 
---------------+------------+-------------+--------------+-----------+----------
-+-------------+---------+-----------+-----------
               |            |             |              |           |          
 |             |         |           | 
 testseq       |          3 |           4 |            1 |    133445 |         1
 |           1 |       0 | f         | f
(2 rows)

This sort of thing is actually very powerful in PostgreSQL but full of unexpected corners (for example not null and check constraints don't work as expected with nested data types). I don't generally recommend nested data types, but it is worth knowing that PostgreSQL can do this and will be happy to accept SQL commands to do it without warning.

Collectives™ on Stack Overflow

reference to a sequence column (postgresql)

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related