So, on my RDS postgres, that is currently running as a DMS CDC from our previous DB, our ID fields lost the serial type, and it got moved to INT without a NEXTVAL. So, using the example of our table public.pageviews, I tried
ALTER TABLE public.pageviews ALTER COLUMN id TYPE SERIAL;
and it is giving me that SERIAL is not a valid data type.
When I run
ALTER TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval('pageviews_id_seq');
I am getting the following:
ERROR: null value in column "c_key" violates not-null constraint DETAIL: Failing row contains (null, 2018-01-03 14:17:28.952708, master, 1528336, ALTER TABLE, 0, , public, ALTER TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval...). CONTEXT: SQL statement "insert into public.awsdms_ddl_audit values ( default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry )" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement
********** Error **********
ERROR: null value in column "c_key" violates not-null constraint SQL state: 23502 Detail: Failing row contains (null, 2018-01-03 14:17:28.952708, master, 1528336, ALTER TABLE, 0, , public, ALTER TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval...). Context: SQL statement "insert into public.awsdms_ddl_audit values ( default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry )" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement
On that, I am not a PG DBA by trade (MSSQL that got lucked into this job) so I am not familiar enough with how to really make my way around things, on pgadmin, I have tried to set the default value for ID to a nextval, but I am not getting anywhere with that.
I'm on windows and have really no experience with psql commands (learning them, but the curve is steep)