On 03/06/15 08:40, Andreas Ulbrich wrote:
On 02.06.2015 22:12, Melvin Davidson wrote:
Your problem is in your design.
If you do it like this:
CREATE TABLE A
(
p_col serial PRIMARY KEY,
acol integer
);
CREATE TABLE B() INHERITS (A);
INSERT INTO A(acol) VALUES (1);
INSERT INTO B(acol) VALUES (2);
SELECT * FROM A;
SELECT * FROM B;
Then the sequence (p_col) will be UNIQUE across all tables and can be
referenced.
No need for a key table.
No, someone can do:
INSERT INTO A VALUES (2,3);
TABLE A;
shows:
p_col | acol
-------+------
1 | 1
2 | 2
2 | 3
p_col is not unique!
Curious, I tried to investigate, to get a better understanding and ran
into a problem...
$ psql
psql (9.4.1)
Type "help" for help.
gavin=> CREATE TABLE A
gavin-> (
gavin(> p_col serial PRIMARY KEY,
gavin(> acol integer
gavin(> );
CREATE TABLE
gavin=> CREATE TABLE B() INHERITS (A);
CREATE TABLE
gavin=> INSERT INTO A(acol) VALUES (1);
ERROR: column "acol" of relation "a" does not exist
LINE 1: INSERT INTO A(acol) VALUES (1);
^
gavin=> \d+ a
Table "public.a"
Column | Type |
Modifiers | Storage | Stats target | Description
--------+---------+---------------------------------------------------+---------+--------------+-------------
p_col | integer | not null default
nextval('a_p_col_seq'::regclass) | plain | |
acol | integer
| | plain
| |
Indexes:
"a_pkey" PRIMARY KEY, btree (p_col)
Child tables: b
gavin=> \d b
Table "public.b"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
p_col | integer | not null default nextval('a_p_col_seq'::regclass)
acol | integer |
Inherits: a
gavin=>
[...]
Cheers,
Gavin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
