Error when trying to record a new record in Posgress.
Hello everyone.
I have a problem trying to create a new record in Posgress. The error log returns:
message:
“SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type integer: “”\nCONTEXT: unnamed portal parameter $1 = ‘’”
Configuration of the job_id field (auto increment):
Type: int4
Length: 32
Default: nextval(‘app_portfolio_id_seq’::regclass)
key: primary
Are you trying to assign a value in the insert clause to an auto+increment field?
Hello @scalaris.
No. What I have is a form with a hidden field (job_id) that only serves to edit a record and when it has the ID (job_id) in the URL
Can you show the SQL ?
Are you casting job_id as an int anywhere?
Sure.
SQL para atualizar os dados:
update “app_portfolio” set “job_type” = ?, “job_title” = ?, “job_content” = ?, “job_slug” = ?, “job_cliente” = ?, “job_cover” = ?, “job_categorias” = ?, “job_date_update” = ?, “job_status” = ?, “job_link” = ? where “job_id” = ?
SQL para inserir novo registro:
insert into “app_portfolio” (“job_categorias”, “job_cliente”, “job_content”, “job_cover”, “job_date_create”, “job_link”, “job_slug”, “job_status”, “job_title”, “job_type”) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Try explicitly casting your job_id as a number before sending it to the query
How have you defined the primary key? As a SERIAL?
If you wish a serial column to have a unique constraint or be a primary key, it must be specified, just like any other data type.
E.g.
JOB_ID SERIAL PRIMARY KEY
I’ve recreated this table a few times, both in Wappler and NavCat (Windows). To edit the data it works without problems but to insert a new record it always gives the same error.
The value your input sends to insert record step is always a string. The client-side formatter won’t change this - on the server side this is still a string. If you want to convert the value to number this must happen in the insert record step. You need to apply the To Number formatter to the $_POST variable, selected as a value.
If you’re sometimes feeding the insert from a DB query and other times $_POST , you can multiply by 1 to make sure it’s a number (toNumber throws an error if it is already a number, which it will be from a DB query) - I find this useful for Library actions in particular
But this behavior is strange since I always do it the same way when I create a table. The other tables of the bank the autoincrement primary key is called ID and in the app_portfolio table I gave the name of job_id. This is the only difference.
Be careful with ID in PG. It’s a Keyword - it’s non-reserved but still a keyword.
Why are you inserting a value in this field if it is set to auto increment?
Shouldn’t job_id only appear in the WHERE condition of the update step?
But I’m going to delete the table and do it again in Wappler
But your screenshot is of an insert step so $_POST.job_id will be empty - just remove that line from the insert. It will be added by the auto increment
Hey guys.
I solved the problem.
I created a file and an API to create and another one to update the record. This way it worked. But I’m still intrigued because the other forms I have on the page are the same for inserting and updating and only this one, app_portfolio, which gave this problem. But it’s already resolved.
Thanks to everyone who helped me.
