Postgres Pro
Downloads
Home   >   mailing lists

Thread: alter table

alter table

From
Pavel SRB
Date:
hi all

please i have one question. I made table called tab_article with

CREATE TABLE "tab_article" (
    "id" serial NOT NULL PRIMARY KEY,
    "flag" boolean NOT NULL,
    "title" text NOT NULL,
    "journal" integer NOT NULL,
    "doi" varchar(255) NOT NULL,
    "year" integer NOT NULL,
    "volume" integer NOT NULL,
    "issue" integer NOT NULL,
    "pages_start" integer NOT NULL,
    "pages_end" integer NOT NULL,
    "printed" boolean NOT NULL,
    "public" boolean NOT NULL,
    "entry" boolean NOT NULL,
    "filename" integer NOT NULL,
    "language" integer NOT NULL,
    "link" integer NOT NULL,
    "info" varchar(255) NOT NULL
);

and later another table called tab_journal

CREATE TABLE "tab_journal" (
    "id" serial NOT NULL PRIMARY KEY,
    "flag" boolean NOT NULL,
    "name" text NOT NULL,
    "publisher" integer NOT NULL,
    "issn_print" integer NOT NULL,
    "issn_online" integer NOT NULL,
    "link" integer NOT NULL,
    "date_range" varchar(255) NOT NULL
);

and i needed to change column journal in tab_article to be set like
"journal_id" integer NOT NULL REFERENCES "article_journal" ("id")

and i am not able to find the right alter table command.
thank you very much

pavel

Re: alter table

From
"Sean Davis"
Date:
On Mon, Apr 7, 2008 at 4:53 PM, Pavel SRB <srb@heckler-koch.cz> wrote:
> hi all
>
>  please i have one question. I made table called tab_article with
>
>  CREATE TABLE "tab_article" (
>    "id" serial NOT NULL PRIMARY KEY,
>    "flag" boolean NOT NULL,
>    "title" text NOT NULL,
>    "journal" integer NOT NULL,
>    "doi" varchar(255) NOT NULL,
>    "year" integer NOT NULL,
>    "volume" integer NOT NULL,
>    "issue" integer NOT NULL,
>    "pages_start" integer NOT NULL,
>    "pages_end" integer NOT NULL,
>    "printed" boolean NOT NULL,
>    "public" boolean NOT NULL,
>    "entry" boolean NOT NULL,
>    "filename" integer NOT NULL,
>    "language" integer NOT NULL,
>    "link" integer NOT NULL,
>    "info" varchar(255) NOT NULL
>  );
>
>  and later another table called tab_journal
>
>  CREATE TABLE "tab_journal" (
>    "id" serial NOT NULL PRIMARY KEY,
>    "flag" boolean NOT NULL,
>    "name" text NOT NULL,
>    "publisher" integer NOT NULL,
>    "issn_print" integer NOT NULL,
>    "issn_online" integer NOT NULL,
>    "link" integer NOT NULL,
>    "date_range" varchar(255) NOT NULL
>  );
>
>  and i needed to change column journal in tab_article to be set like
>  "journal_id" integer NOT NULL REFERENCES "article_journal" ("id")
>
>  and i am not able to find the right alter table command.
>  thank you very much

You will want to add the NOT NULL as one statement.  Then, add the
foreign key constraint as a second statement.  There are examples of
how to do each here:

http://www.postgresql.org/docs/8.3/static/sql-altertable.html

Hope that helps.

Sean

Re: alter table

From
Pavel SRB
Date:
Sean Davis wrote:
> On Mon, Apr 7, 2008 at 4:53 PM, Pavel SRB <srb@heckler-koch.cz> wrote:
>
>> hi all
>>
>>  please i have one question. I made table called tab_article with
>>
>>  CREATE TABLE "tab_article" (
>>    "id" serial NOT NULL PRIMARY KEY,
>>    "flag" boolean NOT NULL,
>>    "title" text NOT NULL,
>>    "journal" integer NOT NULL,
>>    "doi" varchar(255) NOT NULL,
>>    "year" integer NOT NULL,
>>    "volume" integer NOT NULL,
>>    "issue" integer NOT NULL,
>>    "pages_start" integer NOT NULL,
>>    "pages_end" integer NOT NULL,
>>    "printed" boolean NOT NULL,
>>    "public" boolean NOT NULL,
>>    "entry" boolean NOT NULL,
>>    "filename" integer NOT NULL,
>>    "language" integer NOT NULL,
>>    "link" integer NOT NULL,
>>    "info" varchar(255) NOT NULL
>>  );
>>
>>  and later another table called tab_journal
>>
>>  CREATE TABLE "tab_journal" (
>>    "id" serial NOT NULL PRIMARY KEY,
>>    "flag" boolean NOT NULL,
>>    "name" text NOT NULL,
>>    "publisher" integer NOT NULL,
>>    "issn_print" integer NOT NULL,
>>    "issn_online" integer NOT NULL,
>>    "link" integer NOT NULL,
>>    "date_range" varchar(255) NOT NULL
>>  );
>>
>>  and i needed to change column journal in tab_article to be set like
>>  "journal_id" integer NOT NULL REFERENCES "article_journal" ("id")
>>
>>  and i am not able to find the right alter table command.
>>  thank you very much
>>
>
> You will want to add the NOT NULL as one statement.  Then, add the
> foreign key constraint as a second statement.  There are examples of
> how to do each here:
>
> http://www.postgresql.org/docs/8.3/static/sql-altertable.html
>
> Hope that helps.
>
> Sean
>
thank you for fast replay. I have read this one tutorial before i posted
this here, but with no luck.

ALTER TABLE l_article ADD CONSTRAINT journal_id FOREIGN KEY (journal_id)
REFERENCES l_journal (id);

this one stops my psql terminal and did not change anything. Am i
missing the not null statement?

ALTER TABLE "l_article" ADD CONSTRAINT "journal_id" integer NOT NULL
REFERENCES FOREIGN KEY ("journal_id") REFERENCES "l_journal" (id);

thank you
pavel

Re: alter table

From
"Sean Davis"
Date:
On Mon, Apr 7, 2008 at 5:18 PM, Pavel SRB <srb@heckler-koch.cz> wrote:
>
> Sean Davis wrote:
>
> > On Mon, Apr 7, 2008 at 4:53 PM, Pavel SRB <srb@heckler-koch.cz> wrote:
> >
> >
> > > hi all
> > >
> > >  please i have one question. I made table called tab_article with
> > >
> > >  CREATE TABLE "tab_article" (
> > >   "id" serial NOT NULL PRIMARY KEY,
> > >   "flag" boolean NOT NULL,
> > >   "title" text NOT NULL,
> > >   "journal" integer NOT NULL,
> > >   "doi" varchar(255) NOT NULL,
> > >   "year" integer NOT NULL,
> > >   "volume" integer NOT NULL,
> > >   "issue" integer NOT NULL,
> > >   "pages_start" integer NOT NULL,
> > >   "pages_end" integer NOT NULL,
> > >   "printed" boolean NOT NULL,
> > >   "public" boolean NOT NULL,
> > >   "entry" boolean NOT NULL,
> > >   "filename" integer NOT NULL,
> > >   "language" integer NOT NULL,
> > >   "link" integer NOT NULL,
> > >   "info" varchar(255) NOT NULL
> > >  );
> > >
> > >  and later another table called tab_journal
> > >
> > >  CREATE TABLE "tab_journal" (
> > >   "id" serial NOT NULL PRIMARY KEY,
> > >   "flag" boolean NOT NULL,
> > >   "name" text NOT NULL,
> > >   "publisher" integer NOT NULL,
> > >   "issn_print" integer NOT NULL,
> > >   "issn_online" integer NOT NULL,
> > >   "link" integer NOT NULL,
> > >   "date_range" varchar(255) NOT NULL
> > >  );
> > >
> > >  and i needed to change column journal in tab_article to be set like
> > >  "journal_id" integer NOT NULL REFERENCES "article_journal" ("id")
> > >
> > >  and i am not able to find the right alter table command.
> > >  thank you very much
> > >
> > >
> >
> > You will want to add the NOT NULL as one statement.  Then, add the
> > foreign key constraint as a second statement.  There are examples of
> > how to do each here:
> >
> > http://www.postgresql.org/docs/8.3/static/sql-altertable.html
> >
> > Hope that helps.
> >
> > Sean
> >
> >
>  thank you for fast replay. I have read this one tutorial before i posted
> this here, but with no luck.
>
>  ALTER TABLE l_article ADD CONSTRAINT journal_id FOREIGN KEY (journal_id)
> REFERENCES l_journal (id);
>
>  this one stops my psql terminal and did not change anything. Am i missing
> the not null statement?

When you say it stops your psql terminal, you mean that the prompt
does not return immediately?  I assume that your tables already have
data in them.  If that is the case, then postgresql is probably
checking to make sure that your foreign key constraint is actually
satisfied.  If you have a good deal of data, then this may take some
time.

>  ALTER TABLE "l_article" ADD CONSTRAINT "journal_id" integer NOT NULL
> REFERENCES FOREIGN KEY ("journal_id") REFERENCES "l_journal" (id);
>
>  thank you
>  pavel
>
>
>
>  --
>  Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-novice
>

Re: alter table

From
Pavel SRB
Date:
Sean Davis wrote:
> On Mon, Apr 7, 2008 at 5:18 PM, Pavel SRB <srb@heckler-koch.cz> wrote:
>
>> Sean Davis wrote:
>>
>>
>>> On Mon, Apr 7, 2008 at 4:53 PM, Pavel SRB <srb@heckler-koch.cz> wrote:
>>>
>>>
>>>
>>>> hi all
>>>>
>>>>  please i have one question. I made table called tab_article with
>>>>
>>>>  CREATE TABLE "tab_article" (
>>>>   "id" serial NOT NULL PRIMARY KEY,
>>>>   "flag" boolean NOT NULL,
>>>>   "title" text NOT NULL,
>>>>   "journal" integer NOT NULL,
>>>>   "doi" varchar(255) NOT NULL,
>>>>   "year" integer NOT NULL,
>>>>   "volume" integer NOT NULL,
>>>>   "issue" integer NOT NULL,
>>>>   "pages_start" integer NOT NULL,
>>>>   "pages_end" integer NOT NULL,
>>>>   "printed" boolean NOT NULL,
>>>>   "public" boolean NOT NULL,
>>>>   "entry" boolean NOT NULL,
>>>>   "filename" integer NOT NULL,
>>>>   "language" integer NOT NULL,
>>>>   "link" integer NOT NULL,
>>>>   "info" varchar(255) NOT NULL
>>>>  );
>>>>
>>>>  and later another table called tab_journal
>>>>
>>>>  CREATE TABLE "tab_journal" (
>>>>   "id" serial NOT NULL PRIMARY KEY,
>>>>   "flag" boolean NOT NULL,
>>>>   "name" text NOT NULL,
>>>>   "publisher" integer NOT NULL,
>>>>   "issn_print" integer NOT NULL,
>>>>   "issn_online" integer NOT NULL,
>>>>   "link" integer NOT NULL,
>>>>   "date_range" varchar(255) NOT NULL
>>>>  );
>>>>
>>>>  and i needed to change column journal in tab_article to be set like
>>>>  "journal_id" integer NOT NULL REFERENCES "article_journal" ("id")
>>>>
>>>>  and i am not able to find the right alter table command.
>>>>  thank you very much
>>>>
>>>>
>>>>
>>> You will want to add the NOT NULL as one statement.  Then, add the
>>> foreign key constraint as a second statement.  There are examples of
>>> how to do each here:
>>>
>>> http://www.postgresql.org/docs/8.3/static/sql-altertable.html
>>>
>>> Hope that helps.
>>>
>>> Sean
>>>
>>>
>>>
>>  thank you for fast replay. I have read this one tutorial before i posted
>> this here, but with no luck.
>>
>>  ALTER TABLE l_article ADD CONSTRAINT journal_id FOREIGN KEY (journal_id)
>> REFERENCES l_journal (id);
>>
>>  this one stops my psql terminal and did not change anything. Am i missing
>> the not null statement?
>>
>
> When you say it stops your psql terminal, you mean that the prompt
> does not return immediately?  I assume that your tables already have
> data in them.  If that is the case, then postgresql is probably
> checking to make sure that your foreign key constraint is actually
> satisfied.  If you have a good deal of data, then this may take some
> time.
>
>
>>  ALTER TABLE "l_article" ADD CONSTRAINT "journal_id" integer NOT NULL
>> REFERENCES FOREIGN KEY ("journal_id") REFERENCES "l_journal" (id);
>>
>>  thank you
>>  pavel
>>
>>
>>
>>  --
>>  Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>>  To make changes to your subscription:
>>  http://www.postgresql.org/mailpref/pgsql-novice
>>
>>
>
>
i have only six rows in the l_article table. So i believe that fault is
on my code, when after 5 minutes it still does nothing(yes it does not
return the prompt).