71018

Adding a auto incremental column into existing redshift table

Question:

I have a table in Redshift.

I want to add a column which should have incremental values. I dont want to drop the table and create a new one.

Please suggest the command to add a column having auto incremental values in redshift table.

Thanks !!!

Answer1:

It is not possible to add an IDENTITY column to an existing table.

It might be easiest to create a new table with the new IDENTITY column, and copy the data into it. Note that the values aren't guaranteed to increase monotonically - i.e. there may be gaps.

From <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html" rel="nofollow">http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html</a> :

<blockquote>

You cannot use an ALTER TABLE ADD COLUMN command to modify the following table and column attributes:

UNIQUE

PRIMARY KEY

REFERENCES (foreign key)

IDENTITY

</blockquote>

Answer2:

Use CREATE TABLE to add a new table with identity column and then use <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE_APPEND.html" rel="nofollow">ALTER TABLE APPEND</a> with FILLTARGET modifier, and then drop the original table and rename the new table it is extremely fast and simple.

<blockquote>

If the target table contains columns that don't exist in the source table, include FILLTARGET. The command fills the extra columns in the source table with either the default column value or IDENTITY value, if one was defined, or NULL.

</blockquote>

It moves the columns from one table to another, extremely fast, took me 4s for 1GB table in dc1.large node.

<blockquote>

Appends rows to a target table by moving data from an existing source table.<br /> ...<br /> ALTER TABLE APPEND is usually much faster than a similar CREATE TABLE AS or INSERT INTO operation because data is moved, not duplicated.

</blockquote> /* This is your table to be modified */ CREATE TABLE t_original (a varchar); INSERT INTO t_original VALUES ('v1'), ('v2'); /* Here are the steps to add IDENTITY column */ CREATE TABLE t_new (id BIGINT IDENTITY(0,1), a varchar); ALTER TABLE t_new APPEND FROM t_original FILLTARGET; DROP TABLE t_original; ALTER TABLE t_new RENAME TO t_original; /* Show the result */ SELECT * FROM t_original; id | a ----+---- 1 | v2 0 | v1 (2 rows)

Recommend

  • pandas failing with variable columns
  • Optimization of an all-paths algorithm
  • Gathering strings with MPI_Gather openmpi c
  • WPF Datagrid - How to validate multiple rows and mark all invalid ones?
  • inserting duplicate records with SQL
  • Alter Table doesn't work under MS Access 64 bit. Why?
  • AWS Cloudwatch not triggering on API calls
  • How to specify a multi-column UNIQUE constraint in code-first Entity Framework fluent API
  • Is creating a new version of an object in AWS S3 eventually consistent or read-after-write consisten
  • AWS Cognito- get user information with ID
  • Dynamically create AWS IoT topic
  • HTML checkbox form and HTTP URL
  • Msvcr71.dll Msvcp71.dll missing
  • Query pkg-config variable through autotools
  • SQL - Select lowest values with group by and order by?
  • Jenkins Grails plugin does not list lastest versions of Grails
  • How to get links to open in the native browser in iOS Meteor apps?
  • gspread or such: help me get cell coordinates (not value)
  • Zurb Foundation _global.scss meta styles for js?
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • The plugin 'org.apache.maven.plugins:maven-jboss-as-plugin' does not exist or no valid ver
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • Delete MySQLi record without showing the id in the URL
  • QuartzCore.framework for Mono Develop
  • Python - Map / Reduce - How do I read JSON specific field in using DISCO count words example
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • RestKit - RKRequestDelegate does not exist
  • Unanticipated behavior
  • bootstrap to use multiple ng-app
  • Comma separated Values
  • How to get icons for entities from eclipse?
  • Proper way to use connect-multiparty with express.js?
  • Load html files in TinyMce
  • Angular 2 constructor injection vs direct access
  • Trying to get generic when generic is not available
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • Turn off referential integrity in Derby? is it possible?
  • JaxB to read class hierarchy
  • UserPrincipal.Current returns apppool on IIS