Thread

1. Removing foreign key and adding sequence

   Chad Thompson <chad@weblinkservices.com> — 2003-06-26T17:07:47Z

   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
   
   
   

2. Re: Removing foreign key and adding sequence

   Josh Berkus <josh@agliodbs.com> — 2003-06-26T17:12:18Z

   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
   
   

3. basic question

   Michael Hanna <zen@hwcn.org> — 2003-06-26T20:53:42Z

   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
   
   
   

4. Re: basic question

   Josh Berkus <josh@agliodbs.com> — 2003-06-26T20:58:49Z

   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
   
   
   

5. Re: basic question

   Marie G. Tuite <marie.tuite@edisonaffiliates.com> — 2003-06-26T21:06:14Z

   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)
   > 
   
   
   

6. Re: basic question

   Nabil Sayegh <postgresql@e-trolley.de> — 2003-06-26T21:17:11Z

   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