all types

duckdb_all_types.sql

CREATE TABLE all_data_types (
      id INTEGER PRIMARY KEY,
      boolean_col BOOLEAN,
      tinyint_col TINYINT,
      smallint_col SMALLINT,
      integer_col INTEGER,
      bigint_col BIGINT,
      utinyint_col UTINYINT,
      usmallint_col USMALLINT,
      uinteger_col UINTEGER,
      ubigint_col UBIGINT,
      float_col FLOAT,
      double_col DOUBLE,
      date_col DATE,
      time_col TIME,
      timestamp_col TIMESTAMP,
      timestamptz_col TIMESTAMPTZ,
      interval_col INTERVAL,
      varchar_col VARCHAR,
      blob_col BLOB,
      array_col INTEGER[3], -- Fixed-length ARRAY of 3 integers
      list_col INTEGER[],   -- Variable-length LIST of integers
      map_col MAP(VARCHAR, INTEGER),
      struct_col STRUCT(name VARCHAR, age INTEGER),
      union_col UNION(name VARCHAR, age INTEGER)
);

INSERT INTO all_data_types VALUES (
      1, -- id
      TRUE, -- boolean_col
      127, -- tinyint_col
      32767, -- smallint_col
      2147483647, -- integer_col
      9223372036854775807, -- bigint_col
      255, -- utinyint_col
      65535, -- usmallint_col
      4294967295, -- uinteger_col
      18446744073709551615, -- ubigint_col
      3.14, -- float_col
      2.718281828459045, -- double_col
      '2023-10-05', -- date_col
      '14:30:00', -- time_col
      '2023-10-05 14:30:00', -- timestamp_col
      '2023-10-05 14:30:00+00', -- timestamptz_col
      INTERVAL '5 days', -- interval_col
      'Hello, DuckDB!', -- varchar_col
      '\xDEADBEEF', -- blob_col
      [1, 2, 3], -- array_col (fixed-length ARRAY of 3 integers)
      [1, 2, 3], -- list_col (variable-length LIST of integers)
      map {'key1': 1, 'key2': 2} , -- map_col
      {'name': 'Alice', 'age': 30}, -- struct_col
      'Alice' -- union_col (first variant: name)
  );
INSERT INTO all_data_types VALUES (
      2, -- id
      FALSE, -- boolean_col
      -128, -- tinyint_col
      -32768, -- smallint_col
      -2147483648, -- integer_col
      -9223372036854775808, -- bigint_col
      0, -- utinyint_col
      0, -- usmallint_col
      0, -- uinteger_col
      0, -- ubigint_col
      -3.14, -- float_col
      -2.718281828459045, -- double_col
      '1970-01-01', -- date_col
      '00:00:00', -- time_col
      '1970-01-01 00:00:00', -- timestamp_col
      '1970-01-01 00:00:00+00', -- timestamptz_col
      INTERVAL '1 year', -- interval_col
      'Goodbye, DuckDB!', -- varchar_col
      '\xCAFEBABE', -- blob_col
      [4, 5, 6], -- array_col (fixed-length ARRAY of 3 integers)
      [4, 5, 6, 7], -- list_col (variable-length LIST of integers)
      map {'key3': 3, 'key4': 4}, -- map_col
      {'name': 'Bob', 'age': 25}, -- struct_col
      25 -- union_col (second variant: age)
  );