3

How come that statement don't copy constraints if I explicitly ask for it?

CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS);
INSERT INTO backup_schema.permissions
    SELECT *
    FROM public.permissions
    WHERE community_id=123;

verifying with pgadmin3 I discovered that constraints on the new table(eg.:backup_schema) was: enter image description here

but in the original table in public schema was like this: enter image description here

4

The documentation says about INCLUDING CONSTRAINTS (emphasis mine):

Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. Also, no distinction is made between column constraints and table constraints — when constraints are requested, all check constraints are copied.

Foreign keys obviously fall into this 'other constraints' group, this is why the two FKs of yours are not copied over.

Interestingly, the documentation for version 9.3 has a different wording, not stating the above so clearly.

| improve this answer | |
  • 1
    9.4 and devel still have the same wording. Will you submit a patch to the documentation? If not, I will do it. – Colin 't Hart Apr 21 '15 at 8:55
  • @Colin'tHart could you? Thanks a lot. – dezso Apr 21 '15 at 9:06
  • again thankyou for your reply, it gave me a nice push to fix my problem – lese Apr 22 '15 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.