snowflake

pig build pkg snowflake         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

CREATE EXTENSION snowflake;

CREATE EXTENSION snowflake;

snowflake.node = 1

CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

SELECT snowflake.convert_sequence_to_snowflake(
  pg_get_serial_sequence('orders', 'id')::regclass
);

INSERT INTO orders (payload) VALUES ('{"status":"new"}');
SELECT id, snowflake.format(id) FROM orders;

-- Generate a snowflake ID
SELECT snowflake.nextval();

-- Use an already converted named sequence
SELECT snowflake.nextval('orders_id_seq'::regclass);

-- Decode a fixed example: node 1, count 0
SET TIME ZONE 'UTC';
SELECT snowflake.get_epoch(136169504773246976);
-- 1704996539.845

SELECT to_timestamp(snowflake.get_epoch(136169504773246976));
-- 2024-01-11 18:08:59.845+00

SELECT snowflake.get_node(136169504773246976);
-- 1

SELECT snowflake.get_count(136169504773246976);
-- 0

SELECT snowflake.format(136169504773246976);
-- {"id": 1, "ts": "2024-01-11 18:08:59.845+00", "count": 0}

-- Use as default column
CREATE TABLE direct_ids (
  id int8 DEFAULT snowflake.nextval() PRIMARY KEY,
  data text
);

SELECT table_schema, table_name, column_name, column_default
FROM information_schema.columns
WHERE column_default LIKE 'snowflake.nextval(%';