gosql

package module
v0.9.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 19, 2024 License: BSD-2-Clause Imports: 7 Imported by: 3

README

gosql

Postgres SQL builder

Create table (support full PG16 SQL specification) examples
Table with named primary key constraint
CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

films := gosql.CreateTable("films")
films.AddColumn("code").Type("char(5)").Constraint().Name("firstkey").PrimaryKey()
films.AddColumn("title").Type("varchar(40)").Constraint().NotNull()
films.AddColumn("did").Type("integer").Constraint().NotNull()
films.AddColumn("date_prod").Type("date")
films.AddColumn("kind").Type("varchar(10)")
films.AddColumn("len").Type("interval hour to minute")
Table with unique named constraint
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

films := gosql.CreateTable("films")
films.AddColumn("code").Type("char(5)")
films.AddColumn("title").Type("varchar(40)")
films.AddColumn("did").Type("integer")
films.AddColumn("date_prod").Type("date")
films.AddColumn("kind").Type("varchar(10)")
films.AddColumn("len").Type("interval hour to minute")
films.AddConstraint().Name("production").Unique().Columns().Add("date_prod")
Table with primary key constraint
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer")
distributors.AddColumn("name").Type("varchar(40)")
distributors.AddConstraint().PrimaryKey().Columns().Add("did")
Table with primary key in column definition
CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

distributors = gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer").Constraint().PrimaryKey()
distributors.AddColumn("name").Type("varchar(40)")
Table with named constraint not null
CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer").Constraint().Name("no_null").NotNull()
distributors.AddColumn("name").Type("varchar(40)").Constraint().NotNull()
Table with unique column
CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer")
distributors.AddColumn("name").Type("varchar(40)").Constraint().Unique()
Table with unique constraint with storage parameter
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer")
distributors.AddColumn("name").Type("varchar(40)")
unique := distributors.AddConstraint().Unique()
unique.Columns().Add("name")
unique.IndexParameters().With().Add("fillfactor=70")
distributors.With().Expression().Add("fillfactor=70")
Table with name constraint primary key on multiple column
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

films := gosql.CreateTable("films")
films.AddColumn("code").Type("char(5)")
films.AddColumn("title").Type("varchar(40)")
films.AddColumn("did").Type("integer")
films.AddColumn("date_prod").Type("date")
films.AddColumn("kind").Type("varchar(10)")
films.AddColumn("len").Type("interval hour to minute")
films.AddConstraint().Name("code_title").PrimaryKey().Columns().Add("code", "title")
Table with check constraint
CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer").Constraint().Check().Expression().Add("did > 100")
distributors.AddColumn("name").Type("varchar(40)")
Table with default values in column definition
CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("name").Type("varchar(40)").Constraint().Default("'Luso Films'")
distributors.AddColumn("did").Type("integer").Constraint().Default("nextval('distributors_serial')")
distributors.AddColumn("modtime").Type("timestamp").Constraint().Default("current_timestamp")
Table with tablespace
CREATE TABLE cinemas (
    id serial,
    name text,
    location text
) TABLESPACE diskvol1;

cinemas := gosql.CreateTable("cinemas")
cinemas.AddColumn("id").Type("serial")
cinemas.AddColumn("name").Type("text")
cinemas.AddColumn("location").Type("text")
cinemas.TableSpace("diskvol1")
Table with options and default constraint
CREATE TABLE employees OF employee_type (
    PRIMARY KEY (name),
    salary WITH OPTIONS DEFAULT 1000
);

employees := gosql.CreateTable("employees")
employees.OfType().Name("employee_type")
employees.OfType().Columns().AddColumn("name").Constraint().PrimaryKey()
salary := employees.OfType().Columns().AddColumn("salary")
salary.Constraint().Default("1000")
salary.WithOptions()
Table with excluding definition
CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

circles := gosql.CreateTable("circles")
circles.AddColumn("c").Type("circle")
exclude := circles.AddConstraint().Exclude().Using("gist")
exclude.ExcludeElement().Expression().Add("c")
exclude.With().Add("&&")
Table with named check constraint with multiple condition
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

distributors := gosql.CreateTable("distributors")
distributors.AddColumn("did").Type("integer")
distributors.AddColumn("name").Type("varchar(40)")
distributors.AddConstraint().Name("con1").
    Check().
    AddExpression("did > 100").
    AddExpression("name <> ''")
Table with partition by range and clause
CREATE TABLE measurement_year_month (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

measurement = gosql.CreateTable("measurement_year_month")
measurement.AddColumn("logdate").Type("date").Constraint().NotNull()
measurement.AddColumn("peaktemp").Type("int")
measurement.AddColumn("unitsales").Type("int")
measurement.Partition().By(gosql.PartitionByRange).Clause("EXTRACT(YEAR FROM logdate)", "EXTRACT(MONTH FROM logdate)")
Table with partition by hash
CREATE TABLE orders (
    order_id     bigint not null,
    cust_id      bigint not null,
    status       text
) PARTITION BY HASH (order_id);

orders := gosql.CreateTable("orders")
orders.AddColumn("order_id").Type("bigint").Constraint().NotNull()
orders.AddColumn("cust_id").Type("bigint").Constraint().NotNull()
orders.AddColumn("status").Type("text")
orders.Partition().By(gosql.PartitionByHash).Clause("order_id")
Table with partition for values
CREATE TABLE measurement_y2016m07
    PARTITION OF measurement (
    unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

measurement := gosql.CreateTable("measurement_y2016m07")
measurement.OfPartition().Parent("measurement")
measurement.OfPartition().Columns().AddColumn("unitsales").Constraint().Default("0")
measurement.OfPartition().Values().From().Add("'2016-07-01'")
measurement.OfPartition().Values().To().Add("'2016-08-01'")
Table with partition for values with constant MINVALUE
CREATE TABLE measurement_ym_older
    PARTITION OF measurement_year_month
    FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);

measurement = gosql.CreateTable("measurement_ym_older")
measurement.OfPartition().Parent("measurement_year_month")
measurement.OfPartition().Values().From().Add(PartitionBoundFromMin, PartitionBoundFromMin)
measurement.OfPartition().Values().To().Add("2016", "11")
Table with partition for values by range
CREATE TABLE cities_ab
    PARTITION OF cities (
    CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);

cities = gosql.CreateTable("cities_ab")
cities.OfPartition().Parent("cities")
cities.OfPartition().Columns().AddConstraint().Name("city_id_nonzero").Check().AddExpression("city_id != 0")
cities.OfPartition().Values().In().Add("'a'", "'b'")
cities.Partition().By(PartitionByRange).Clause("population")
Table with partition for values from to
CREATE TABLE cities_ab_10000_to_100000
    PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);

citiesAb := gosql.CreateTable("cities_ab_10000_to_100000")
citiesAb.OfPartition().Parent("cities_ab").Values().From().Add("10000")
citiesAb.OfPartition().Parent("cities_ab").Values().To().Add("100000")
Table with default partition
CREATE TABLE cities_partdef
    PARTITION OF cities DEFAULT;

citiesPartdef := gosql.CreateTable("cities_partdef")
citiesPartdef.OfPartition().Parent("cities")
Table with primary key generated by default
CREATE TABLE distributors (
    did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name   varchar(40) NOT NULL CHECK (name <> '')
);

distributors := gosql.CreateTable("distributors")
did := distributors.AddColumn("did").Type("integer")
did.Constraint().PrimaryKey()
did.Constraint().Generated().SetDetail(GeneratedByDefault)
name := distributors.AddColumn("name").Type("varchar(40)")
name.Constraint().NotNull()
name.Constraint().Check().Expression().Add("name <> ''")
Create Index (support full PG16 SQL specification) examples
Create simple index
CREATE UNIQUE INDEX title_idx ON films (title);

idx := gosql.CreateIndex("films", "title").Name("title_idx").Unique()
OR
idx = gosql.CreateIndex().Table("films").Name("title_idx").Unique()
idx.Expression().Add("title")
OR
idx = gosql.CreateIndex("films", "title").Unique().AutoName()
Create unique index
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

idx := gosql.CreateIndex("films", "title").Name("title_idx").Include("director", "rating").Unique()
OR
idx = gosql.CreateIndex("films", "title").AutoName().Include("director", "rating").Unique()
Create index with storage param
CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

idx := gosql.CreateIndex("films", "title").Name("title_idx").With("deduplicate_items = off")
Create index with expression
CREATE INDEX ON films ((lower(title)));

idx := gosql.CreateIndex("films", "(lower(title))")
Create index with collate
CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

idx := gosql.CreateIndex("films", `title COLLATE "de_DE"`).Name("title_idx_german")
Create index nulls first
CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

idx := gosql.CreateIndex("films", `title NULLS FIRST`).Name("title_idx_nulls_low")
Create index with using
CREATE INDEX pointloc ON points USING gist (box(location,location));

idx := gosql.CreateIndex("points", "box(location,location)").Name("pointloc").Using("gist")
Create index concurrently
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

idx := gosql.CreateIndex("sales_table", "quantity").Name("sales_quantity_index").Concurrently()
Comment examples
Comment column
COMMENT ON COLUMN table_name.column IS 'The column comment';

c := gosql.Comment().Column("table_name.column", "The column comment")
Comment table
COMMENT ON TABLE table_name IS 'The table comment';

c := gosql.Comment().Table("table_name", "The table comment")
Delete query (support full PG16 SQL specification) examples
Delete with condition
DELETE FROM films WHERE (kind <> ?);

d := gosql.NewDelete().From("films")
d.Where().AddExpression("kind <> ?", "Musical")
Delete all from table
DELETE FROM films;

d := gosql.NewDelete().From("films")
Delete with condition returning all
DELETE FROM tasks WHERE (status = ?) RETURNING *;

d := gosql.NewDelete().From("tasks")
d.Returning().Add("*")
d.Where().AddExpression("status = ?", "DONE")
Delete where sub query
DELETE FROM tasks WHERE (producer_id IN (SELECT id FROM producers WHERE (name = ?)));

sub := gosql.NewSelect()
sub.Columns().Add("id")
sub.From("producers")
sub.Where().AddExpression("name = ?", "foo")
sub.SubQuery = true

d := NewDelete().From("tasks")
d.Where().AddExpression("producer_id IN "+sub.String(), sub.GetArguments()...)
Update query (support full PG16 SQL specification) examples
Update with condition
UPDATE films SET kind = ? WHERE (kind = ?);

u := gosql.NewUpdate().Table("films")
u.Set().Append("kind = ?", "Dramatic")
u.Where().AddExpression("kind = ?", "Drama")
Update complex expression
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE (city = ? AND date = ?);

u := gosql.NewUpdate().Table("weather")
u.Set().Add("temp_lo = temp_lo+1", "temp_hi = temp_lo+15", "prcp = DEFAULT")
u.Where().
    AddExpression("city = ?", "San Francisco").
    AddExpression("date = ?", "2003-07-03")
Update with returning
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE (city = ? AND date = ?) RETURNING temp_lo, temp_hi, prcp;

u := gosql.NewUpdate().Table("weather")
u.Set().Add("temp_lo = temp_lo+1", "temp_hi = temp_lo+15", "prcp = DEFAULT")
u.Returning().Add("temp_lo", "temp_hi", "prcp")
u.Where().
    AddExpression("city = ?", "San Francisco").
    AddExpression("date = ?", "2003-07-03")
Update from
UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE (accounts.name = ? AND employees.id = accounts.sales_person);

u := gosql.NewUpdate().Table("employees").From("accounts")
u.Set().Add("sales_count = sales_count + 1")
u.Where().
    AddExpression("accounts.name = ?", "Acme Corporation").
    AddExpression("employees.id = accounts.sales_person")
Update sub select
UPDATE employees SET sales_count = sales_count + 1 WHERE (id = (SELECT sales_person FROM accounts WHERE (name = ?)));

sub := gosql.NewSelect()
sub.From("accounts")
sub.Columns().Add("sales_person")
sub.Where().AddExpression("name = ?", "Acme Corporation")
sub.SubQuery = true

u := gosql.NewUpdate().Table("employees")
u.Set().Add("sales_count = sales_count + 1")
u.Where().AddExpression("id = "+sub.String(), sub.GetArguments()...)
Insert query (support full PG16 SQL specification) examples
Insert values
INSERT INTO user (name, entity_id, created_at) VALUES (?, ?, ?), (?, ?, ?) RETURNING id, created_at;

i := gosql.NewInsert().Into("user")
i.Columns().Add("name", "entity_id", "created_at")
i.Returning().Add("id", "created_at")
i.Columns().Arg("foo", 10, "2021-01-01T10:10:00Z")
i.Columns().Arg("bar", 20, "2021-01-01T10:10:00Z")
Insert with
WITH dict AS (SELECT * FROM dictionary d JOIN relation r ON r.dictionary_id = d.id WHERE (some = ?)) INSERT INTO user (name, entity_id, created_at) RETURNING id, created_at;

i := gosql.NewInsert().Into("user")
i.Columns().Add("name", "entity_id", "created_at")
i.Returning().Add("id", "created_at")

q := gosql.NewSelect()
q.From("dictionary d")
q.Columns().Add("*")
q.Where().AddExpression("some = ?", 1)
q.Relate("JOIN relation r ON r.dictionary_id = d.id")

i.With().Add("dict", q)
Insert conflict
INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

i := gosql.NewInsert().Into("distributors")
i.Columns().Add("did", "dname")
i.Columns().Arg(5, "Gizmo Transglobal")
i.Columns().Arg(6, "Associated Computing, Inc")
i.Conflict().Object("did").Action("UPDATE").Set().Add("dname = EXCLUDED.dname")
Insert conflict no action
INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING;

i := gosql.NewInsert().Into("distributors")
i.Columns().Add("did", "dname")
i.Columns().Arg(7, "Redline GmbH")
i.Conflict().Object("did").Action("NOTHING")
Insert conflict with condition
INSERT INTO distributors AS d (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')' WHERE (d.zipcode <> '21201');

i := gosql.NewInsert().Into("distributors AS d")
i.Columns().Add("did", "dname")
i.Columns().Arg(8, "Anvil Distribution")
i.Conflict().Object("did").Action("UPDATE").Set().Add("dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'")
i.Conflict().Where().AddExpression("d.zipcode <> '21201'")
Insert on conflict on constraint
INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

i := gosql.NewInsert().Into("distributors")
i.Columns().Add("did", "dname")
i.Columns().Arg(9, "Antwerp Design")
i.Conflict().Constraint("distributors_pkey").Action("NOTHING")
Insert and returning
INSERT INTO distributors (did, dname) VALUES (?, ?) RETURNING did;

i := gosql.NewInsert().Into("distributors")
i.Columns().Add("did", "dname")
i.Columns().Arg(1, "XYZ Widgets")
i.Returning().Add("did")
Select query (partial support PG15 SQL specification) examples
Select from table
SELECT * FROM name

s := NewSelect().From("name")
s.Columns().Add("*")
Select from join using
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d 
    JOIN films f USING (did)

s := NewSelect().From("distributors d").Relate("JOIN films f USING (did)")
s.Columns().Add("f.title", "f.did", "d.name", "f.date_prod", "f.kind")
Select sum group by
SELECT kind, sum(len) AS total FROM films GROUP BY kind

s := NewSelect().From("films").GroupBy("kind")
s.Columns().Add("kind", "sum(len) AS total")
Select group by having
SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours'

s := NewSelect().From("films").GroupBy("kind")
s.Columns().Add("kind", "sum(len) AS total")
s.Having().AddExpression("sum(len) < interval '5 hours'")
Select order
SELECT * FROM distributors ORDER BY name

s := NewSelect().From("distributors").AddOrder("name")
s.Columns().Add("*")
Select union
SELECT distributors.name
    FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
WHERE actors.name LIKE 'W%'

s := NewSelect().From("distributors")
s.Columns().Add("distributors.name")
s.Where().AddExpression("distributors.name LIKE 'W%'")
u := NewSelect().From("actors")
u.Columns().Add("actors.name")
u.Where().AddExpression("actors.name LIKE 'W%'")
Select from unnest
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY

s := NewSelect().From("unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY")
s.Columns().Add("*")
Select from tables
SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;

s := NewSelect().From("manufacturers m", "LATERAL get_product_names(m.id) pname")
s.Columns().Add("m.name AS mname", "pname")
Select union intersect
WITH some AS (
    SELECT * FROM some_table 
    UNION (
        SELECT * FROM some_table_union_1 INTERSECT SELECT * FROM some_table_union_2
     )
) 
SELECT * FROM main_table

m := NewSelect().From("main_table")
m.Columns().Add("*")
q := NewSelect().From("some_table")
q.Columns().Add("*")
u1 := NewSelect().From("some_table_union_1")
u1.Columns().Add("*")
u2 := NewSelect().From("some_table_union_2")
u2.Columns().Add("*")
u1.Intersect(u2)
u1.SubQuery = true
q.Union(u1)

m.With().Add("some", q)
Select union intersect
WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
  )
SELECT distance, employee_name FROM employee_recursive;

employee := NewSelect().From("employee")
employee.Columns().Add("1", "employee_name", "manager_name")
employee.Where().AddExpression("manager_name = ?", "Mary")

reqEmployee := NewSelect().From("employee_recursive er", "employee e")
reqEmployee.Columns().Add("er.distance + 1", "e.employee_name", "e.manager_name")
reqEmployee.Where().AddExpression("er.employee_name = e.manager_name")
employee.Union(reqEmployee)

s := NewSelect().From("employee_recursive")
s.Columns().Add("distance", "employee_name")
s.With().Recursive().Add("employee_recursive(distance, employee_name, manager_name)", employee)
Select left join group order having limit
SELECT t.id, t.name, c.code 
FROM table AS t
LEFT JOIN country AS c ON c.tid = t.id
GROUP BY t.id, t.name, c.code 
ORDER BY t.name
LIMIT 10 OFFSET 30

s := NewSelect().
From("table AS t").
Relate("LEFT JOIN country AS c ON c.tid = t.id").
GroupBy("t.id", "t.name", "c.code").
AddOrder("t.name").
SetPagination(10, 30)
s.Columns().Add("t.id", "t.name", "c.code")
Merge query (full support PG16 SQL specification) examples
Merge update insert
MERGE INTO customer_account ca
USING recent_transactions t ON t.customer_id = ca.customer_id
 WHEN MATCHED THEN
    UPDATE SET balance = balance + transaction_value
 WHEN NOT MATCHED THEN
    INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);

m := gosql.NewMerge().
    Into("customer_account ca").
    Using("recent_transactions t ON t.customer_id = ca.customer_id")
    
    m.When().Update().Add("balance = balance + transaction_value")
    m.When().Insert().Columns("customer_id", "balance").Values().Add("t.customer_id", "t.transaction_value")
Merge update insert using sub query
MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t ON t.customer_id = ca.customer_id
 WHEN MATCHED THEN
    UPDATE SET balance = balance + transaction_value
 WHEN NOT MATCHED THEN
    INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);

sub := NewSelect().From("recent_transactions")
sub.Columns().Add("customer_id", "transaction_value")
sub.SubQuery = true

m := gosql.NewMerge().
    Into("customer_account ca").
    Using(sub.String() + " AS t ON t.customer_id = ca.customer_id")

m.When().Update().Add("balance = balance + transaction_value")
m.When().Insert().Columns("customer_id", "balance").
    Values().Add("t.customer_id", "t.transaction_value")
Merge insert update delete
MERGE INTO wines w
USING wine_stock_changes s ON s.winename = w.winename
 WHEN NOT MATCHED AND s.stock_delta > 0 THEN
    INSERT VALUES(s.winename, s.stock_delta)
 WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
    UPDATE SET stock = w.stock + s.stock_delta
 WHEN MATCHED THEN
    DELETE;

m := gosql.NewMerge().
    Into("wines w").
    Using("wine_stock_changes s ON s.winename = w.winename")

insertWhen := m.When()
insertWhen.Condition().AddExpression("s.stock_delta > 0")
insertWhen.Insert().Values().Add("s.winename", "s.stock_delta")

updateWhen := m.When()
updateWhen.Condition().AddExpression("w.stock + s.stock_delta > 0")
updateWhen.Update().Add("stock = w.stock + s.stock_delta")

m.When().Delete()
Merge update insert with default fields
MERGE INTO station_data_actual sda
USING station_data_new sdn ON sda.station_id = sdn.station_id
 WHEN MATCHED THEN
    UPDATE SET a = sdn.a, b = sdn.b, updated = DEFAULT
 WHEN NOT MATCHED THEN
    INSERT (station_id, a, b) VALUES (sdn.station_id, sdn.a, sdn.b);

m := gosql.NewMerge().
    Into("station_data_actual sda").
    Using("station_data_new sdn ON sda.station_id = sdn.station_id")

m.When().Update().Add("a = sdn.a", "b = sdn.b", "updated = DEFAULT")

insertWhen := m.When().Insert()
insertWhen.Columns("station_id", "a", "b")
insertWhen.Values().Add("sdn.station_id", "sdn.a", "sdn.b")
Alter table query (full support PG16 SQL specification) examples
Add column
ALTER TABLE distributors ADD COLUMN address varchar(30);

alter := gosql.AlterTable("distributors")
alter.Action().Add().Column("address", "varchar(30)")
Add column with default constraint
ALTER TABLE measurements
    ADD COLUMN mtime timestamp with time zone DEFAULT now();

alter := gosql.AlterTable("measurements")
alter.Action().Add().Column("mtime", "timestamp with time zone").Constraint().Default("now()")
Add and alter column with default constraint
ALTER TABLE transactions
    ADD COLUMN status varchar(30) DEFAULT 'old',
    ALTER COLUMN status SET default 'current';

alter := gosql.AlterTable("transactions")
alter.Action().Add().Column("status", "varchar(30)").Constraint().Default("'old'")
alter.Action().AlterColumn("status").Set().Default("'current'")
Drop column restrict
ALTER TABLE distributors DROP COLUMN address RESTRICT;

alter := gosql.AlterTable("distributors")
alter.Action().Drop().Column("address").Restrict()
Change type of columns
ALTER TABLE distributors
    ALTER COLUMN address SET DATA TYPE varchar(80),
    ALTER COLUMN name SET DATA TYPE varchar(100);

alter := gosql.AlterTable("distributors")
alter.Action().AlterColumn("address").Set().DataType("varchar(80)")
alter.Action().AlterColumn("name").Set().DataType("varchar(100)")
Change type of column using
ALTER TABLE foo
    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
    USING
    timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

alter := gosql.AlterTable("foo")
alter.Action().AlterColumn("foo_timestamp").Set().DataType("timestamp with time zone").
    Using("timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'")
Change type of column using with default expression
ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
        USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

alter := gosql.AlterTable("foo")
alter.Action().AlterColumn("foo_timestamp").Drop().Default()
alter.Action().AlterColumn("foo_timestamp").Set().DataType("timestamp with time zone").
    Using("timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'")
alter.Action().AlterColumn("foo_timestamp").Set().Default("now()")
Rename existing column
ALTER TABLE distributors RENAME COLUMN address TO city;

alter := gosql.AlterTable("distributors").RenameColumn("address", "city")
Rename table
ALTER TABLE distributors RENAME TO suppliers;

alter := gosql.AlterTable("distributors").Rename("suppliers")
Rename constraint
ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

alter := gosql.AlterTable("distributors").RenameConstraint("zipchk", "zip_check")
Set column not null
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

alter := gosql.AlterTable("distributors")
alter.Action().AlterColumn("street").Set().NotNull()
Drop column not null
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

alter := gosql.AlterTable("distributors")
alter.Action().AlterColumn("street").Drop().NotNull()
Add constraint
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

alter := gosql.AlterTable("distributors")
alter.Action().Add().TableConstraint().Name("zipchk").Check().AddExpression("char_length(zipcode) = 5")
Add constraint no inherit
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

alter := gosql.AlterTable("distributors")
alter.Action().Add().TableConstraint().Name("zipchk").NoInherit().Check().AddExpression("char_length(zipcode) = 5")
Remove constraint
ALTER TABLE distributors DROP CONSTRAINT zipchk;

alter := gosql.AlterTable("distributors")
alter.Action().Drop().Constraint("zipchk")
Remove constraint only from distributors table
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

alter := gosql.AlterTable("distributors").Only()
alter.Action().Drop().Constraint("zipchk")
Add constraint foreign key
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

alter := gosql.AlterTable("distributors")
alter.Action().Add().TableConstraint().Name("distfk").
    ForeignKey().Column("address").References().RefTable("addresses").Column("address")
Add constraint and validate
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

alter := gosql.AlterTable("distributors")
alter.Action().Add().NotValid().TableConstraint().Name("distfk").
    ForeignKey().Column("address").References().RefTable("addresses").Column("address")

alter = gosql.AlterTable("distributors")
alter.Action().ValidateConstraint("distfk")
Add multicolumn unique constraint
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

alter := gosql.AlterTable("distributors")
alter.Action().Add().TableConstraint().Name("dist_id_zipcode_key").Unique().Column("dist_id", "zipcode")
Add primary key
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

alter := gosql.AlterTable("distributors")
alter.Action().Add().TableConstraint().PrimaryKey().Column("dist_id")
Set tablespace
ALTER TABLE distributors SET TABLESPACE fasttablespace;

alter := gosql.AlterTable("distributors").SetTableSpace("fasttablespace")
Set schema
ALTER TABLE myschema.distributors SET SCHEMA yourschema;

alter := gosql.AlterTable("myschema.distributors").SetSchema("yourschema")
Recreate primary key without blocking updates while the index is rebuilt
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
                         ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

unique := gosql.CreateIndex("distributors", "dist_id").Name("dist_id_temp_idx").Concurrently().Unique()

alter := gosql.AlterTable("distributors")
alter.Action().Drop().Constraint("distributors_pkey")
alter.Action().Add().TableConstraintUsingIndex().Name("distributors_pkey").PrimaryKey().Using("dist_id_temp_idx")
Attach partition
ALTER TABLE measurement
    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

alter := gosql.AlterTable("distributors")
bound := alter.AttachPartition("measurement_y2016m07")
bound.From().Add("'2016-07-01'")
bound.To().Add("'2016-08-01'")
Attach partition to a list-partitioned table
ALTER TABLE cities
    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

alter := gosql.AlterTable("cities")
alter.AttachPartition("cities_ab").In().Add("'a'", "'b'")
Attach partition for values with
ALTER TABLE orders
    ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

alter := gosql.AlterTable("orders")
alter.AttachPartition("orders_p4").With().Add("MODULUS 4", "REMAINDER 3")
Attach partition default
ALTER TABLE cities
    ATTACH PARTITION cities_partdef DEFAULT;

alter := gosql.AlterTable("cities")
alter.AttachDefaultPartition("cities_partdef")
Attach partition default
ALTER TABLE measurement
    DETACH PARTITION measurement_y2015m12;;

alter := gosql.AlterTable("cities")
alter.DetachPartition("measurement_y2015m12")
If you find this project useful or want to support the author, you can send tokens to any of these wallets
  • Bitcoin: bc1qgx5c3n7q26qv0tngculjz0g78u6mzavy2vg3tf
  • Ethereum: 0x62812cb089E0df31347ca32A1610019537bbFe0D
  • Dogecoin: DET7fbNzZftp4sGRrBehfVRoi97RiPKajV

Documentation

Index

Constants

View Source 
const (
	// OwnerCurrentRole role
	OwnerCurrentRole = "CURRENT_ROLE"
	// OwnerCurrentUser user
	OwnerCurrentUser = "CURRENT_USER"
	// OwnerCurrentSession session
	OwnerCurrentSession = "SESSION_USER"
)
View Source 
const (
	ConditionOperatorAnd = "AND"
	ConditionOperatorOr  = "OR"
	ConditionOperatorXor = "XOR"
)
View Source 
const (
	// ConflictActionNothing On conflict action do nothing
	ConflictActionNothing = "NOTHING"
	// ConflictActionUpdate On conflict action do update nothing
	ConflictActionUpdate = "UPDATE"
)
View Source 
const (
	// ReferencesMatchFull MATCH FULL
	ReferencesMatchFull = "FULL"
	// ReferencesMatchPartial MATCH PARTIAL
	ReferencesMatchPartial = "PARTIAL"
	// ReferencesMatchSimple MATCH SIMPLE
	ReferencesMatchSimple = "SIMPLE"

	// CheckNoInherit NO INHERIT
	CheckNoInherit = "NO INHERIT"

	// GeneratedStored STORED
	GeneratedStored = "STORED"
	// GeneratedAlways ALWAYS
	GeneratedAlways = "ALWAYS"
	// GeneratedByDefault BY DEFAULT
	GeneratedByDefault = "BY DEFAULT"

	// Deferrable DEFERRABLE
	Deferrable = "DEFERRABLE"
	// NotDeferrable NOT DEFERRABLE
	NotDeferrable = "NOT DEFERRABLE"

	// InitiallyDeferred DEFERRED
	InitiallyDeferred = "DEFERRED"
	// InitiallyImmediate IMMEDIATE
	InitiallyImmediate = "IMMEDIATE"

	// ActionNoAction NO ACTION
	ActionNoAction = "NO ACTION"
	// ActionCascade CASCADE
	ActionCascade = "CASCADE"
	// ActionRestrict RESTRICT
	ActionRestrict = "RESTRICT"
	// ActionSetNull SET NULL
	ActionSetNull = "SET NULL"
	// ActionSetDefault SET DEFAULT
	ActionSetDefault = "SET DEFAULT"

	// LikeIncluding INCLUDING
	LikeIncluding = "INCLUDING"
	// LikeExcluding EXCLUDING
	LikeExcluding = "EXCLUDING"
	// LikeComments COMMENTS
	LikeComments = "COMMENTS"
	// LikeCompression COMPRESSION
	LikeCompression = "COMPRESSION"
	// LikeConstraints CONSTRAINTS
	LikeConstraints = "CONSTRAINTS"
	// LikeDefaults DEFAULTS
	LikeDefaults = "DEFAULTS"
	// LikeGenerated GENERATED
	LikeGenerated = "GENERATED"
	// LikeIdentity IDENTITY
	LikeIdentity = "IDENTITY"
	// LikeIndexes INDEXES
	LikeIndexes = "INDEXES"
	// LikeStatistics STATISTICS
	LikeStatistics = "STATISTICS"
	// LikeStorage STORAGE
	LikeStorage = "STORAGE"
	// LikeAll ALL
	LikeAll = "ALL"

	// WithWithoutOIDS WITHOUT OIDS
	WithWithoutOIDS = "WITHOUT OIDS"

	// PartitionByRange RANGE
	PartitionByRange = "RANGE"
	// PartitionByList LIST
	PartitionByList = "LIST"
	// PartitionByHash HASH
	PartitionByHash = "HASH"

	// PartitionBoundFromMin MINVALUE
	PartitionBoundFromMin = "MINVALUE"
	// PartitionBoundFromMax MAXVALUE
	PartitionBoundFromMax = "MAXVALUE"
	// PartitionBoundWithModulus MODULUS
	PartitionBoundWithModulus = "MODULUS"
	// PartitionBoundWithRemainder REMAINDER
	PartitionBoundWithRemainder = "REMAINDER"

	// PartitionOfWithOptions WITH OPTIONS
	PartitionOfWithOptions = "WITH OPTIONS"
)
View Source 
const EnumDelimiter = "#"

EnumDelimiter for join strings

Variables

This section is empty.

Functions

func NewConflict added in v0.3.4 

func NewConflict() *conflict

NewConflict conflict constructor

func NewDetailedExpression added in v0.5.0 

func NewDetailedExpression() *detailedExpression

NewDetailedExpression init detailed expression

func NewExpression added in v0.3.1 

func NewExpression() *expression

NewExpression init expression

func PGSQL added in v0.7.1 

func PGSQL(isql ISQL) (query string, params []any, returning []any)

PGSQL Transform to postgres params query

func PostgresQueryParamHook added in v0.7.0 

func PostgresQueryParamHook(query string) string

PostgresQueryParamHook Position argument

Types

type Alter added in v0.8.0 

type Alter struct {
	// contains filtered or unexported fields
}

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] 

action [, ... ]

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] 

RENAME [ COLUMN ] column_name TO new_column_name

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] 

RENAME CONSTRAINT constraint_name TO new_constraint_name

ALTER TABLE [ IF EXISTS ] name 

RENAME TO new_name

ALTER TABLE [ IF EXISTS ] name 

SET SCHEMA new_schema

ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] 

SET TABLESPACE new_tablespace [ NOWAIT ]

ALTER TABLE [ IF EXISTS ] name 

ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }

ALTER TABLE [ IF EXISTS ] name 

DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) | FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) 

TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |

WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

and column_constraint is:

[ CONSTRAINT constraint_name ] { NOT NULL | 

NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | 

UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
  [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint_using_index is: 

[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

func AlterTable added in v0.8.0 

func AlterTable(args ...string) *Alter

AlterTable table constructor

func (*Alter) Action added in v0.8.0 

func (a *Alter) Action() *alterTableAction

Action get action

func (*Alter) AllInTableSpace added in v0.8.0 

func (a *Alter) AllInTableSpace(name string) *Alter

AllInTableSpace name

func (*Alter) AttachDefaultPartition added in v0.8.0 

func (a *Alter) AttachDefaultPartition(name string) *Alter

AttachDefaultPartition name

func (*Alter) AttachPartition added in v0.8.0 

func (a *Alter) AttachPartition(name string) *partitionBound

AttachPartition name

func (*Alter) DetachPartition added in v0.8.0 

func (a *Alter) DetachPartition(name string) *Alter

DetachPartition name

func (*Alter) DetachPartitionConcurrently added in v0.8.0 

func (a *Alter) DetachPartitionConcurrently(name string) *Alter

DetachPartitionConcurrently name

func (*Alter) DetachPartitionFinalize added in v0.8.0 

func (a *Alter) DetachPartitionFinalize(name string) *Alter

DetachPartitionFinalize name

func (*Alter) IfExists added in v0.8.0 

func (a *Alter) IfExists() *Alter

IfExists set if exists

func (*Alter) IsEmpty added in v0.8.0 

func (a *Alter) IsEmpty() bool

IsEmpty check if empty

func (*Alter) Name added in v0.8.0 

func (a *Alter) Name(name string) *Alter

Name set name

func (*Alter) Only added in v0.8.0 

func (a *Alter) Only() *Alter

Only set

func (*Alter) OwnedBy added in v0.8.0 

func (a *Alter) OwnedBy(role ...string) *Alter

OwnedBy role

func (*Alter) Rename added in v0.8.0 

func (a *Alter) Rename(name string) *Alter

Rename table

func (*Alter) RenameColumn added in v0.8.0 

func (a *Alter) RenameColumn(old, new string) *Alter

RenameColumn rename column

func (*Alter) RenameConstraint added in v0.8.0 

func (a *Alter) RenameConstraint(old, new string) *Alter

RenameConstraint rename constraint

func (*Alter) SQL added in v0.8.0 

func (a *Alter) SQL() (query string, params []any, returning []any)

SQL common sql interface

func (*Alter) SetSchema added in v0.8.0 

func (a *Alter) SetSchema(name string) *Alter

SetSchema table

func (*Alter) SetTableSpace added in v0.8.0 

func (a *Alter) SetTableSpace(name string) *Alter

SetTableSpace name

func (*Alter) SetTableSpaceNoWait added in v0.8.0 

func (a *Alter) SetTableSpaceNoWait(name string) *Alter

SetTableSpaceNoWait name

func (*Alter) String added in v0.8.0 

func (a *Alter) String() string

String render alter table query

type Comment added in v0.5.0 

type Comment struct {
	// contains filtered or unexported fields
}

func NewComment added in v0.7.0 

func NewComment() *Comment

NewComment init comment

func (*Comment) Column added in v0.7.0 

func (c *Comment) Column(column string, comment string) *Comment

Column comment column

func (*Comment) IsEmpty added in v0.7.0 

func (c *Comment) IsEmpty() bool

IsEmpty check if empty

func (*Comment) SQL added in v0.7.0 

func (c *Comment) SQL() (query string, params []any, returning []any)

SQL common sql interface

func (*Comment) String added in v0.7.0 

func (c *Comment) String() string

String render comment query

func (*Comment) Table added in v0.7.0 

func (c *Comment) Table(table string, comment string) *Comment

Table comment table

type Condition 

type Condition struct {
	// contains filtered or unexported fields
}

Condition type

func NewSqlCondition 

func NewSqlCondition(operator string) *Condition

NewSqlCondition init condition

func (*Condition) AddArgument 

func (c *Condition) AddArgument(values ...interface{}) *Condition

AddArgument add argument

func (*Condition) AddExpression 

func (c *Condition) AddExpression(expression string, values ...interface{}) *Condition

AddExpression add expression

func (*Condition) GetArguments 

func (c *Condition) GetArguments() []interface{}

GetArguments get arguments

func (*Condition) IsEmpty 

func (c *Condition) IsEmpty() bool

IsEmpty check if condition is empty

func (*Condition) Merge 

func (c *Condition) Merge(operator string, conditions ...*Condition) *Condition

Merge with conditions

func (*Condition) Replace added in v0.4.1 

func (c *Condition) Replace(cond *Condition) *Condition

Replace current condition

func (*Condition) String 

func (c *Condition) String() string

Get string of conditions

type Delete 

type Delete struct {
	// contains filtered or unexported fields
}

Delete query

func NewDelete 

func NewDelete() *Delete

func (*Delete) From 

func (d *Delete) From(from string) *Delete

From Set from value

func (*Delete) GetGetArguments added in v0.4.0 

func (d *Delete) GetGetArguments() []any

GetGetArguments get all values

func (*Delete) IsEmpty 

func (d *Delete) IsEmpty() bool

IsEmpty check if query is empty

func (*Delete) ResetFrom 

func (d *Delete) ResetFrom() *Delete

ResetFrom Set from empty string

func (*Delete) ResetUsing 

func (d *Delete) ResetUsing() *Delete

ResetUsing clear using

func (*Delete) Returning added in v0.4.0 

func (d *Delete) Returning() *expression

Returning Append returning expression

func (*Delete) SQL added in v0.3.0 

func (d *Delete) SQL() (query string, params []any, returning []any)

SQL Get sql query

func (*Delete) String 

func (d *Delete) String() string

String return result query

func (*Delete) Using 

func (d *Delete) Using(using ...string) *Delete

Using add using

func (*Delete) Where added in v0.4.0 

func (d *Delete) Where() *Condition

Where set condition

type ISQL added in v0.3.0 

type ISQL interface {
	// SQL Get query as string with all params
	SQL() (query string, params []any, returning []any)
}

ISQL each query should implement the interface

type Index added in v0.7.0 

type Index struct {
	// contains filtered or unexported fields
}

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ] 

( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ NULLS [ NOT ] DISTINCT ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]

func CreateIndex added in v0.5.1 

func CreateIndex(arg ...string) *Index

CreateIndex new index

func (*Index) AutoName added in v0.7.0 

func (i *Index) AutoName() *Index

AutoName generate name on render

func (*Index) Concurrently added in v0.7.0 

func (i *Index) Concurrently() *Index

Concurrently create index

func (*Index) Expression added in v0.7.0 

func (i *Index) Expression() *expression

Expression index expression

func (*Index) IfNotExists added in v0.7.0 

func (i *Index) IfNotExists() *Index

IfNotExists set if not exists

func (*Index) Include added in v0.7.0 

func (i *Index) Include(column ...string) *Index

Include add include columns

func (*Index) IsEmpty added in v0.7.0 

func (i *Index) IsEmpty() bool

IsEmpty check if empty

func (*Index) Name added in v0.7.0 

func (i *Index) Name(name string) *Index

Name set index name

func (*Index) NullsNotDistinct added in v0.7.0 

func (i *Index) NullsNotDistinct() *Index

NullsNotDistinct set null not distinct

func (*Index) SQL added in v0.7.0 

func (i *Index) SQL() (query string, params []any, returning []any)

SQL common sql interface

func (*Index) String added in v0.7.0 

func (i *Index) String() string

String render index query

func (*Index) Table added in v0.7.0 

func (i *Index) Table(name string) *Index

Table set table name

func (*Index) TableSpace added in v0.7.0 

func (i *Index) TableSpace(space string) *Index

TableSpace set table space

func (*Index) Unique added in v0.7.0 

func (i *Index) Unique() *Index

Unique set unique

func (*Index) Using added in v0.7.0 

func (i *Index) Using(using string) *Index

Using method

func (*Index) Where added in v0.7.0 

func (i *Index) Where() *Condition

Where get where condition

func (*Index) With added in v0.7.0 

func (i *Index) With(param ...string) *Index

With add with params

type Insert 

type Insert struct {
	// contains filtered or unexported fields
}

Insert query builder

func NewInsert 

func NewInsert() *Insert

NewInsert new insert query builder

func (*Insert) Columns 

func (i *Insert) Columns() *expression

Columns get columns for insert

func (*Insert) Conflict 

func (i *Insert) Conflict() *conflict

Conflict get conflict expression

func (*Insert) From 

func (i *Insert) From(from ...string) *Insert

From insert from

func (*Insert) GetArguments added in v0.3.0 

func (i *Insert) GetArguments() []any

GetArguments get all arguments

func (*Insert) Into 

func (i *Insert) Into(into string) *Insert

Into Set into value

func (*Insert) IsEmpty added in v0.1.1 

func (i *Insert) IsEmpty() bool

IsEmpty return true if all parts not filed

func (*Insert) ResetConflict added in v0.3.4 

func (i *Insert) ResetConflict() *Insert

ResetConflict reset conflict expression

func (*Insert) ResetFrom 

func (i *Insert) ResetFrom() *Insert

ResetFrom clear from

func (*Insert) ResetInto 

func (i *Insert) ResetInto() *Insert

ResetInto Set into empty string

func (*Insert) Returning added in v0.4.0 

func (i *Insert) Returning() *expression

Returning Get returning expression

func (*Insert) SQL 

func (i *Insert) SQL() (query string, params []any, returning []any)

SQL Get sql query

func (*Insert) SetConflict added in v0.3.4 

func (i *Insert) SetConflict(conflict conflict) *Insert

SetConflict set conflict

func (*Insert) String 

func (i *Insert) String() string

Get sql insert query

func (*Insert) With 

func (i *Insert) With() *with

With Get with query

type Merge added in v0.7.0 

type Merge struct {
	// contains filtered or unexported fields
}

[ WITH with_query [, ...] ] MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ] USING data_source ON join_condition when_clause [...]

where data_source is:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } | 

WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

INSERT [( column_name [, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } | 

( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE

func NewMerge added in v0.7.0 

func NewMerge() *Merge

NewMerge merge constructor

func (*Merge) GetArguments added in v0.7.0 

func (m *Merge) GetArguments() []any

GetArguments Get merge arguments

func (*Merge) Into added in v0.7.0 

func (m *Merge) Into(target string) *Merge

Into target

func (*Merge) IntoOnly added in v0.7.0 

func (m *Merge) IntoOnly(target string) *Merge

IntoOnly target

func (*Merge) IsEmpty added in v0.7.0 

func (m *Merge) IsEmpty() bool

IsEmpty if empty

func (*Merge) SQL added in v0.7.0 

func (m *Merge) SQL() (query string, params []any, returning []any)

SQL Get sql query

func (*Merge) String added in v0.7.0 

func (m *Merge) String() string

String build query

func (*Merge) Using added in v0.7.0 

func (m *Merge) Using(datasource string) *Merge

Using datasource

func (*Merge) UsingOnly added in v0.7.0 

func (m *Merge) UsingOnly(datasource string) *Merge

UsingOnly datasource

func (*Merge) When added in v0.7.0 

func (m *Merge) When() *when

When clause

func (*Merge) With added in v0.7.0 

func (m *Merge) With() *with

With queries

type PeriodFilter added in v0.7.2 

type PeriodFilter struct {
	// begins from
	Start *time.Time `json:"start"`
	// ended when
	End *time.Time `json:"end"`
}

PeriodFilter filter by datetime columns

func (*PeriodFilter) FieldCondition added in v0.7.2 

func (p *PeriodFilter) FieldCondition(field string) *Condition

FieldCondition получить условие для фильтрации

func (*PeriodFilter) IsEmpty added in v0.7.2 

func (p *PeriodFilter) IsEmpty() bool

IsEmpty if filter is empty

type SQList added in v0.6.0 

type SQList []ISQL

SQList Collection of SQL element

func (SQList) Join added in v0.6.0 

func (s SQList) Join() (query string, params []any, returning []any)

Join Return query string joined in one string

type SearchString added in v0.7.2 

type SearchString string

SearchString search by text columns

func (SearchString) PrepareLikeValue added in v0.7.2 

func (s SearchString) PrepareLikeValue(column string) *Condition

PrepareLikeValue prepare search like condition

type Select 

type Select struct {

	// is subquery. Put query in bracers
	SubQuery bool
	// contains filtered or unexported fields
}

Select Query Builder struct Not a thread safety

func NewSelect 

func NewSelect() *Select

NewSelect Query Builder

func (*Select) AddOrder 

func (q *Select) AddOrder(expression ...string) *Select

Append Order

func (*Select) Columns 

func (q *Select) Columns() *expression

Append column

func (*Select) Except 

func (q *Select) Except(s *Select) *Select

Except query

func (*Select) From 

func (q *Select) From(table ...string) *Select

Append from

func (*Select) GetArguments 

func (q *Select) GetArguments() []interface{}

Get arguments

func (*Select) GroupBy 

func (q *Select) GroupBy(fields ...string) *Select

Append Group

func (*Select) Having 

func (q *Select) Having() *Condition

Where conditions

func (*Select) Intersect 

func (q *Select) Intersect(s *Select) *Select

Intersect query

func (*Select) Relate 

func (q *Select) Relate(relation ...string) *Select

Append join

func (*Select) ResetExcept 

func (q *Select) ResetExcept() *Select

ResetExcept reset except

func (*Select) ResetFrom 

func (q *Select) ResetFrom() *Select

Reset column

func (*Select) ResetGroupBy 

func (q *Select) ResetGroupBy() *Select

Reset Group

func (*Select) ResetIntersect 

func (q *Select) ResetIntersect() *Select

ResetIntersect reset intersect

func (*Select) ResetOrder 

func (q *Select) ResetOrder() *Select

Reset Order

func (*Select) ResetRelations 

func (q *Select) ResetRelations() *Select

Reset join

func (*Select) ResetUnion 

func (q *Select) ResetUnion() *Select

ResetUnion reset union

func (*Select) SQL added in v0.3.0 

func (q *Select) SQL() (query string, params []any, returning []any)

SQL Get sql query

func (*Select) SetPagination 

func (q *Select) SetPagination(limit int, offset int) *Select

Set pagination

func (*Select) String 

func (q *Select) String() string

Make SQL query

func (*Select) Union 

func (q *Select) Union(s *Select) *Select

Union add union

func (*Select) Where 

func (q *Select) Where() *Condition

Where conditions

func (*Select) With 

func (q *Select) With() *with

With get with queries

type Sorting added in v0.7.2 

type Sorting []string

Sorting fields Example: ['createdAt:desc', 'name', 'qty:asc']

func (Sorting) Allowed added in v0.7.2 

func (s Sorting) Allowed(items map[string]string) []string

Allowed return all sorting columns according to allowed sort map

func (Sorting) Contains added in v0.9.1 

func (s Sorting) Contains(field string) (contained bool, direction *bool)

Contains check if sorting contains sort field contained == true if sorting has sorted field direction == nil if no sort direction provided direction == true if provided sort direction is ascending direction == false if provided sort direction is descending

type Storage added in v0.8.0 

type Storage string
const (
	// StoragePlain PLAIN
	StoragePlain Storage = "PLAIN"
	// StorageExternal EXTERNAL
	StorageExternal Storage = "EXTERNAL"
	// StorageExtended EXTENDED
	StorageExtended Storage = "EXTENDED"
	// StorageMain MAIN
	StorageMain Storage = "MAIN"
	// StorageDefault DEFAULT
	StorageDefault Storage = "DEFAULT"
)

type Table added in v0.5.0 

type Table struct {
	// contains filtered or unexported fields
}

Table create table query builder CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ 

{ column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
  | table_constraint
  | LIKE source_table [ like_option ... ] }
  [, ... ]

] ) [ INHERITS ( parent_table [, ... ] ) ] [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 

  OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
  | table_constraint }
  [, ... ]

) ] [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 

  PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
  | table_constraint }
  [, ... ]

) ] { FOR VALUES partition_bound_spec | DEFAULT } [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ]

func CreateTable added in v0.5.0 

func CreateTable(name string) *Table

CreateTable init table

func (*Table) AddColumn added in v0.5.0 

func (t *Table) AddColumn(name string) *column

AddColumn add column

func (*Table) AddConstraint added in v0.5.0 

func (t *Table) AddConstraint() *constraintTable

AddConstraint add constraint

func (*Table) AddForeignKey added in v0.5.0 

func (t *Table) AddForeignKey(target string, columns ...string) *foreignKey

AddForeignKey add foreign key

func (*Table) Definitions added in v0.6.1 

func (t *Table) Definitions() *columnDefinitions

Definitions return column definitions

func (*Table) GetName added in v0.5.0 

func (t *Table) GetName() string

GetName get name of table

func (*Table) IfNotExists added in v0.5.0 

func (t *Table) IfNotExists() *Table

IfNotExists Set to true

func (*Table) Inherits added in v0.5.0 

func (t *Table) Inherits() *expression

Inherits inherit form tables

func (*Table) IsEmpty added in v0.5.0 

func (t *Table) IsEmpty() bool

IsEmpty check if table is empty

func (*Table) Name added in v0.6.0 

func (t *Table) Name(name string) *Table

Name Set name

func (*Table) OfPartition added in v0.5.0 

func (t *Table) OfPartition() *ofPartition

OfPartition get of partition definition

func (*Table) OfType added in v0.5.0 

func (t *Table) OfType() *ofType

OfType get of type definition

func (*Table) OnCommit added in v0.6.0 

func (t *Table) OnCommit(onCommit string) *Table

OnCommit set onCommit

func (*Table) Partition added in v0.5.0 

func (t *Table) Partition() *partitionTable

Partition expression

func (*Table) SQL added in v0.5.0 

func (t *Table) SQL() (query string, params []any, returning []any)

SQL Render query

func (*Table) Scope added in v0.6.0 

func (t *Table) Scope(scope string) *Table

Scope set scope

func (*Table) String added in v0.5.0 

func (t *Table) String() string

String render table

func (*Table) TableSpace added in v0.6.0 

func (t *Table) TableSpace(space string) *Table

TableSpace set table space

func (*Table) Temp added in v0.5.0 

func (t *Table) Temp() *Table

Temp Set temp to true

func (*Table) UnLogged added in v0.5.0 

func (t *Table) UnLogged() *Table

UnLogged Set to true

func (*Table) Using added in v0.6.0 

func (t *Table) Using(using string) *Table

Using set using

func (*Table) With added in v0.5.0 

func (t *Table) With(expr ...string) *detailedExpression

With expression

func (*Table) WithOutOIDS added in v0.5.0 

func (t *Table) WithOutOIDS() *detailedExpression

WithOutOIDS expression

type TableModeler added in v0.6.1 

type TableModeler []TableModifier

TableModeler list of TableModifier

func (TableModeler) Add added in v0.6.1 

func (t TableModeler) Add(m ...TableModifier) TableModeler

Add define next table population method

func (TableModeler) Prepare added in v0.6.1 

func (t TableModeler) Prepare(tb *Table)

Prepare table

type TableModifier added in v0.6.1 

type TableModifier func(t *Table)

TableModifier func to modify table

type Update 

type Update struct {
	// contains filtered or unexported fields
}

Update query builder

func NewUpdate 

func NewUpdate() *Update

NewUpdate Update Query Builder

func (*Update) From 

func (u *Update) From(from ...string) *Update

From update

func (*Update) GetArguments added in v0.4.3 

func (u *Update) GetArguments() []any

GetArguments get all values

func (*Update) IsEmpty 

func (u *Update) IsEmpty() bool

IsEmpty check if query is empty

func (*Update) ResetFrom 

func (u *Update) ResetFrom() *Update

ResetFrom clear from

func (*Update) ResetTable 

func (u *Update) ResetTable() *Update

ResetTable reset table

func (*Update) Returning added in v0.4.0 

func (u *Update) Returning() *expression

Returning get returning expression

func (*Update) SQL added in v0.3.0 

func (u *Update) SQL() (query string, params []any, returning []any)

SQL Get sql query

func (*Update) Set 

func (u *Update) Set() *expression

Set get set

func (*Update) String 

func (u *Update) String() string

String return result query

func (*Update) Table 

func (u *Update) Table(table string) *Update

Table Set table

func (*Update) Where added in v0.4.0 

func (u *Update) Where() *Condition

Where set condition

func (*Update) With 

func (u *Update) With() *with

With Append with query

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.