I had a column in postgresSQL which was serial. The column was generated by postgresSQL so I didn't have to input it. After transferring it from serial to integer, I found I couldn't write it to database in FME workspace, which may have already cached previous database information. I inspected the data and figured that all the values of this column were transferred to null when writing to database. To solve it, I deleted the column and recreated it with same name in DB. Then everything works. Is this a bug of FME? By the way I am using 2018.
Hi @zhangjinzhou, the 'serial' type in PostgerSQL is implemented as 'integer' with a 'sequence' as 'default' for the column. To stop generating sequence numbers, you will have to drop the 'default' and then drop the 'sequence'.
If you have created the table containing a serial column with FME PostgreSQL writer, the sequence has been created with this name.
<table name>_<column name>_seqFor example, if the name of a serial type column in 'serial_test' table was 'serial_id', the sequence name for the column would be
serial_test_serial_id_seqMake sure the actual sequence name using pgAdmin, since the naming convention may be different depending on how you created the table.
Assuming the table name and column name were above mentioned, the SQL statements for dropping the serial implementation (i.e. default and sequence) are:
alter table serial_test alter column serial_id drop default;
drop sequence serial_test_serial_id_seq;
Also if you need to drop the 'not null' constraint, execute this statement additionally.
alter table serial_test alter column serial_id drop not null;Hope this helps.
+2takashi wrote:
Hi @zhangjinzhou, the 'serial' type in PostgerSQL is implemented as 'integer' with a 'sequence' as 'default' for the column. To stop generating sequence numbers, you will have to drop the 'default' and then drop the 'sequence'.
If you have created the table containing a serial column with FME PostgreSQL writer, the sequence has been created with this name.
<table name>_<column name>_seqFor example, if the name of a serial type column in 'serial_test' table was 'serial_id', the sequence name for the column would be
serial_test_serial_id_seqMake sure the actual sequence name using pgAdmin, since the naming convention may be different depending on how you created the table.
Assuming the table name and column name were above mentioned, the SQL statements for dropping the serial implementation (i.e. default and sequence) are:
alter table serial_test alter column serial_id drop default;
drop sequence serial_test_serial_id_seq;
Also if you need to drop the 'not null' constraint, execute this statement additionally.
alter table serial_test alter column serial_id drop not null;Hope this helps.
takashi wrote:
Hi @zhangjinzhou, the 'serial' type in PostgerSQL is implemented as 'integer' with a 'sequence' as 'default' for the column. To stop generating sequence numbers, you will have to drop the 'default' and then drop the 'sequence'.
If you have created the table containing a serial column with FME PostgreSQL writer, the sequence has been created with this name.
<table name>_<column name>_seqFor example, if the name of a serial type column in 'serial_test' table was 'serial_id', the sequence name for the column would be
serial_test_serial_id_seqMake sure the actual sequence name using pgAdmin, since the naming convention may be different depending on how you created the table.
Assuming the table name and column name were above mentioned, the SQL statements for dropping the serial implementation (i.e. default and sequence) are:
alter table serial_test alter column serial_id drop default;
drop sequence serial_test_serial_id_seq;
Also if you need to drop the 'not null' constraint, execute this statement additionally.
alter table serial_test alter column serial_id drop not null;Hope this helps.
+2takashi wrote:
The SQL statements I have posted just drop the constraints 'default' and the 'sequence' object from the 'serial' type column in order to change its data type to 'integer'. I don't think the statements could drop the existing column itself.
Thank you so much takashi!
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.