how to alter column in postgres
by redssr from LinuxQuestions.org on (#6HA93)
Dear experts,
i want to convert an existing column (id) int primary key to serial in postgresql i tried by creating a sequence and mapping the id column of table to the newly created sequence but its giving an error Kindly find the details below
Code:ALTER TABLE ybl_payoutapi_response ALTER id SET DEFAULT nextval('test_id_seq');
ERROR: column "id" is of type integer but default expression is of type character varying
HINT: You will need to rewrite or cast the expression.table description
Code:[ \d+ ybl_payoutapi_response;
Table "public.ybl_payoutapi_response"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------------+-------------------------+-----------+----------+-------------------------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
transaction_type | character varying(45) | | | NULL::character varying | extended | | |
ybl_account_no | character varying(100) | | | NULL::character varying | extended | | |
acc_name | character varying(45) | | | NULL::character varying | extended | | |
beneficiary_code | character varying(45) | | | NULL::character varying | extended | | |
ifsc_code | character varying(45) | | | NULL::character varying | extended | | |
beneficiary_acc_no | character varying(100) | | | NULL::character varying | extended | | |
beneficiary_acc_name | character varying(100) | | | NULL::character varying | extended | | |
unique_code | character varying(45) | | | NULL::character varying | extended | | |
payout_date | character varying(45) | | | NULL::character varying | extended | | |
amount | character varying(45) | | | NULL::character varying | extended | | |
payout_ref_no | character varying(45) | | | NULL::character varying | extended | | |
initial_response_code | character varying(45) | | | NULL::character varying | extended | | |
initial_response_status | character varying(3000) | | | NULL::character varying | extended | | |
merchant_id | character varying(45) | | | NULL::character varying | extended | | |
instruction_id | character varying(45) | | | NULL::character varying | extended | | |
consent_id | character varying(45) | | | NULL::character varying | extended | | |
secondary_id | character varying(45) | | | NULL::character varying | extended | | |
settlement_status | character varying(45) | | | NULL::character varying | extended | | |
utr_no | character varying(45) | | | NULL::character varying | extended | | |
created_on | character varying(45) | | | NULL::character varying | extended | | |
modified_on | character varying(45) | | | NULL::character varying | extended | | |
initial_transactionid | character varying(100) | | | NULL::character varying | extended | | |
Indexes:
"ybl_payoutapi_response_pkey" PRIMARY KEY, btree (id)
Access method: heapSequence description
Code:
[ \d+ test_id_seq
Sequence "public.test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 9 | 9 | 9223372036854775807 | 1 | no | 1postgres version
Code:select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)Where i am going wrong not getting any clue
Thanks and Regards
i want to convert an existing column (id) int primary key to serial in postgresql i tried by creating a sequence and mapping the id column of table to the newly created sequence but its giving an error Kindly find the details below
Code:ALTER TABLE ybl_payoutapi_response ALTER id SET DEFAULT nextval('test_id_seq');
ERROR: column "id" is of type integer but default expression is of type character varying
HINT: You will need to rewrite or cast the expression.table description
Code:[ \d+ ybl_payoutapi_response;
Table "public.ybl_payoutapi_response"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------------+-------------------------+-----------+----------+-------------------------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
transaction_type | character varying(45) | | | NULL::character varying | extended | | |
ybl_account_no | character varying(100) | | | NULL::character varying | extended | | |
acc_name | character varying(45) | | | NULL::character varying | extended | | |
beneficiary_code | character varying(45) | | | NULL::character varying | extended | | |
ifsc_code | character varying(45) | | | NULL::character varying | extended | | |
beneficiary_acc_no | character varying(100) | | | NULL::character varying | extended | | |
beneficiary_acc_name | character varying(100) | | | NULL::character varying | extended | | |
unique_code | character varying(45) | | | NULL::character varying | extended | | |
payout_date | character varying(45) | | | NULL::character varying | extended | | |
amount | character varying(45) | | | NULL::character varying | extended | | |
payout_ref_no | character varying(45) | | | NULL::character varying | extended | | |
initial_response_code | character varying(45) | | | NULL::character varying | extended | | |
initial_response_status | character varying(3000) | | | NULL::character varying | extended | | |
merchant_id | character varying(45) | | | NULL::character varying | extended | | |
instruction_id | character varying(45) | | | NULL::character varying | extended | | |
consent_id | character varying(45) | | | NULL::character varying | extended | | |
secondary_id | character varying(45) | | | NULL::character varying | extended | | |
settlement_status | character varying(45) | | | NULL::character varying | extended | | |
utr_no | character varying(45) | | | NULL::character varying | extended | | |
created_on | character varying(45) | | | NULL::character varying | extended | | |
modified_on | character varying(45) | | | NULL::character varying | extended | | |
initial_transactionid | character varying(100) | | | NULL::character varying | extended | | |
Indexes:
"ybl_payoutapi_response_pkey" PRIMARY KEY, btree (id)
Access method: heapSequence description
Code:
[ \d+ test_id_seq
Sequence "public.test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 9 | 9 | 9223372036854775807 | 1 | no | 1postgres version
Code:select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)Where i am going wrong not getting any clue
Thanks and Regards