Thread: Error updating column of type text as boolean type

Hi guys, I was looking datatype bugs for postgresql 10.6 but i not found anything.

I am trying to update the column of type text column but i get an error for boolean type. In other tables the behavior of the text type column is normal.

Has anyone had this problem before?

accounting@accounting=> \dS+ stock_asset_document
                                                           Table "public.stock_asset_document"
     Column     |           Type           | Collation | Nullable |                    Default                    | Storage  | Stats target | Description
----------------+--------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 id             | bigint                   |           | not null | nextval('seq_stock_asset_document'::regclass) | plain    |              |
 tenant_id      | bigint                   |           | not null |                                               | plain    |              |
 registry_id    | uuid                     |           | not null |                                               | plain    |              |
 revision_id    | uuid                     |           | not null |                                               | plain    |              |
 negotiation_id | bigint                   |           | not null |                                               | plain    |              |
 competence_at  | date                     |           | not null |                                               | plain    |              |
 is_deleted     | boolean                  |           | not null |                                               | plain    |              |
 created_at     | timestamp with time zone |           | not null | now()                                         | plain    |              |
 updated_at     | timestamp with time zone |           |          |                                               | plain    |              |
 number         | bigint                   |           |          |                                               | plain    |              |
 serial         | text                     |           |          |                                               | extended |              |
Indexes:
    "pk_stock_asset_document" PRIMARY KEY, btree (id)
    "uk_stock_asset_document_registry_revision" UNIQUE CONSTRAINT, btree (registry_id, revision_id)
    "ix_stock_asset_document_tenant_deleted" btree (tenant_id, is_deleted)
    "ix_stock_asset_document_tenant_registry_revision_deleted" btree (tenant_id, registry_id, revision_id, is_deleted)
Referenced by:
    TABLE "stock_asset" CONSTRAINT "fk_stock_asset_entry_stock_asset_document" FOREIGN KEY (entry_document_id) REFERENCES stock_asset_document(id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "stock_asset" CONSTRAINT "fk_stock_asset_output_stock_asset_document" FOREIGN KEY (output_document_id) REFERENCES stock_asset_document(id) ON UPDATE RESTRICT ON DELETE RESTRICT

accounting@accounting=> UPDATE stock_asset_document SET serial = '3' AND number = 36245 WHERE negotiation_id = 15948333;

ERROR:  22P02: invalid input syntax for type boolean: "3"
LINE 1: UPDATE stock_asset_document SET serial = '3' AND number = 36...
                                                 ^
LOCATION:  boolin, bool.c:154
Time: 16.427 ms
accounting@accounting=> SELECT version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

Time: 15.989 ms

Atenciosamente,

Márllius de Carvalho Ribeiro

On Wed, Jul 3, 2019 at 10:09 AM Marllius <marllius@gmail.com> wrote:

Hi guys, I was looking datatype bugs for postgresql 10.6 but i not found anything.

I am trying to update the column of type text column but i get an error for boolean type. In other tables the behavior of the text type column is normal.

Has anyone had this problem before?

accounting@accounting=> \dS+ stock_asset_document
                                                           Table "public.stock_asset_document"
     Column     |           Type           | Collation | Nullable |                    Default                    | Storage  | Stats target | Description
----------------+--------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 id             | bigint                   |           | not null | nextval('seq_stock_asset_document'::regclass) | plain    |              |
 tenant_id      | bigint                   |           | not null |                                               | plain    |              |
 registry_id    | uuid                     |           | not null |                                               | plain    |              |
 revision_id    | uuid                     |           | not null |                                               | plain    |              |
 negotiation_id | bigint                   |           | not null |                                               | plain    |              |
 competence_at  | date                     |           | not null |                                               | plain    |              |
 is_deleted     | boolean                  |           | not null |                                               | plain    |              |
 created_at     | timestamp with time zone |           | not null | now()                                         | plain    |              |
 updated_at     | timestamp with time zone |           |          |                                               | plain    |              |
 number         | bigint                   |           |          |                                               | plain    |              |
 serial         | text                     |           |          |                                               | extended |              |
Indexes:
    "pk_stock_asset_document" PRIMARY KEY, btree (id)
    "uk_stock_asset_document_registry_revision" UNIQUE CONSTRAINT, btree (registry_id, revision_id)
    "ix_stock_asset_document_tenant_deleted" btree (tenant_id, is_deleted)
    "ix_stock_asset_document_tenant_registry_revision_deleted" btree (tenant_id, registry_id, revision_id, is_deleted)
Referenced by:
    TABLE "stock_asset" CONSTRAINT "fk_stock_asset_entry_stock_asset_document" FOREIGN KEY (entry_document_id) REFERENCES stock_asset_document(id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "stock_asset" CONSTRAINT "fk_stock_asset_output_stock_asset_document" FOREIGN KEY (output_document_id) REFERENCES stock_asset_document(id) ON UPDATE RESTRICT ON DELETE RESTRICT

accounting@accounting=> UPDATE stock_asset_document SET serial = '3' AND number = 36245 WHERE negotiation_id = 15948333;

Don't use AND. Use a comma:

UPDATE stock_asset_document SET serial = '3', number = 36245 WHERE negotiation_id = 15948333;

ref: https://www.postgresql.org/docs/11/sql-update.html

 

ERROR:  22P02: invalid input syntax for type boolean: "3"
LINE 1: UPDATE stock_asset_document SET serial = '3' AND number = 36...
                                                 ^
LOCATION:  boolin, bool.c:154
Time: 16.427 ms
accounting@accounting=> SELECT version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

Time: 15.989 ms

Atenciosamente,

Márllius de Carvalho Ribeiro

--

Money is the root of all evil.

Evil is the root of all money.

With that in mind, money is made by the government ...

Maranatha! <><
John McKown