Re: Changing the Primary Key Column

This, too, is new to me. The first table column is a contrived key that is
no longer needed as a unique row identifier. The second column should
replace the first as the primary key. The ALTER TABLE command can add a
primary key to a column, but I don't see how to remove that constraint from
column1, add it to column2, so I can drop column1.

Pointers, please.

Rich

--
Richard B. Shepard, Ph.D. | The Plain English Science Consultant
Applied Ecosystem Services, Inc. | Helping Ensure Our Clients' Futures
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On 02/23/2011 03:34 PM, Rich Shepard wrote:
> This, too, is new to me. The first table column is a contrived key that is
> no longer needed as a unique row identifier. The second column should
> replace the first as the primary key. The ALTER TABLE command can add a
> primary key to a column, but I don't see how to remove that constraint from
> column1, add it to column2, so I can drop column1.
>

Maybe someone else has a better answer but in my experience you have to
drop the table to change the PK. You can add/drop other indexes or FK.
How about leaving the synthetic PK and adding an extra index for your
other key. Doing that also gets you away from any duplicate primary key
errors. Although Josh Berkus will probably slap me silly for ever
suggesting a synthetic key.

On Wed, 23 Feb 2011, Michael Ewan wrote:

> Maybe someone else has a better answer but in my experience you have to
> drop the table to change the PK. You can add/drop other indexes or FK.
> How about leaving the synthetic PK and adding an extra index for your
> other key. Doing that also gets you away from any duplicate primary key
> errors. Although Josh Berkus will probably slap me silly for ever
> suggesting a synthetic key.

Michael,

Joe Celko doesn't like synthetic keys, either.

I suppose what I need to do is dump the table to a .sql file and make
changes there. If I drop the table I lose all the hours of work getting data
in there. :-(

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | The Plain English Science Consultant
Applied Ecosystem Services, Inc. | Helping Ensure Our Clients' Futures
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On 02/23/2011 04:12 PM, Rich Shepard wrote:
> On Wed, 23 Feb 2011, Michael Ewan wrote:
>
>> Maybe someone else has a better answer but in my experience you have to
>> drop the table to change the PK. You can add/drop other indexes or FK.
>> How about leaving the synthetic PK and adding an extra index for your
>> other key. Doing that also gets you away from any duplicate primary key
>> errors. Although Josh Berkus will probably slap me silly for ever
>> suggesting a synthetic key.
>
> Michael,
>
> Joe Celko doesn't like synthetic keys, either.
>
> I suppose what I need to do is dump the table to a .sql file and make
> changes there. If I drop the table I lose all the hours of work getting data
> in there. :-(

Yes that would be a good idea, and was implied in any suggestion of
dropping tables. ;-)
Just be advised, pg_dump dumps to a copy from stdin: format with tab
delimiters.

On Wed, February 23, 2011 16:22, Rich Shepard wrote:
> On Wed, 23 Feb 2011, Michael Ewan wrote:
>
>> Yes that would be a good idea, and was implied in any suggestion of
>> dropping
>> tables. ;-)
>
>> Just be advised, pg_dump dumps to a copy from stdin: format with tab
>> delimiters.
>
> I read the pg_dump man page but did not learn how to specify an
> alternative format that would allow me to re-arrange column order, drop
> the
> unwanted column, and so on. Next step is looking at the 9.0.x docs for
> pg_dump.
>
> Rich
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

Why not just dump the data (dump just the table with pg_dump), drop the
table, build the new table and re-load the data (using psql)?
--
Timothy J. Bruce

visit my Website at: http://www.tbruce.com
Registered Linux User #325725

On Wed, 23 Feb 2011, Michael Ewan wrote:

> Yes that would be a good idea, and was implied in any suggestion of dropping
> tables. ;-)

> Just be advised, pg_dump dumps to a copy from stdin: format with tab
> delimiters.

I read the pg_dump man page but did not learn how to specify an
alternative format that would allow me to re-arrange column order, drop the
unwanted column, and so on. Next step is looking at the 9.0.x docs for
pg_dump.

Rich

Why make it so hard?

begin;
SELECT * INTO new_table FROM old_table;
DROP old_table;
CREATE old_table ( new definition );
INSERT INTO old_table SELECT col1,col2,col3,etc. FROM new_table;
DROP new_table;
commit;

On Feb 23, 2011, at 16:22, Rich Shepard wrote:

> On Wed, 23 Feb 2011, Michael Ewan wrote:
>
>> Yes that would be a good idea, and was implied in any suggestion of dropping tables. ;-)
>
>> Just be advised, pg_dump dumps to a copy from stdin: format with tab delimiters.
>
> I read the pg_dump man page but did not learn how to specify an
> alternative format that would allow me to re-arrange column order, drop the
> unwanted column, and so on. Next step is looking at the 9.0.x docs for
> pg_dump.
>
> Rich
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug

On Wed, 2011-02-23 at 16:06 -0800, Michael Ewan wrote:
>
> On 02/23/2011 03:34 PM, Rich Shepard wrote:
> > This, too, is new to me. The first table column is a contrived key that is
> > no longer needed as a unique row identifier. The second column should
> > replace the first as the primary key. The ALTER TABLE command can add a
> > primary key to a column, but I don't see how to remove that constraint from
> > column1, add it to column2, so I can drop column1.
> >
>
> Maybe someone else has a better answer but in my experience you have to
> drop the table to change the PK.

Uhh, no. You drop the constraint and re-add it for the second column.

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Wed, 23 Feb 2011, Tim Bruce wrote:

> Why not just dump the data (dump just the table with pg_dump), drop the
> table, build the new table and re-load the data (using psql)?

Tim,

I've not done this before and I'd still need to manually drop the first
column's worth of data.

Since I've been using the copy command, I will think overnight of copying
the table to a .csv file, editing that in emacs or joe (to remove the first
column), then drop the original table and copy it back in.

Then I'll need to figure out postGIS so I can map locations and build
surface maps.

Thanks,

Rich

On Wed, 23 Feb 2011, Darrell Fuhriman wrote:

> Why make it so hard?
>
> begin;
> SELECT * INTO new_table FROM old_table;
> DROP old_table;
> CREATE old_table ( new definition );
> INSERT INTO old_table SELECT col1,col2,col3,etc. FROM new_table;
> DROP new_table;
> commit;

Darrell,

Because, while I've winnowed the number of columns from 73 to 63, I don't
want to type all 63 column names interactively. :-)

But, I'm learning from all of you and really appreciate it!

Rich

On Wed, 23 Feb 2011, Joshua D. Drake wrote:

> Uhh, no. You drop the constraint and re-add it for the second column.

Josh,

And if I do this, I can follow that with dropping the first column which
no longer has any constraints? This would be the simplest and quickest
solution.

Rich

--
Richard B. Shepard, Ph.D. | The Plain English Science Consultant
Applied Ecosystem Services, Inc. | Helping Ensure Our Clients' Futures
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Wed, 2011-02-23 at 16:43 -0800, Rich Shepard wrote:
> On Wed, 23 Feb 2011, Joshua D. Drake wrote:
>
> > Uhh, no. You drop the constraint and re-add it for the second column.
>
> Josh,
>
> And if I do this, I can follow that with dropping the first column which
> no longer has any constraints? This would be the simplest and quickest
> solution.

alter table drop constraint
alter table drop column
alter table add primary key

done

>
> Rich
>
> --
> Richard B. Shepard, Ph.D. | The Plain English Science Consultant
> Applied Ecosystem Services, Inc. | Helping Ensure Our Clients' Futures
> <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Wed, 23 Feb 2011, Joshua D. Drake wrote:

> alter table drop constraint
> alter table drop column
> alter table add primary key
>
> done

Thanks, Josh.

When I do this and follow with '\d water_well' I still see the first
column (sequence_no) with the not null constraint. Wonder why that didn't
drop when I specified the column name in the drop column command.

Rich

On 02/23/2011 05:09 PM, Rich Shepard wrote:
> On Wed, 23 Feb 2011, Joshua D. Drake wrote:
>
>> alter table drop constraint
>> alter table drop column
>> alter table add primary key
>>
>> done
>
> Thanks, Josh.
>
> When I do this and follow with '\d water_well' I still see the first
> column (sequence_no) with the not null constraint. Wonder why that didn't
> drop when I specified the column name in the drop column command.

Did you COMMIT; ?

>>>>> "Rich" == Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

Rich> Joe Celko doesn't like synthetic keys, either.

What does Joe Celko think about the "washington's axe" problem?

For those not familiar:

The museum guide tells the visitor that "yes, this is the original axe
that Washington used to chop down the cherry tree". The visitor
replies "Really?", to which the guide says "well, the handle has been
replaced three times, and the blade twice."

So, is that the same axe or not? If it is, then you need a synthetic
key. If not, then Celko is right.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.posterous.com/ for Smalltalk discussion

On Wed, 2011-02-23 at 17:09 -0800, Rich Shepard wrote:
> On Wed, 23 Feb 2011, Joshua D. Drake wrote:
>
> > alter table drop constraint
> > alter table drop column
> > alter table add primary key
> >
> > done
>
> Thanks, Josh.
>
> When I do this and follow with '\d water_well' I still see the first
> column (sequence_no) with the not null constraint. Wonder why that didn't
> drop when I specified the column name in the drop column command.

That doesn't make sense. Paste your output please.

>
> Rich
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

>>>>> "Rich" == Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

Rich> Because, while I've winnowed the number of columns from 73 to 63, I don't
Rich> want to type all 63 column names interactively. :-)

63 columns is still about 30 too many.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.posterous.com/ for Smalltalk discussion

On Wed, 2011-02-23 at 17:11 -0800, Michael Ewan wrote:
>
> On 02/23/2011 05:09 PM, Rich Shepard wrote:
> > On Wed, 23 Feb 2011, Joshua D. Drake wrote:
> >
> >> alter table drop constraint
> >> alter table drop column
> >> alter table add primary key
> >>
> >> done
> >
> > Thanks, Josh.
> >
> > When I do this and follow with '\d water_well' I still see the first
> > column (sequence_no) with the not null constraint. Wonder why that didn't
> > drop when I specified the column name in the drop column command.
>
> Did you COMMIT; ?

He would still see the change if it actually executed, even in a
transaction because of the isolation. Now if he disconnected and
reconnected or is looking at it in another terminal/session, it wouldn't
see it.

JD

>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Wed, 23 Feb 2011, Michael Ewan wrote:

> Did you COMMIT; ?

Nope.

Will re-do.

Thanks,

Rich

On Wed, 23 Feb 2011, Joshua D. Drake wrote:

> He would still see the change if it actually executed, even in a
> transaction because of the isolation. Now if he disconnected and
> reconnected or is looking at it in another terminal/session, it wouldn't
> see it.

Tomorrow I'll finish.

Thanks,

Rich

On Wed, 23 Feb 2011, Randal L. Schwartz wrote:

> 63 columns is still about 30 too many.

It came from an Access flat file. Eventually I'll separate it into 2 or 3
separate, but related, tables.

Rich

On Wed, 23 Feb 2011, Joshua D. Drake wrote:

>>> alter table drop constraint
>>> alter table drop column
>>> alter table add primary key

> That doesn't make sense. Paste your output please.

nevada-# alter table water_well drop constraint 'not null'
nevada-# alter table water_well drop column 'sequence_no'
nevada-# alter table water_well add primary key 'well_log'
nevada-# commit;
ERROR: syntax error at or near "alter"
LINE 2: alter table water_well drop constraint 'not null'
^
nevada=#

Must be user error; I don't see the variables quoted on the alter table
doc page, but that makes no difference:

nevada=# alter table water_well drop constraint not null
nevada-# alter table water_well drop column sequence_no
nevada-# alter table water_well add primary key well_log
nevada-# commit;
ERROR: syntax error at or near "not"
LINE 1: alter table water_well drop constraint not null
^
nevada=#

Schema's unchanged:

Table "public.water_well"
Column | Type | Modifiers
--------------------+-----------------------+-----------
sequence_no | text | not null
well_log | text |

Elucidation needed.

Thanks,

Rich

Try

# alter table water_well alter column sequence_no drop not null;
# commit;

http://www.java2s.com/Code/PostgreSQL/Constraints/ALTERTABLEemployeeALTERCOLUMNidDROPNOTNULL.htm

Brian Kurle
President
Traveling bits, Inc.
Cell: 503 961-2489
Fax: 503 640-3814

On Feb 24, 2011, at 6:20 AM, Rich Shepard wrote:

> On Wed, 23 Feb 2011, Joshua D. Drake wrote:
>
>>>> alter table drop constraint
>>>> alter table drop column
>>>> alter table add primary key
>
>> That doesn't make sense. Paste your output please.
>
> nevada-# alter table water_well drop constraint 'not null'
> nevada-# alter table water_well drop column 'sequence_no'
> nevada-# alter table water_well add primary key 'well_log'
> nevada-# commit;
> ERROR: syntax error at or near "alter"
> LINE 2: alter table water_well drop constraint 'not null'
> ^
> nevada=#
>
> Must be user error; I don't see the variables quoted on the alter table
> doc page, but that makes no difference:
>
> nevada=# alter table water_well drop constraint not null
> nevada-# alter table water_well drop column sequence_no
> nevada-# alter table water_well add primary key well_log
> nevada-# commit;
> ERROR: syntax error at or near "not"
> LINE 1: alter table water_well drop constraint not null
> ^
> nevada=#
>
> Schema's unchanged:
>
> Table "public.water_well"
> Column | Type | Modifiers --------------------+-----------------------+-----------
> sequence_no | text | not null
> well_log | text |
>
> Elucidation needed.
>
> Thanks,
>
> Rich
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

On Feb 24, 2011, at 6:20 AM, Rich Shepard wrote:

> On Wed, 23 Feb 2011, Joshua D. Drake wrote:
>
>>>> alter table drop constraint
>>>> alter table drop column
>>>> alter table add primary key
>
>> That doesn't make sense. Paste your output please.
>
> nevada-# alter table water_well drop constraint 'not null'
> nevada-# alter table water_well drop column 'sequence_no'
> nevada-# alter table water_well add primary key 'well_log'
> nevada-# commit;
> ERROR: syntax error at or near "alter"
> LINE 2: alter table water_well drop constraint 'not null'
> ^
> nevada=#

'not null' is a column constraint, not the table constraint. Look for the table constraint at the bottom of the table description. It will likely be something like table_pkey. Here is a sample from a test database I have:

Table "public.person"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
ssn | integer |
name | character varying(32) |
phone | character(12) |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)

test=# alter table person drop constraint "person_pkey";
ALTER TABLE
test=# \d person
Table "public.person"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
ssn | integer |
name | character varying(32) |
phone | character(12) |

Brent.

On Thu, 24 Feb 2011, Brian Kurle wrote:

> # alter table water_well alter column sequence_no drop not null;

Brian,

nevada=# alter table water_well alter column sequence_no drop not null;
ERROR: column "sequence_no" is in a primary key

> http://www.java2s.com/Code/PostgreSQL/Constraints/ALTERTABLEemployeeALTERCOLUMNidDROPNOTNULL.htm

The example above does not have the id column as primary key or any other
constraint.

Rich

Rich,

As was mentioned by Brent, you may have to look to see what the named constraint is and use it. However, the issue originally was that you weren't using the correct command as you were altering the table, but not altering the column.

Although the PostgreSQL documentation varies between releases, the following appears to be more complete in it's explanations:

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

Brian Kurle
President
Traveling bits, Inc.
Cell: 503 961-2489
Fax: 503 640-3814

On Feb 24, 2011, at 7:51 AM, Rich Shepard wrote:

> On Thu, 24 Feb 2011, Brian Kurle wrote:
>
>> # alter table water_well alter column sequence_no drop not null;
>
> Brian,
>
> nevada=# alter table water_well alter column sequence_no drop not null;
> ERROR: column "sequence_no" is in a primary key
>
>> http://www.java2s.com/Code/PostgreSQL/Constraints/ALTERTABLEemployeeALTERCOLUMNidDROPNOTNULL.htm
>
> The example above does not have the id column as primary key or any other
> constraint.
>
> Rich
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

On Thu, 24 Feb 2011, Brent Dombrowski wrote:

> 'not null' is a column constraint, not the table constraint. Look for the
> table constraint at the bottom of the table description. It will likely be
> something like table_pkey. Here is a sample from a test database I have:

Brent,

Sonofagun! I totally missed this by being too close to the problem. Yes,
the table key was water_well_pkey.

Now that I dropped that key and the sequence_no column, what is the
correct syntax to add the primary key constraint to the now first column,
well_log? Trying to follow the 9.1 alter table page:

nevada-# alter table water_well add well_log_pkey
nevada-# commit;
ERROR: syntax error at or near "table"
LINE 2: alter table water_well add well_log_pkey
^
Thanks,

Rich

As I stated, the documentation varies between releases, but seldom do features go away (it happens, but not very often). Thus, looking at the older notes can be very insightful and still valid.

As for adding the new primary key, it will be an add instead of a drop operation.

Perform a pg_dump on a simple table (even the one you have, but without dumping the table), and you'll even see the commands it would use to create the primary key to reconstruct it.

For one of my tables, after performing a pg_dump, I see the following:

ALTER TABLE ONLY action
ADD CONSTRAINT action_pkey PRIMARY KEY (transactionid);

You would do something similar.

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

Brian Kurle
President
Traveling bits, Inc.
Cell: 503 961-2489
Fax: 503 640-3814

On Feb 24, 2011, at 8:03 AM, Rich Shepard wrote:

> On Thu, 24 Feb 2011, Brian Kurle wrote:
>
>> As was mentioned by Brent, you may have to look to see what the named
>> constraint is and use it.
>
> Brian,
>
> Got that, thanks.
>
>> Although the PostgreSQL documentation varies between releases, the
>> following appears to be more complete in it's explanations:
>>
>> http://www.postgresql.org/docs/8.0/static/sql-altertable.html
>
> I used the 9.1 page since I'm running 9.0.1.
>
> What remains is assigning a new primary key on the well_log column.
>
> Thanks,
>
> Rich
>

On Thu, 24 Feb 2011, Brian Kurle wrote:

> For one of my tables, after performing a pg_dump, I see the following:
>
> ALTER TABLE ONLY action
> ADD CONSTRAINT action_pkey PRIMARY KEY (transactionid);
>
> You would do something similar.

Brian,

Thanks very much for clarifying the syntax. While I read the doc page I
did not have all necessary parts in the one statement. Now I understand how
to add a new primary key.

Rich

On 02/24/2011 08:58 AM, Rich Shepard wrote:
> On Thu, 24 Feb 2011, Brian Kurle wrote:
>
>> For one of my tables, after performing a pg_dump, I see the following:
>>
>> ALTER TABLE ONLY action
>> ADD CONSTRAINT action_pkey PRIMARY KEY (transactionid);
>>
>> You would do something similar.
>
> Brian,
>
> Thanks very much for clarifying the syntax. While I read the doc page I
> did not have all necessary parts in the one statement. Now I understand how
> to add a new primary key.
>

If you are willing to use a GUI interface, pgadmin3 is very useful in
making these kind of changes and it shows you the syntax of the SQL
command it is using.

On Thu, 2011-02-24 at 06:20 -0800, Rich Shepard wrote:
> On Wed, 23 Feb 2011, Joshua D. Drake wrote:
>
> >>> alter table drop constraint
> >>> alter table drop column
> >>> alter table add primary key
>
> > That doesn't make sense. Paste your output please.
>
> nevada-# alter table water_well drop constraint 'not null'
> nevada-# alter table water_well drop column 'sequence_no'
> nevada-# alter table water_well add primary key 'well_log'
> nevada-# commit;
> ERROR: syntax error at or near "alter"
> LINE 2: alter table water_well drop constraint 'not null'
> ^

You forgot the semicolon. You can notice that from the prompt (which is
"-#" rather than "=#".

I think that is causing some of this confusion, although the syntax is
wrong as well -- don't use single-quotes for column/constraint names.

Regards,
Jeff Davis

On Thu, 24 Feb 2011, Michael Ewan wrote:

> If you are willing to use a GUI interface, pgadmin3 is very useful in
> making these kind of changes and it shows you the syntax of the SQL
> command it is using.

Michael,

I do GUIs when they increase efficiency (e.g., LyX rather than LaTeX in
emacs). I need to split some columns into another table so I'll look at
pgadmin3.

Thanks,

Rich

On 02/24/2011 10:00 AM, Rich Shepard wrote:
> On Thu, 24 Feb 2011, Michael Ewan wrote:
>
>> If you are willing to use a GUI interface, pgadmin3 is very useful in
>> making these kind of changes and it shows you the syntax of the SQL
>> command it is using.
>
> Michael,
>
> I do GUIs when they increase efficiency (e.g., LyX rather than LaTeX in
> emacs). I need to split some columns into another table so I'll look at
> pgadmin3.

I agree 100%, when doing data extract queries, I just do psql command
line, or a script file. When doing schema changes, the syntax can be a
little twitchy, so I use pgadmin3. Now if Toad supported PostgreSQL, I
might use that for queries also, it does all kinds of cool auto-complete
for joins.

On Thu, 24 Feb 2011, Michael Ewan wrote:

> If you are willing to use a GUI interface, pgadmin3 is very useful in
> making these kind of changes and it shows you the syntax of the SQL
> command it is using.

What are the differences between pgadmin and pgadmin3? I actually have
the latter installed here: pgadmin3-1.10.2.

Rich