Обсуждение: Partitions Table

Dear all,

i want to create partitions table :

*create table advertisements_otomotif (CHECK (select * from
advertisements where category_id in (select id from categories where
name='Otomotif'))) INHERITS (advertisements)
*

but i have error like this:

*ERROR:  syntax error at or near "select"
LINE 1: create table advertisements_otomotif (CHECK (select * from a...
                                                     ^


********** Error **********

ERROR: syntax error at or near "select"
SQL state: 42601
Character: 46

*any have sollutions?

thanks

idris khanafi <idris.khanafi@detik.com> writes:
> i want to create partitions table :

> *create table advertisements_otomotif (CHECK (select * from
> advertisements where category_id in (select id from categories where
> name='Otomotif'))) INHERITS (advertisements)
> *

This is completely confused.  I'm not sure what you are trying to
accomplish, but you've got at least three problems here:

1. Sub-selects used in expressions need to have their own set of
parentheses, ie you'd need something more like CHECK ((select ...))
to get past that syntax error.

2. The sub-select doesn't appear to return boolean, which is what
the CHECK would require.

3. Sub-selects aren't permitted in CHECK constraints, because they
would almost certainly do the wrong thing --- CHECKs are only checked
when a row in the current table is inserted or updated.  So for example
modification or deletion of a row in the other table might make the
check expression no longer true, but the system wouldn't notice.

I think you might be trying to re-invent foreign keys, but without
an explanation of what it is you hope to accomplish, it's hard to
be sure.

            regards, tom lane