Lists: | pgsql-novice |
---|
From: | "Chad Thompson" <chad(at)weblinkservices(dot)com> |
---|---|
To: | "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Removing foreign key and adding sequence |
Date: | 2003-06-26 17:07:47 |
Message-ID: | 000901c33c05$781aa120$32021aac@chad |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
I have a table of 12 M records. When this table was created a specific
foreign key constraint was very necessary to ensure the data was correct. I
have now built enough checks through software that this foreign key is
hindering performance more than it is useful.
So my idea is this.
alter table lists rename to lists_bak;
select * from lists_bak into lists;
I can add the constraints and foreign key's back in that ARE necessary, my
only worry (and mabey you can think of another worry) is that my sequence is
no longer attached to the id field of my table.
Is this the right approach, and if so, how do I add my sequence back in?
#select version();
version
----------------------------------------------------------------------------
-----------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
TIA
Chad
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | "Chad Thompson" <chad(at)weblinkservices(dot)com>, "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Removing foreign key and adding sequence |
Date: | 2003-06-26 17:12:18 |
Message-ID: | 200306261012.18546.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Chad,
> alter table lists rename to lists_bak;
>
> select * from lists_bak into lists;
>
> I can add the constraints and foreign key's back in that ARE necessary, my
> only worry (and mabey you can think of another worry) is that my sequence
> is no longer attached to the id field of my table.
>
> Is this the right approach, and if so, how do I add my sequence back in?
Simple: when you re-create Lists, just make sure that the id is "DEFAULT
NEXTVAL('sequence_name')". That's it. Sequences are easy.
However, I think it might be easier just to drop the FKs. What version are
you using?
--
Josh Berkus
Aglio Database Solutions
San Francisco
From: | Michael Hanna <zen(at)hwcn(dot)org> |
---|---|
To: | |
Cc: | "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | basic question |
Date: | 2003-06-26 20:53:42 |
Message-ID: | 457BF1A6-A818-11D7-96EE-00039308EB2C@hwcn.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
in psql 7.3.3, press enter after a semi-colon
michael=# insert into healthnotes (notes) values ('mytext....');
michael'#
changes the prompt accordingly and doesn't commit the change..what am I
doing wrong? I'm certain this code works, as I've done something
similar via dbVis
Michael
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Michael Hanna <zen(at)hwcn(dot)org> |
Cc: | "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: basic question |
Date: | 2003-06-26 20:58:49 |
Message-ID: | 200306261358.49398.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Michael,
> michael=# insert into healthnotes (notes) values ('mytext....');
> michael'#
>
> changes the prompt accordingly and doesn't commit the change..what am I
> doing wrong? I'm certain this code works, as I've done something
> similar via dbVis
You're failing to close a set of single quotes. Look for, for example, an
un-escaped apostrophe.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
From: | "Marie G(dot) Tuite" <marie(dot)tuite(at)edisonaffiliates(dot)com> |
---|---|
To: | "Michael Hanna" <zen(at)hwcn(dot)org> |
Cc: | "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: basic question |
Date: | 2003-06-26 21:06:14 |
Message-ID: | IGELKLINGDMODABPOOFEAEAGEHAA.marie.tuite@edisonaffiliates.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
You most likely have a ' in the mytext. You need to escape it using \'.
i.e.
insert into healthnotes (notes) values ('some test\'s');
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Michael Hanna
> Sent: Thursday, June 26, 2003 3:54 PM
> Cc: pgsql-novice
> Subject: [NOVICE] basic question
>
>
> in psql 7.3.3, press enter after a semi-colon
>
> michael=# insert into healthnotes (notes) values ('mytext....');
> michael'#
>
> changes the prompt accordingly and doesn't commit the change..what am I
> doing wrong? I'm certain this code works, as I've done something
> similar via dbVis
>
> Michael
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
From: | Nabil Sayegh <postgresql(at)e-trolley(dot)de> |
---|---|
To: | marie(dot)tuite(at)edisonaffiliates(dot)com |
Cc: | Michael Hanna <zen(at)hwcn(dot)org>, pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: basic question |
Date: | 2003-06-26 21:17:11 |
Message-ID: | 1056662230.627.2.camel@billy |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Am Don, 2003-06-26 um 23.06 schrieb Marie G. Tuite:
> You most likely have a ' in the mytext. You need to escape it using \'.
Alternatively, in SQL quotes may be quotet by double quoting :)
that means '' is a quoted '
In some cases it's useful :)
bye
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de