Question 212075 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to delete all records using go-pg package

I use go-pg package to work with postgres. I have the following model:

type DataItem struct{
    tableName struct{} `pg:"some_table"`

    SomeID    string    `pg:"some_id,pk"`
    SomeData  string    `pg:"some_data"`
    UpdatedAt time.Time `pg:"updated_at"`
}

At some point I need to delete all records from the table. So I do:


package storage

import (
    "github.com/go-pg/pg/v9"
)

type DataItem struct{
    tableName struct{} `pg:"some_table"`

    SomeID    string    `pg:"some_id,pk"`
    SomeData  string    `pg:"some_data"`
    UpdatedAt time.Time `pg:"updated_at"`
}

type PostgresStorage struct {
    db *pg.DB
}

func Connect(ctx context.Context, cfg Config) (*PostgresStorage, error) {
    db, err := database.Connect(ctx, ....)
    if err != nil {
        // ...
    }

    return &PostgresStorage{db: db}, nil
}

func (p *PostgresStorage) deleteAll(ctx context.Context) error {
    model := DataItem{}

    _, err := p.db.ModelContext(ctx, &model).Delete()
    if err != nil {
        return fmt.Errorf("DELETE: %w", err)
    }

    return nil
}

And I get an error:

pg: Update and Delete queries require Where clause (try WherePK)

I need to delete all records with no conditions. WherePK() also doesn’t have sence here as I don’t pick records by primary key.

What should I put to WHERE?

Tags:

2

Answers


  1. 0

    You can use the boolean constant true as the sole conditional expression, i.e.

    DELETE FROM ... WHERE true;

    I’m not 100% sure what the go-pg method call for that would look like but probably something like this:

    p.db.ModelContext(ctx, &model).Where("?", true).Delete()
    Login or Signup to reply.
  2. 0

    By adding.Where("TRUE") to your Delete() call(as @mkopriva mentioned in comments), you are effectively creating a WHERE clause that will always evaluate to true, matching all rows in the table. This allows you to delete all records without specifying any specific conditions.

    Here’s how you can modify your code to achieve this:

    package storage

import (
    "context"
    "github.com/go-pg/pg/v9"
)

type DataItem struct {
    tableName struct{} `pg:"some_table"`

    SomeID    string    `pg:"some_id,pk"`
    SomeData  string    `pg:"some_data"`
    UpdatedAt time.Time `pg:"updated_at"`
}

type PostgresStorage struct {
    db *pg.DB
}

func Connect(ctx context.Context, cfg Config) (*PostgresStorage, error) {
    db, err := database.Connect(ctx, ....)
    if err != nil {
        // ...
    }

    return &PostgresStorage{db: db}, nil
}

func (p *PostgresStorage) deleteAll(ctx context.Context) error {
    model := DataItem{}

    _, err := p.db.ModelContext(ctx, &model).Where("TRUE").Delete()
    if err != nil {
        return fmt.Errorf("DELETE: %w", err)
    }

    return nil
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search