ERROR with ActiveRecord::loadDefaultValues(), id serial and posgresql

[makroxyz]

using $model->loadDefaultValues() where model ID is serial, throws this error:
ERROR: invalid input syntax for integer: "nextval('tbl_mytable_id_seq'"

[cebe]

can you please post the complete CREATE TABLE statement?

[makroxyz]

sure

CREATE TABLE tbl_mytable
(
id serial NOT NULL,
user_id integer NOT NULL,
notes character varying(255),
CONSTRAINT tbl_mytable_pkey PRIMARY KEY (id)
);

[qiangxue]

In general, PKs should not have default values.

[makroxyz]

I didn't set any default value. My create statement is in above comment.
if I unload database to a script I can see that line:
ALTER TABLE ONLY tbl_mytable ALTER COLUMN id SET DEFAULT nextval('tbl_mytable_id_seq'::regclass);

[cebe]

looks like serial is implemented via default value internally in postgres.

[qiangxue]

@makroxyz I am not saying you did wrong. ;) I mean in our core code when loading table schema, we should not load default values for PK columns. This needs to be fixed (not your code.) Sorry for confusion.

[makroxyz]

@qiangxue I didn't understand I did wrong! :) I just want to give more infos ;)
Sorry for confusion too!

Another thing:
yii\db\pgsql\Schema::loadColumnSchema sets phpType to "string" for smallint columns.
$typeMap doesn't have 'int2' column type... I think smallint are int2 in PostgreSQL 9.3

should I open a new issue?

[cebe]

should I open a new issue?

yes please.

[makroxyz]

I'm still having this error after $model->loadDefaultValues()
ERROR: invalid input syntax for integer: "nextval('tbl_mytable_id_seq'::regclass)"

$column->defaultValue is loaded in loadColumnSchema function. The fix doesn't unset defaultValue if column is PK...

[qiangxue]

Thanks! Should be fixed now.