Postgres Pro
Downloads
Home   >   mailing lists

Thread: Removing foreign key and adding sequence

Removing foreign key and adding sequence

From
"Chad Thompson"
Date:
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

Re: Removing foreign key and adding sequence

From
Josh Berkus
Date:
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

basic question

From
Michael Hanna
Date:
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

Re: basic question

From
Josh Berkus
Date:
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

Re: basic question

From
"Marie G. Tuite"
Date:
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@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.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@postgresql.org)
>

Re: basic question

From
Nabil Sayegh
Date:
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