Postgresql Creating a Schema with tables in it

Question

Here's my script.

CREATE SCHEMA testSchema
        create table REF_PRODUCT (
            id int8 not null,
            created_date timestamp,
            CODE varchar(255),
            DESCRIPTION varchar(255),
            LOCATION varchar(255),
            MANUFACTURER varchar(255),
            NAME varchar(255),
            COST numeric(19, 2),
            DEALERS_PRICE numeric(19, 2),
            SUGGESTED_RETAIL_PRICE numeric(19, 2),
            STOCK int4,
            PRODUCT_TYPE varchar(255),
            picture_id int8,
            primary key (id)
        )

        create table TXN_LINE_ITEM (
            id int8 not null,
            created_date timestamp,
            cancelled boolean,
            cost numeric(19, 2),
            dp numeric(19, 2),
            itemCode varchar(255),
            itemName varchar(255),
            priceType varchar(255),
            qty int4,
            refunded boolean,
            srp numeric(19, 2),
            total numeric(19, 2),
            transactionRecord_id int8,
            primary key (id)
        )

        create table TXN_PRODUCT_PICTURE (
            id int8 not null,
            created_date timestamp,
            picture bytea,
            primary key (id)
        )

        create table TXN_TRANSACTION_RECORD (
            id int8 not null,
            created_date timestamp,
            total numeric(19, 2),
            transaction_date timestamp,
            TRANSACTION_NUMBER varchar(255),
            VALID boolean,
            primary key (id)
        )

        alter table REF_PRODUCT
            add constraint FK_sjugahpelk16qj5h3w8dli42l
            foreign key (picture_id)
            references TXN_PRODUCT_PICTURE;

        alter table TXN_LINE_ITEM
            add constraint FK_o5mslaahpil9d3g9rl2s22rpm
            foreign key (transactionRecord_id)
            references TXN_TRANSACTION_RECORD;

        create table SEQ_ENTITY_ID (
             sequence_name varchar(255),
             sequence_next_hi_value int4
        );

The problem is the fk relationships specified in alter table statement is not being created

Because of this error.

[WARNING  ] CREATE SCHEMA testSchema
                    create table REF_PRODUCT (
                        id int8 not null,
                        created_date timestamp,
                        CODE varchar(255),
                        DESCRIPTION varchar(255),
                        LOCATION varchar(255),
                        MANUFACTURER varchar(255),
                        NAME varchar(255),
                        COST numeric(19, 2),
                        DEALERS_PRICE numeric(19, 2),
                        SUGGESTED_RETAIL_PRICE numeric(19, 2),
                        STOCK int4,
                        PRODUCT_TYPE varchar(255),
                        picture_id int8,
                        primary key (id)
                    )

                    create table TXN_LINE_ITEM (
                        id int8 not null,
                        created_date timestamp,
                        cancelled boolean,
                        cost numeric(19, 2),
                        dp numeric(19, 2),
                        itemCode varchar(255),
                        itemName varchar(255),
                        priceType varchar(255),
                        qty int4,
                        refunded boolean,
                        srp numeric(19, 2),
                        total numeric(19, 2),
                        transactionRecord_id int8,
                        primary key (id)
                    )

                    create table TXN_PRODUCT_PICTURE (
                        id int8 not null,
                        created_date timestamp,
                        picture bytea,
                        primary key (id)
                    )

                    create table TXN_TRANSACTION_RECORD (
                        id int8 not null,
                        created_date timestamp,
                        total numeric(19, 2),
                        transaction_date timestamp,
                        TRANSACTION_NUMBER varchar(255),
                        VALID boolean,
                        primary key (id)
                    )

                    alter table REF_PRODUCT
                        add constraint FK_sjugahpelk16qj5h3w8dli42l
                        foreign key (picture_id)
                        references TXN_PRODUCT_PICTURE
            ERROR:  syntax error at or near "alter"
            LINE 53:         alter table REF_PRODUCT
                             ^
[WARNING  ] alter table TXN_LINE_ITEM
                        add constraint FK_o5mslaahpil9d3g9rl2s22rpm
                        foreign key (transactionRecord_id)
                        references TXN_TRANSACTION_RECORD
            ERROR:  relation "txn_line_item" does not exist
[WARNING  ] create table SEQ_ENTITY_ID (
                         sequence_name varchar(255),
                         sequence_next_hi_value int4
                    )
            ERROR:  relation "seq_entity_id" already exists

NOTE THAT: There's no existing tables on the said Schema I have made. What have I missed?

I don't think the error message schema "tenant19" already exists leaves much room for interpretation .... — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 17, 2015 at 4:05
The problem is, the alter_table command/fk relationshipss is not being generated — user962206
– user962206, Commented Apr 17, 2015 at 5:14
If you always want to re-create the whole schema (which I doubt), you could put a drop schema if exists tenant19 cascade before your create schema statement. But it's probably better to not put everything into a single statement, but run each step separately and add an if not exists to each create statement. — user330315
– user330315, Commented Apr 17, 2015 at 5:56
From the manual - "An SQL statement defining an object to be created within the schema. Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER and GRANT are accepted as clauses within CREATE SCHEMA". You can't put an ALTER TABLE inside a CREATE SCHEMA statement. you need to define the FKs inline. — user330315
– user330315, Commented Apr 17, 2015 at 12:59
@a_horse_with_no_name care to give an example? would like to see that as answer and accept it. — user962206
– user962206, Commented Apr 17, 2015 at 13:45

user330315 · Accepted Answer · 2015-04-17 13:59:17Z

2

I think you have two ways of doing this.

The first one: don't run one create schema statement, but run individual statements for each part (this is what I prefer). You can still do that as a single transaction:

begin transaction;
CREATE SCHEMA testSchema; -- only create the namespace

-- make the new schema the default schema
-- so the the tables do not need to be full qualified
set search_path = testschema;

create table REF_PRODUCT (
...
);

create table TXN_LINE_ITEM (
...
);

create table TXN_PRODUCT_PICTURE (
...
);

create table TXN_TRANSACTION_RECORD (
...
);

alter table REF_PRODUCT
    add constraint fk_product_picture
    foreign key (picture_id)
    references TXN_PRODUCT_PICTURE;

alter table TXN_LINE_ITEM
    add constraint FK_line_item_trans_record
    foreign key (transactionRecord_id)
    references TXN_TRANSACTION_RECORD;

create table SEQ_ENTITY_ID (
     sequence_name varchar(255),
     sequence_next_hi_value int4
);

commit;

The other option is to move the foreign keys into the table definition, but then you need to re-order your create table statements:

CREATE SCHEMA testSchema

  create table TXN_PRODUCT_PICTURE (
  ...
  )

  create table TXN_TRANSACTION_RECORD (
  ...
  )

  create table REF_PRODUCT (
    ...,
    constraint fk_product_picture
    foreign key (picture_id)
    references TXN_PRODUCT_PICTURE
  )

  create table TXN_LINE_ITEM (
    ...
    constraint FK_line_item_trans_record
      foreign key (transactionRecord_id)
      references TXN_TRANSACTION_RECORD
  )

  create table SEQ_ENTITY_ID (
       sequence_name varchar(255),
       sequence_next_hi_value int4
  )
  ;

Unrelated, but: What is this SEQ_ENTITY_ID table for? Why don't you use native Postgres sequences. They will be much faster, more scalable and more robust than anything you can do in your application to generate unique numbers

answered Apr 17, 2015 at 13:59

user330315

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

3 Comments

user962206 Over a year ago

SEQ_ENTITY_ID is generated automatically by hibernate for me

user330315 Over a year ago

@user962206: then tell your obfuscation layer (aka ORM) to use native sequences.

Erwin Brandstetter Over a year ago

I shall have to include "obfuscation layer" for ORMs in my vocabulary. Very much to the point. :)

Collectives™ on Stack Overflow

Postgresql Creating a Schema with tables in it

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related