Ошибка неверный синтаксис для типа bigint

createdAt: {
    type: DataTypes.INTEGER,
    defaultValue: Sequelize.literal('current_millis()'),
  }

 Unexpected error: invalid input syntax for type bigint: "[object Object]"
SequelizeDatabaseError: invalid input syntax for type bigint: "[object Object]"

{
  type: [Function: INTEGER] {
    types: {
      postgres: [Array],
      mysql: [Array],
      mariadb: [Array],
      sqlite: [Array],
      mssql: [Array]
    },
    key: 'INTEGER'
  },
  defaultValue: Literal { val: 'current_millis()' }
}

Я новичок в PostgreSQL. Я создал ниже функцию PostgreSQL и получил ошибку, как показано ниже:

ERROR:  invalid input syntax for type bigint: "expenseid"

CREATE OR REPLACE FUNCTION insertorupdateinvoice(invoice jsonb)
     RETURNS void
LANGUAGE plpgsql
AS $BODY$
Declare _invoiceid bigint;
begin
  --Consider all columns in specialist table as character varying and code column as integer.
  insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
  select j.invoice->>'expenseid'::bigint, 
         j.invoice->>'invoiceno', 
         (j.invoice->>'transactiondate')::date, 
         j.invoice->>'totalinvoiceamount'::double precision, 
         j.invoice->>'invoicedoc', 
         j.invoice->>'createdby'::bigint, 
         (j.invoice->>'createdon')::timestamp without time zone
  from jsonb_array_elements(invoice) as j(invoice)
  returning invoiceid into _invoiceid;
  
  insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
                              itemcategory, itemtype, status, isrejected, createdby, createdon)
  select _invoiceid::bigint, 
        x.invoice->>'transactiondate'::date, 
        x.invoice->>'merchantname', 
        x.invoice->>'amount'::double precision, 
        x.invoice->>'departmentid'::integer, 
        x.invoice->>'policyid'::integer, 
        x.invoice->>'itemdescription', 
        x.invoice->>'itemcategory'::integer, 
        x.invoice->>'itemtype'::integer, 
        x.invoice->>'status'::boolean, 
        x.invoice->>'isrejected'::boolean, 
        x.invoice->>'createdby'::bigint, 
        (x.invoice->>'createdon')::timestamp without time zone
  from jsonb_array_elements(invoice ->'lineitems') as x;
end;
$BODY$;

Я выполняю функцию, как показано ниже:

select * from insertorupdateinvoice('{"expenseid":1,
    "invoiceno":"04012022",
    "transactiondate":"2022-01-04",
    "totalinvoiceamount":1000.00,
    "invoicedoc":"invoicedoc",
    "createdby":"1",
    "list":[
      {"transactiondate":"2022-01-01", "merchantname":"Apple", "amount":"100.50", "departmentid":"1","policyid":"1", "itemdescription":"iphone 14 pro max", "itemcategory":"55", "itemtype":"499", "status":"true", "isrejected":"false", "createdby":"1"},
      {"transactiondate":"2022-01-02", "merchantname":"Samsung", "amount":"1050.35", "departmentid":"2","policyid":"2", "itemdescription":"samsung galaxy tab", "itemcategory":"40", "itemtype":"50", "status":"true", "isrejected":"false", "createdby":"1"},
      {"transactiondate":"2022-01-03", "merchantname":"Big bazar", "amount":"555.75", "departmentid":"3","policyid":"3", "itemdescription":"grocerry", "itemcategory":"5", "itemtype":"90", "status":"false", "isrejected":"false", "createdby":"1"}
    ]}');

Ошибка, которую я получаю, как показано ниже:

ERROR:  invalid input syntax for type bigint: "expenseid"
LINE 2:   select j.invoice->>'expenseid'::bigint, 
                             ^
QUERY:  insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
  select j.invoice->>'expenseid'::bigint, 
         j.invoice->>'invoiceno', 
         (j.invoice->>'transactiondate')::date, 
         j.invoice->>'totalinvoiceamount'::double precision, 
         j.invoice->>'invoicedoc', 
         j.invoice->>'createdby'::bigint, 
         (j.invoice->>'createdon')::timestamp without time zone
  from jsonb_array_elements(invoice) as j(invoice)
  returning invoiceid
CONTEXT:  PL/pgSQL function insertorupdateinvoice(jsonb) line 5 at SQL statement
SQL state: 22P02

Итак, я получил решение для этого, как показано ниже, поскольку не используйте имя jsonb при извлечении значений.

    CREATE OR REPLACE FUNCTION public.insertorupdateinvoice(
        invoice jsonb)
        RETURNS void
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    AS $BODY$
    Declare _invoiceid bigint;
    begin
      insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
      values ( (invoice->>'expenseid') :: bigint, 
               (invoice->>'invoiceno') :: character, 
               (invoice->>'transactiondate') :: date, 
               (invoice->>'totalinvoiceamount') :: double precision, 
               (invoice->>'invoicedoc') :: character, 
               (invoice->>'createdby') :: bigint,
               NOW()
             )
      returning invoiceid into _invoiceid;

      insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
                                  itemcategory, itemtype, status, isrejected, createdby, createdon)
      select _invoiceid::bigint, 
            (x->>'transactiondate')::date, 
            x->>'merchantname', 
            (x->>'amount')::double precision, 
            (x->>'departmentid')::integer, 
            (x->>'policyid')::integer, 
            x->>'itemdescription', 
            (x->>'itemcategory')::integer, 
            (x->>'itemtype')::integer, 
            (x->>'status')::boolean, 
            (x->>'isrejected')::boolean, 
            (x->>'createdby')::bigint,
            NOW()
      from jsonb_array_elements(invoice ->'lineitems') as x;
    end;
    $BODY$;

Если вы не хотите потерять db.sqlite3, попробуйте сначала удалить миграции

Шаг 1: Удалите файл db.sqlite3.

Шаг 2 : $ python manage.py migrate

Шаг 3 : $ python manage.py makemigrations

Шаг 4: Создайте суперпользователя с помощью $ python manage.py createsuperuser

новый db.sqlite3 будет сгенерирован автоматически

См. Поле чужого ключа. По умолчанию поле FK будет использовать первичный ключ ссылающейся таблицы (модели), в данном случае поле id из ClassBooks. Поле id является целым числом, поэтому вы получите ошибку при попытке использовать строковое поле. Чтобы заставить это работать, из ссылки на документацию :

ForeignKey.to_field

Поле связанного объекта, к которому относится отношение. По умолчанию Django использует первичный ключ связанного объекта. Если вы ссылаетесь на другое поле, то это поле должно иметь unique=True.

Что в вашем случае становится:

b_class =models.ForeignKey(ClassBooks,to_field='name',on_delete=models.CASCADE)

Это предполагает, что поле name имеет ограничение Unique.

Хотя я не уверен, как "", "1", "2" ... сопоставить с ClassBooks.name.

Problem/Motivation

Drupal 9.2.7
Unexpected error after enabling webform_group, using PostgreSQL.

DrupalCoreDatabaseDatabaseExceptionWrapper: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: «my_test_form» LINE 5: WHERE «group_content_field_data».»entity_id» = ‘my_test_for… ^: SELECT «base_table».»id» AS «id», «base_table».»id» AS «base_table_id» FROM «group_content» «base_table» INNER JOIN «group_content_field_data» «group_content_field_data» ON «group_content_field_data».»id» = «base_table».»id» WHERE «group_content_field_data».»entity_id» = :db_condition_placeholder_0; Array ( [:db_condition_placeholder_0] => my_test_form ) in DrupalCoreEntityQuerySqlQuery->result() (line 271 of core/lib/Drupal/Core/Entity/Query/Sql/Query.php).
DrupalCoreDatabaseStatementWrapper->execute() (Line: 874)
DrupalCoreDatabaseConnection->query() (Line: 195)
DrupalCoreDatabaseDriverpgsqlConnection->query() (Line: 512)
DrupalCoreDatabaseQuerySelect->execute() (Line: 145)
DrupalCoreDatabaseDriverpgsqlSelect->execute() (Line: 271)
DrupalCoreEntityQuerySqlQuery->result() (Line: 83)
DrupalCoreEntityQuerySqlQuery->execute() (Line: 166)
Drupalwebform_groupWebformGroupManager->getCurrentGroupContent() (Line: 140)
Drupalwebform_groupWebformGroupManager->getCurrentUserGroupRoles() (Line: 309)
webform_group_webform_access()
call_user_func_array() (Line: 403)
DrupalCoreExtensionModuleHandler->invokeAll() (Line: 97)
DrupalCoreEntityEntityAccessControlHandler->access() (Line: 314)
DrupalCoreEntityEntityBase->access() (Line: 65)
DrupalCoreEntityEntityAccessCheck->access()
call_user_func_array() (Line: 159)
DrupalCoreAccessAccessManager->performCheck() (Line: 135)
DrupalCoreAccessAccessManager->check() (Line: 112)
DrupalCoreAccessAccessManager->checkRequest() (Line: 109)
DrupalCoreRoutingAccessAwareRouter->checkAccess() (Line: 94)
DrupalCoreRoutingAccessAwareRouter->matchRequest() (Line: 112)
SymfonyComponentHttpKernelEventListenerRouterListener->onKernelRequest()
call_user_func() (Line: 142)
DrupalComponentEventDispatcherContainerAwareEventDispatcher->dispatch() (Line: 134)
SymfonyComponentHttpKernelHttpKernel->handleRaw() (Line: 80)
SymfonyComponentHttpKernelHttpKernel->handle() (Line: 57)
DrupalCoreStackMiddlewareSession->handle() (Line: 47)
DrupalCoreStackMiddlewareKernelPreHandle->handle() (Line: 106)
Drupalpage_cacheStackMiddlewarePageCache->pass() (Line: 85)
Drupalpage_cacheStackMiddlewarePageCache->handle() (Line: 47)
DrupalCoreStackMiddlewareReverseProxyMiddleware->handle() (Line: 52)
DrupalCoreStackMiddlewareNegotiationMiddleware->handle() (Line: 23)
StackStackedHttpKernel->handle() (Line: 717)
DrupalCoreDrupalKernel->handle() (Line: 19)

Steps to reproduce

Enable webform_group
And try to build the my_test_form webform

Proposed resolution

Cast entity id as (int).

Remaining tasks

To be patched

User interface changes

no

API changes

no

Data model changes

no