database.psql

-- File:        database.psql
-- Project:     Apache NiFi Timestamp Resolution Blog
--
-- Copyright © Lima Buttgereit Holdings LLC d/b/a Muse Systems
-- This file may include content copyrighted and licensed from third parties.
--
-- See the LICENSE file in the project root for license terms and conditions.
-- See the NOTICE file in the project root for copyright ownership information.
--
-- muse.information@musesystems.com :: https://muse.systems

\set ON_ERROR_STOP true
\c postgres

CREATE DATABASE muse_nifi_example_db;

\c muse_nifi_example_db

CREATE TABLE source_table (
     id
        bigint
        PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ,test_timestamp
        timestamptz
        NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE avro_destination (
     id
        bigint
        PRIMARY KEY
    ,test_timestamp
        timestamptz
        NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE json_destination (
     id
        bigint
        PRIMARY KEY
    ,test_timestamp
        timestamptz
        NOT NULL DEFAULT clock_timestamp()
);

DO
$POPULATE$
DECLARE

    v_counter integer := 100;

BEGIN

    << source_insert_loop >>
    WHILE v_counter > 0 LOOP

        INSERT INTO source_table DEFAULT VALUES;

        PERFORM pg_sleep(round(random()*100000)/1000000);

        v_counter := v_counter - 1;

    END LOOP source_insert_loop;

END;
$POPULATE$ LANGUAGE plpgsql;