How to Use a Pg Sequence on a Per Record Label

postgresql company id based sequence

You could just embed a counter in your companies table:

CREATE TABLE companies (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    product_id  INT DEFAULT 0
);

CREATE TABLE products (
    company     INT REFERENCES companies(id),
    product_id  INT,
    PRIMARY KEY (company, product_id),

    name        TEXT
);

INSERT INTO companies (id, name) VALUES (1, 'Acme Corporation');
INSERT INTO companies (id, name) VALUES (2, 'Umbrella Corporation');

Then, use UPDATE ... RETURNING to get the next product ID for a given company:

> INSERT INTO products VALUES (1, (UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id), 'Anvil');
ERROR:  syntax error at or near "companies"
LINE 1: INSERT INTO products VALUES (1, (UPDATE companies SET produc...
                                            ^

Oh noes! It seems you can't (as of PostgreSQL 9.1devel) use UPDATE ... RETURNING as a subquery.

The good news is, it's not a problem! Just create a stored procedure that does the increment/return part:

CREATE FUNCTION next_product_id(company INT) RETURNS INT
AS $$
    UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id
$$ LANGUAGE 'sql';

Now insertion is a piece of cake:

INSERT INTO products VALUES (1, next_product_id(1), 'Anvil');
INSERT INTO products VALUES (1, next_product_id(1), 'Dynamite');
INSERT INTO products VALUES (2, next_product_id(2), 'Umbrella');
INSERT INTO products VALUES (1, next_product_id(1), 'Explosive tennis balls');

Be sure to use the same company ID in both the product value and the argument to next_product_id(company INT).

Custom progressive sequence (per year) with a column as prefix

In the end I found a solution by using multiple sequences (one per year), created dynamically when entering the record.
A trigger, before the insertion invoke a procedure that creates the sequence (if it does not exist) and assigns the value to the seq column (if not assigned).

WORKFLOW

record insertion
sequence creation 'tab_ {year} _seq_id' if it does not exist
if the column seq is empty the value nextval is assigned (tab_ {year} _seq_id)
test insertions and deletions to verify that the column is populated in the correct way

TABLE STRUCTURE

CREATE TABLE tab (
    id serial not null constraint tab_pkey primary key,
    year varchar(4) not null,
    seq  varchar(20)
);

FUNCTION

CREATE FUNCTION tab_sequence_trigger_function() RETURNS trigger AS $$
  BEGIN
    IF NEW.seq IS NULL OR NEW.seq = '''' THEN
      EXECUTE ('CREATE SEQUENCE IF NOT EXISTS tab_' || NEW.year || '_id_seq AS INTEGER');
      NEW.seq = NEW.year || '_' || nextval('tab_' || NEW.year || '_id_seq');
    END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

TRIGGER

CREATE TRIGGER tab_sequence_trigger
  BEFORE INSERT ON tab
  FOR EACH ROW
EXECUTE PROCEDURE tab_sequence_trigger_function();

TEST

INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
DELETE FROM tab WHERE id=5;
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2020);
INSERT INTO tab (year) VALUES (2020);
INSERT INTO tab (year) VALUES (2021);
DELETE FROM tab WHERE id=8;
DELETE FROM tab WHERE id=9;
INSERT INTO tab (year) VALUES (2021);
INSERT INTO tab (year) VALUES (2020);

RESULT

SELECT * FROM tab;

----------------------
| id | year |  seq   |
----------------------
|  1 | 2019 | 2019_1 |
----------------------
|  2 | 2019 | 2019_2 |
----------------------
|  3 | 2019 | 2019_3 |
----------------------
|  4 | 2019 | 2019_4 |
----------------------
|  6 | 2019 | 2019_6 |
----------------------
|  7 | 2019 | 2019_7 |
----------------------
| 10 | 2021 | 2021_3 |
----------------------
| 11 | 2021 | 2021_4 |
----------------------
| 12 | 2020 | 2020_3 |
----------------------

How to invoke sequence while inserting new record into postgresql table?

You got it almost. You don't need the SELECT in there:

insert into biz_term(
  biz_term_id, 
  biz_term_name, 
) 
values(
 nextval('idsequence'),
 'temp'
);

Any reasons you did not specify the biz_term_id as serial (or bigserial) which handles that automatically for you?

In-order sequence generation

No. Since there is no natural order of rows in a database table, all you have to work with is the values in your table.

Well, there are the Postgres specific system columns cmin and ctid you could abuse to some degree.

The tuple ID (ctid) contains the file block number and position in the block for the row. So this represents the current physical ordering on disk. Later additions will have a bigger ctid, normally. Your SELECT statement could look like this

SELECT *, ctid   -- save ctid from last row in last_ctid
FROM   tbl
WHERE  ctid > last_ctid
ORDER  BY ctid

ctid has the data type tid. Example: '(0,9)'::tid

However it is not stable as long-term identifier, since VACUUM or any concurrent UPDATE or some other operations can change the physical location of a tuple at any time. For the duration of a transaction it is stable, though. And if you are just inserting and nothing else, it should work locally for your purpose.

I would add a timestamp column with default now() in addition to the serial column ...

I would also let a column default populate your id column (a serial or IDENTITY column). That retrieves the number from the sequence at a later stage than explicitly fetching and then inserting it, thereby minimizing (but not eliminating) the window for a race condition - the chance that a lower id would be inserted at a later time. Detailed instructions:

Auto increment table column

How do I create custom sequence in PostgreSQL based on date of row creation?

You can set your manually created sequence to a specific value using the EXTRACT() function:

setval('my_sequence',
  (EXTRACT(YEAR FROM now())::integer * 1000000) +
  (EXTRACT(MONTH FROM now())::integer * 10000)
);

The next order entered will take the next value in the sequence, i.e. YYYYMM0001 etc.

The trick is when to update the sequence value. You could do it the hard way inside PG and write a BEFORE INSERT trigger on your orders table that checks if this is the first record in a new month:

CREATE FUNCTION before_insert_order() RETURNS trigger AS $$
DECLARE
  base_val  integer;
BEGIN
  -- base_val is the minimal value of the sequence for the current month: YYYYMM0000
  base_val := (EXTRACT(YEAR FROM now())::integer * 1000000) +
              (EXTRACT(MONTH FROM now())::integer * 10000);

  -- So if the sequence is less, then update it
  IF (currval('my_sequence') < base_val)
    setval('my_sequence', base_val);
  END IF;

  -- Now assign the order id and continue with the insert
  NEW.id := nextval('my_sequence');
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_bi_order
  BEFORE INSERT ON order_table
  FOR EACH ROW EXECUTE PROCEDURE before_insert_order();

Why is this the hard way? Because you check the value of the sequence on every insert. If you have only a few inserts per day and your system is not very busy, this is a viable approach.

If you cannot spare all those CPU cycles you could schedule a cron job to run at 00:00:01 of every first day of the month to execute a PG function via psql to update the sequence and then just use the sequence as a default value for new order records (so no trigger needed).

SQL selecting first record per group

GROUP BY u.d (without also listing u1, u2, u3) would only work if u.d was the PRIMARY KEY (which it is not, and also wouldn't make sense in your scenario). See:

Is it possible to have an SQL query that uses AGG functions in this way?

I suggest DISTINCT ON in a subquery on UTable instead:

SELECT o.d, u.u1, u.u2, u.u3, o.n
FROM  (
   SELECT DISTINCT ON (u.d)
          u.d, u.u1, u.u2, u.u3
   FROM   UTable u
   WHERE  u.gid = 3
   AND    u.gt = 'dog night'
   ORDER  BY u.d, u.timestamp
   ) u
JOIN   OTable o USING (gid, gt, d);

See:

Select first row in each GROUP BY group?

If UTable is big, at least a multicolumn index on (gid, gt) is advisable. Same for OTable.

Maybe even on (gid, gt, d). Depends on data types, cardinalities, ...