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.

Issue when cache information from postgreSQL

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>_seq

For 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_seq

Make 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.

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>_seq

For 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_seq

Make 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.

Sorry I didn't make the question clear. I already solved the problem by dropping old column and creating a new one. I am curious about why switching the column in database doesn't work like dropping and creating. Can't I switch a serial column to a regular int column without dropping?

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>_seq

For 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_seq

Make 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.

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.

I don't know much about SQL so I thought the drop command drops the whole column. I think the problem is that I didn't edit 'default' and 'sequence' constrains. By the way I use pgAdmin for database edits. Will check with our database developer.

Thank you so much takashi!

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded