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

Name		Name	Last commit message	Last commit date
Latest commit History 54 Commits
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
alter_table.go		alter_table.go
alter_table_action.go		alter_table_action.go
alter_table_action_add.go		alter_table_action_add.go
alter_table_action_drop.go		alter_table_action_drop.go
alter_table_action_drop_test.go		alter_table_action_drop_test.go
alter_table_action_set.go		alter_table_action_set.go
alter_table_action_set_test.go		alter_table_action_set_test.go
alter_table_test.go		alter_table_test.go
column_test.go		column_test.go
comment.go		comment.go
comment_test.go		comment_test.go
condition.go		condition.go
condition_test.go		condition_test.go
conflict.go		conflict.go
conflict_test.go		conflict_test.go
delete.go		delete.go
delete_test.go		delete_test.go
detailed_expression.go		detailed_expression.go
detailed_expression_test.go		detailed_expression_test.go
dialect.go		dialect.go
dialect_test.go		dialect_test.go
expression.go		expression.go
expression_test.go		expression_test.go
go.mod		go.mod
go.sum		go.sum
helper.go		helper.go
helper_test.go		helper_test.go
index.go		index.go
index_test.go		index_test.go
insert.go		insert.go
insert_test.go		insert_test.go
merge.go		merge.go
merge_test.go		merge_test.go
ordered_expression.go		ordered_expression.go
select.go		select.go
select_test.go		select_test.go
sql.go		sql.go
table.go		table.go
table_column.go		table_column.go
table_column_constraint.go		table_column_constraint.go
table_column_definition.go		table_column_definition.go
table_column_index.go		table_column_index.go
table_column_like.go		table_column_like.go
table_column_references.go		table_column_references.go
table_constraint.go		table_constraint.go
table_constraint_using_index.go		table_constraint_using_index.go
table_exclude.go		table_exclude.go
table_exclude_element.go		table_exclude_element.go
table_foreign_key.go		table_foreign_key.go
table_index_parameters.go		table_index_parameters.go
table_modeler.go		table_modeler.go
table_of_definition.go		table_of_definition.go
table_of_partition.go		table_of_partition.go
table_partition.go		table_partition.go
table_test.go		table_test.go
update.go		update.go
update_test.go		update_test.go
with.go		with.go
with_test.go		with_test.go

License

dimonrus/gosql

Folders and files

Latest commit

History

Repository files navigation

gosql

Postgres SQL builder

Create table (support full PG16 SQL specification) examples

Table with named primary key constraint

Table with unique named constraint

Table with primary key constraint

Table with primary key in column definition

Table with named constraint not null

Table with unique column

Table with unique constraint with storage parameter

Table with name constraint primary key on multiple column

Table with check constraint

Table with default values in column definition

Table with tablespace

Table with options and default constraint

Table with excluding definition

Table with named check constraint with multiple condition

Table with partition by range and clause

Table with partition by hash

Table with partition for values

Table with partition for values with constant MINVALUE

Table with partition for values by range

Table with partition for values from to

Table with default partition

Table with primary key generated by default

Create Index (support full PG16 SQL specification) examples

Create simple index

Create unique index

Create index with storage param

Create index with expression

Create index with collate

Create index nulls first

Create index with using

Create index concurrently

Comment examples

Comment column

Comment table

Delete query (support full PG16 SQL specification) examples

Delete with condition

Delete all from table

Delete with condition returning all

Delete where sub query

Update query (support full PG16 SQL specification) examples

Update with condition

Update complex expression

Update with returning

Update from

Update sub select

Insert query (support full PG16 SQL specification) examples

Insert values

Insert with

Insert conflict

Insert conflict no action

Insert conflict with condition

Insert on conflict on constraint

Insert and returning

Select query (partial support PG15 SQL specification) examples

Select from table

Select from join using

Select sum group by

Select group by having

Select order

Select union

Select from unnest

Select from tables

Select union intersect

Select union intersect

Select left join group order having limit

Merge query (full support PG16 SQL specification) examples

Merge update insert

Merge update insert using sub query

Merge insert update delete

Merge update insert with default fields

Alter table query (full support PG16 SQL specification) examples

Packages