Golang中处理PostgreSQL插入错误："Error pq: syntax error at or near ",""

Golang中处理PostgreSQL插入错误：“Error pq: syntax error at or near “,”” 我已经尝试了所有方法……[被这个问题卡住了，而且还需要做一个批量版本]。

有人看出问题出在哪里吗？

底部是创建表的语句：

pmnt 是一个结构体

func (pmnt tPGPayment) SingleInsert(pgdb *pgsql.DB) (err error) {

	ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancelfunc()

	sqlStatement := "INSERT INTO public.salespayments (invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) VALUES (?, ?, ?, ?, ?)"
	stmt, err := pgdb.PrepareContext(ctx, sqlStatement)
	if err != nil {
		grpcLog.Errorf("PgSql SingleInsert: Error %s when preparing SQL statement: `%s`\n", err, sqlStatement)
		return err
	}
	defer stmt.Close()

	_, err = stmt.ExecContext(ctx, pmnt.InvoiceNumber, pmnt.PayDateTime_Ltz, pmnt.PayTimestamp_Epoc, pmnt.Paid, pmnt.FinTransactionID)
	if err != nil {
		grpcLog.Errorf("PgSql SingleInsert: Error %s when inserting row into table: `%s`\n", err, "salespayments")
		return err
	}
	if vGeneral.Debuglevel >= 2 {
		grpcLog.Infoln("PgSql SingleInsert: \t\t\t1 row inserted")
	}
	return nil
}

错误信息：

2024/09/01 13:38:55 ERROR: PgSql SingleInsert: Error pq: syntax error at or near "," when preparing SQL statement: `INSERT INTO public.salespayments (invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) VALUES (?, ?, ?, ?, ?)`
2024/09/01 13:38:55 ERROR: PgSql SingleInsert: Error pq: syntax error at or near "," when preparing SQL statement: `INSERT INTO public.salespayments (invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) VALUES (?, ?, ?, ?, ?)`
2024/09/01 13:38:55 ERROR: PgSql SingleInsert: Error pq: syntax error at or near "," when preparing SQL statement: `INSERT INTO public.salespayments (invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) VALUES (?, ?, ?, ?, ?)`

结构体定义：

type TPPayment struct {
	InvoiceNumber     string  `json:"invoiceNumber,omitempty" avro:"invoiceNumber" db:"invoicenumber"`
	PayDateTime_Ltz   string  `json:"payDateTime_Ltz,omitempty" avro:"payDateTime_Ltz" db:"paydatetime_ltz"`
	PayTimestamp_Epoc string  `json:"payTimestamp_Epoc,omitempty" avro:"payTimestamp_Epoc" db:"paytimestamp_epoc"`
	Paid              float64 `json:"paid,omitempty" avro:"paid" db:"paid"`
	FinTransactionID  string  `json:"finTransactionId,omitempty" avro:"finTransactionId" db:"fintransactionid"`
}

创建表：

CREATE TABLE public.salespayments (
    invoicenumber varchar(40) NOT NULL,
    paydatetime_ltz varchar(32),
    paytimestamp_epoc varchar(14),
    paid decimal(10,2),
    fintransactionid varchar(40),
    created_at timestamptz DEFAULT NOW() NOT NULL,
    PRIMARY KEY (invoicenumber))
TABLESPACE pg_default;

CREATE INDEX salespayments_invoicenumber_idx ON public.salespayments (invoicenumber);

ALTER TABLE IF EXISTS public.salespayments OWNER to dbadmin;

更多关于Golang中处理PostgreSQL插入错误："Error pq: syntax error at or near ",""的实战教程也可以访问 https://www.itying.com/category-94-b0.html

3 回复

使用 $1, $2, … 作为参数的占位符…

更多关于Golang中处理PostgreSQL插入错误："Error pq: syntax error at or near ",""的实战系列教程也可以访问 https://www.itying.com/category-94-b0.html


有趣的是，我确实是那样写的，但当时有其他错误，我想是那些错误让我分心了……以为那是问题所在。

谢谢。

G

问题出在SQL语句使用了?作为占位符。PostgreSQL不支持?占位符，应该使用$1, $2, $3...格式的占位符。

修改后的代码：

func (pmnt tPGPayment) SingleInsert(pgdb *pgsql.DB) (err error) {

	ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancelfunc()

	sqlStatement := "INSERT INTO public.salespayments (invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) VALUES ($1, $2, $3, $4, $5)"
	stmt, err := pgdb.PrepareContext(ctx, sqlStatement)
	if err != nil {
		grpcLog.Errorf("PgSql SingleInsert: Error %s when preparing SQL statement: `%s`\n", err, sqlStatement)
		return err
	}
	defer stmt.Close()

	_, err = stmt.ExecContext(ctx, pmnt.InvoiceNumber, pmnt.PayDateTime_Ltz, pmnt.PayTimestamp_Epoc, pmnt.Paid, pmnt.FinTransactionID)
	if err != nil {
		grpcLog.Errorf("PgSql SingleInsert: Error %s when inserting row into table: `%s`\n", err, "salespayments")
		return err
	}
	if vGeneral.Debuglevel >= 2 {
		grpcLog.Infoln("PgSql SingleInsert: \t\t\t1 row inserted")
	}
	return nil
}

批量插入版本示例：

func BatchInsert(pgdb *pgsql.DB, payments []tPGPayment) (err error) {
	ctx, cancelfunc := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancelfunc()

	sqlStatement := `
		INSERT INTO public.salespayments 
		(invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) 
		VALUES ($1, $2, $3, $4, $5)`

	stmt, err := pgdb.PrepareContext(ctx, sqlStatement)
	if err != nil {
		grpcLog.Errorf("PgSql BatchInsert: Error %s when preparing SQL statement\n", err)
		return err
	}
	defer stmt.Close()

	for _, pmnt := range payments {
		_, err = stmt.ExecContext(ctx, 
			pmnt.InvoiceNumber, 
			pmnt.PayDateTime_Ltz, 
			pmnt.PayTimestamp_Epoc, 
			pmnt.Paid, 
			pmnt.FinTransactionID)
		if err != nil {
			grpcLog.Errorf("PgSql BatchInsert: Error %s when inserting row\n", err)
			return err
		}
	}

	if vGeneral.Debuglevel >= 2 {
		grpcLog.Infof("PgSql BatchInsert: %d rows inserted\n", len(payments))
	}
	return nil
}

使用pgx驱动时也可以直接使用Exec而不需要Prepare：

func (pmnt tPGPayment) DirectInsert(pgdb *pgsql.DB) (err error) {
	ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancelfunc()

	sqlStatement := "INSERT INTO public.salespayments (invoicenumber, paydatetime_ltz, paytimestamp_epoc, paid, fintransactionid) VALUES ($1, $2, $3, $4, $5)"
	
	_, err = pgdb.ExecContext(ctx, sqlStatement, 
		pmnt.InvoiceNumber, 
		pmnt.PayDateTime_Ltz, 
		pmnt.PayTimestamp_Epoc, 
		pmnt.Paid, 
		pmnt.FinTransactionID)
	
	if err != nil {
		grpcLog.Errorf("PgSql DirectInsert: Error %s when inserting row\n", err)
		return err
	}
	
	return nil
}
回到顶部