Skip site navigation (1) Skip section navigation (2)

Re: alter table

From: Pavel SRB <srb(at)heckler-koch(dot)cz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: alter table
Date: 2008-04-08 04:32:37
Message-ID: 47FAF565.9020308@heckler-koch.cz (view raw or flat)
Thread:
Lists: pgsql-novice
Sean Davis wrote:
> On Mon, Apr 7, 2008 at 5:18 PM, Pavel SRB <srb(at)heckler-koch(dot)cz> wrote:
>   
>> Sean Davis wrote:
>>
>>     
>>> On Mon, Apr 7, 2008 at 4:53 PM, Pavel SRB <srb(at)heckler-koch(dot)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(at)postgresql(dot)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).



In response to

pgsql-novice by date

Next:From: A BDate: 2008-04-08 09:54:09
Subject: boolean as 0 and 1
Previous:From: Justin BaileyDate: 2008-04-08 00:27:15
Subject: problems with dollar quoting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group