You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by "Riesland, Zack" <Za...@sensus.com> on 2015/07/14 19:01:52 UTC
How to adjust primary key on existing table
This is probably a lame question, but can anyone point me in the right direction for CHANGING and EXISTING primary key on a table?
I want to add a column.
Is it possible to do that without dropping the table?
Thanks!
RE: How to adjust primary key on existing table
Posted by "Riesland, Zack" <Za...@sensus.com>.
Thank you!
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Tuesday, July 14, 2015 1:34 PM
To: user
Subject: Re: How to adjust primary key on existing table
Yes. UPSERT SELECT. Have you seen this yet? https://phoenix.apache.org/language/index.html
On Tue, Jul 14, 2015 at 10:21 AM, Riesland, Zack <Za...@sensus.com>> wrote:
Thanks James,
That’s what I thought.
If I were to make a NEW table with the same columns, is there a simple way to copy the data from the old table to the new one?
From: James Taylor [mailto:jamestaylor@apache.org<ma...@apache.org>]
Sent: Tuesday, July 14, 2015 1:17 PM
To: user
Subject: Re: How to adjust primary key on existing table
Ah, we don't support that currently. You can drop the existing column first (but you lose your data, though you could set a CURRENT_SCN property on your connection to prevent this data loss). Then you could run the command I mentioned.
On Tue, Jul 14, 2015 at 10:14 AM, Riesland, Zack <Za...@sensus.com>> wrote:
Thanks James,
To clarify: the column already exists on the table, but I want to add it to the primary key.
Is that what your example accomplishes?
From: James Taylor [mailto:jamestaylor@apache.org<ma...@apache.org>]
Sent: Tuesday, July 14, 2015 1:11 PM
To: user
Subject: Re: How to adjust primary key on existing table
ALTER TABLE t ADD my_new_col VARCHAR PRIMARY KEY
The new column must be nullable and the last existing PK column cannot be nullable and fixed width (or varbinary or array).
On Tue, Jul 14, 2015 at 10:01 AM, Riesland, Zack <Za...@sensus.com>> wrote:
This is probably a lame question, but can anyone point me in the right direction for CHANGING and EXISTING primary key on a table?
I want to add a column.
Is it possible to do that without dropping the table?
Thanks!
Re: How to adjust primary key on existing table
Posted by James Taylor <ja...@apache.org>.
Yes. UPSERT SELECT. Have you seen this yet?
https://phoenix.apache.org/language/index.html
On Tue, Jul 14, 2015 at 10:21 AM, Riesland, Zack <Za...@sensus.com>
wrote:
> Thanks James,
>
>
>
> That’s what I thought.
>
>
>
> If I were to make a NEW table with the same columns, is there a simple way
> to copy the data from the old table to the new one?
>
>
>
> *From:* James Taylor [mailto:jamestaylor@apache.org]
> *Sent:* Tuesday, July 14, 2015 1:17 PM
> *To:* user
> *Subject:* Re: How to adjust primary key on existing table
>
>
>
> Ah, we don't support that currently. You can drop the existing column
> first (but you lose your data, though you could set a CURRENT_SCN property
> on your connection to prevent this data loss). Then you could run the
> command I mentioned.
>
>
>
> On Tue, Jul 14, 2015 at 10:14 AM, Riesland, Zack <Za...@sensus.com>
> wrote:
>
> Thanks James,
>
>
>
> To clarify: the column already exists on the table, but I want to add it
> to the primary key.
>
>
>
> Is that what your example accomplishes?
>
>
>
> *From:* James Taylor [mailto:jamestaylor@apache.org]
> *Sent:* Tuesday, July 14, 2015 1:11 PM
> *To:* user
> *Subject:* Re: How to adjust primary key on existing table
>
>
>
> ALTER TABLE t ADD my_new_col VARCHAR PRIMARY KEY
>
>
>
> The new column must be nullable and the last existing PK column cannot be
> nullable and fixed width (or varbinary or array).
>
>
>
> On Tue, Jul 14, 2015 at 10:01 AM, Riesland, Zack <Za...@sensus.com>
> wrote:
>
> This is probably a lame question, but can anyone point me in the right
> direction for CHANGING and EXISTING primary key on a table?
>
>
>
> I want to add a column.
>
>
>
> Is it possible to do that without dropping the table?
>
>
>
> Thanks!
>
>
>
>
>
>
>
>
>
RE: How to adjust primary key on existing table
Posted by "Riesland, Zack" <Za...@sensus.com>.
Thanks James,
That’s what I thought.
If I were to make a NEW table with the same columns, is there a simple way to copy the data from the old table to the new one?
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Tuesday, July 14, 2015 1:17 PM
To: user
Subject: Re: How to adjust primary key on existing table
Ah, we don't support that currently. You can drop the existing column first (but you lose your data, though you could set a CURRENT_SCN property on your connection to prevent this data loss). Then you could run the command I mentioned.
On Tue, Jul 14, 2015 at 10:14 AM, Riesland, Zack <Za...@sensus.com>> wrote:
Thanks James,
To clarify: the column already exists on the table, but I want to add it to the primary key.
Is that what your example accomplishes?
From: James Taylor [mailto:jamestaylor@apache.org<ma...@apache.org>]
Sent: Tuesday, July 14, 2015 1:11 PM
To: user
Subject: Re: How to adjust primary key on existing table
ALTER TABLE t ADD my_new_col VARCHAR PRIMARY KEY
The new column must be nullable and the last existing PK column cannot be nullable and fixed width (or varbinary or array).
On Tue, Jul 14, 2015 at 10:01 AM, Riesland, Zack <Za...@sensus.com>> wrote:
This is probably a lame question, but can anyone point me in the right direction for CHANGING and EXISTING primary key on a table?
I want to add a column.
Is it possible to do that without dropping the table?
Thanks!
Re: How to adjust primary key on existing table
Posted by James Taylor <ja...@apache.org>.
Ah, we don't support that currently. You can drop the existing column first
(but you lose your data, though you could set a CURRENT_SCN property on
your connection to prevent this data loss). Then you could run the command
I mentioned.
On Tue, Jul 14, 2015 at 10:14 AM, Riesland, Zack <Za...@sensus.com>
wrote:
> Thanks James,
>
>
>
> To clarify: the column already exists on the table, but I want to add it
> to the primary key.
>
>
>
> Is that what your example accomplishes?
>
>
>
> *From:* James Taylor [mailto:jamestaylor@apache.org]
> *Sent:* Tuesday, July 14, 2015 1:11 PM
> *To:* user
> *Subject:* Re: How to adjust primary key on existing table
>
>
>
> ALTER TABLE t ADD my_new_col VARCHAR PRIMARY KEY
>
>
>
> The new column must be nullable and the last existing PK column cannot be
> nullable and fixed width (or varbinary or array).
>
>
>
> On Tue, Jul 14, 2015 at 10:01 AM, Riesland, Zack <Za...@sensus.com>
> wrote:
>
> This is probably a lame question, but can anyone point me in the right
> direction for CHANGING and EXISTING primary key on a table?
>
>
>
> I want to add a column.
>
>
>
> Is it possible to do that without dropping the table?
>
>
>
> Thanks!
>
>
>
>
>
>
>
RE: How to adjust primary key on existing table
Posted by "Riesland, Zack" <Za...@sensus.com>.
Thanks James,
To clarify: the column already exists on the table, but I want to add it to the primary key.
Is that what your example accomplishes?
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Tuesday, July 14, 2015 1:11 PM
To: user
Subject: Re: How to adjust primary key on existing table
ALTER TABLE t ADD my_new_col VARCHAR PRIMARY KEY
The new column must be nullable and the last existing PK column cannot be nullable and fixed width (or varbinary or array).
On Tue, Jul 14, 2015 at 10:01 AM, Riesland, Zack <Za...@sensus.com>> wrote:
This is probably a lame question, but can anyone point me in the right direction for CHANGING and EXISTING primary key on a table?
I want to add a column.
Is it possible to do that without dropping the table?
Thanks!
Re: How to adjust primary key on existing table
Posted by James Taylor <ja...@apache.org>.
ALTER TABLE t ADD my_new_col VARCHAR PRIMARY KEY
The new column must be nullable and the last existing PK column cannot be
nullable and fixed width (or varbinary or array).
On Tue, Jul 14, 2015 at 10:01 AM, Riesland, Zack <Za...@sensus.com>
wrote:
> This is probably a lame question, but can anyone point me in the right
> direction for CHANGING and EXISTING primary key on a table?
>
>
>
> I want to add a column.
>
>
>
> Is it possible to do that without dropping the table?
>
>
>
> Thanks!
>
>
>
>
>