CODEWITHSUNDEEP
arrayspostgresql
user2793872
user2793872

Reputation: 319

converting the varchar dataype to text[] array

I have table A(colmn1 varchar(10),column2 varchar2(20));

but I need to convert the datatype varchar to text[] array

table A(column1 text[],column2 text[])

please tell me how to alter this table columns

Upvotes: 2

Views: 3754

Answers (4)

Pascal_dher
Pascal_dher

Reputation: 458

This works and it also makes sure that you dont create values that are arrays with a null element in.

ALTER TABLE myTable ALTER COLUMN myColumn TYPE varchar[] USING CASE WHEN myColumn IS null THEN null ELSE array[myColumn] END;

Upvotes: 1

Ghost Answer
Ghost Answer

Reputation: 1498

To convert a column to a different data type, use a command like this:

ALTER TABLE TableName ALTER COLUMN ColumnName TYPE text[] Using array[ColumnName];;

This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast. If a more complex conversion is needed, you can add a USING clause that specifies how to compute the new values from the old.

PostgreSQL will attempt to convert the column's default value (if any) to the new type, as well as any constraints that involve the column. But these conversions may fail, or may produce surprising results. It's often best to drop any constraints on the column before altering its type, and then add back suitably modified constraints afterwards.

Upvotes: 1

Curry
Curry

Reputation: 895

do you really want text[]??? 'text' can save string. you don't need text[].

postgresql doc is like below

In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

so you just type below

ALTER TABLE TableName ALTER COLUMN ColumnName TYPE text;

Upvotes: 2

mu is too short
mu is too short

Reputation: 434595

There is no implicit cast that will convert your varchar to a text[] so you'll need to specify how the conversion should be done with a USING clause:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

Something simple like this should do the trick:

alter table A alter column column1 type text[] using array[column1];

Similarly for the other column.

Upvotes: 4

Related Questions