You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

postgres-create-trades-table.sql 4.9KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128
  1. CREATE SEQUENCE trades_id_seq
  2. INCREMENT 1
  3. MINVALUE 1
  4. MAXVALUE 9223372036854775807
  5. START 1
  6. CACHE 1;
  7. CREATE TABLE exchanges
  8. (
  9. id smallint NOT NULL,
  10. symbol character varying(4) NOT NULL,
  11. CONSTRAINT exchanges_pkey PRIMARY KEY (id)
  12. );
  13. CREATE TABLE currencies
  14. (
  15. id smallint NOT NULL,
  16. symbol character varying(6) NOT NULL,
  17. CONSTRAINT currencies_pkey PRIMARY KEY (id)
  18. );
  19. CREATE TABLE trades
  20. (
  21. id integer NOT NULL DEFAULT nextval('trades_id_seq'::regclass),
  22. "time" timestamp with time zone NOT NULL,
  23. exch smallint NOT NULL,
  24. base smallint NOT NULL,
  25. quote smallint NOT NULL,
  26. amount double precision NOT NULL,
  27. price double precision NOT NULL,
  28. side smallint NULL, -- side has no fk ... bid=1, ask=2
  29. server_time timestamp with time zone NULL,
  30. CONSTRAINT trades_pkey PRIMARY KEY (id),
  31. CONSTRAINT exch_fk FOREIGN KEY (exch)
  32. REFERENCES exchanges (id) MATCH SIMPLE
  33. ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  34. CONSTRAINT base_fk FOREIGN KEY (base)
  35. REFERENCES currencies (id) MATCH SIMPLE
  36. ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  37. CONSTRAINT quote_fk FOREIGN KEY (quote)
  38. REFERENCES currencies (id) MATCH SIMPLE
  39. ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
  40. );
  41. CREATE INDEX trades_time_abcdefg
  42. ON trades
  43. USING btree
  44. ("time");
  45. CREATE INDEX trades_base_quote_f6b2eeda
  46. ON trades
  47. USING btree
  48. (base, quote);
  49. CREATE INDEX trades_exchange_5d5c6971
  50. ON trades
  51. USING btree
  52. (exch);
  53. -- fill in exchanges/currencies
  54. INSERT INTO exchanges (id, symbol) VALUES (1, 'plnx');
  55. INSERT INTO exchanges (id, symbol) VALUES (2, 'krkn');
  56. INSERT INTO exchanges (id, symbol) VALUES (3, 'gdax');
  57. INSERT INTO exchanges (id, symbol) VALUES (4, 'exmo');
  58. INSERT INTO exchanges (id, symbol) VALUES (5, 'bits');
  59. INSERT INTO exchanges (id, symbol) VALUES (6, 'bmex');
  60. INSERT INTO exchanges (id, symbol) VALUES (7, 'btfx');
  61. INSERT INTO exchanges (id, symbol) VALUES (8, 'bnce');
  62. INSERT INTO exchanges (id, symbol) VALUES (9, 'okex');
  63. INSERT INTO exchanges (id, symbol) VALUES (10, 'drbt');
  64. INSERT INTO currencies (id, symbol) VALUES (1, 'btc');
  65. INSERT INTO currencies (id, symbol) VALUES (2, 'eth');
  66. INSERT INTO currencies (id, symbol) VALUES (3, 'xmr');
  67. INSERT INTO currencies (id, symbol) VALUES (4, 'usdt');
  68. INSERT INTO currencies (id, symbol) VALUES (5, 'ltc');
  69. INSERT INTO currencies (id, symbol) VALUES (6, 'dash');
  70. INSERT INTO currencies (id, symbol) VALUES (7, 'nvc');
  71. INSERT INTO currencies (id, symbol) VALUES (8, 'ppc');
  72. INSERT INTO currencies (id, symbol) VALUES (9, 'zec');
  73. INSERT INTO currencies (id, symbol) VALUES (10, 'xrp');
  74. INSERT INTO currencies (id, symbol) VALUES (11, 'gnt');
  75. INSERT INTO currencies (id, symbol) VALUES (12, 'steem');
  76. INSERT INTO currencies (id, symbol) VALUES (13, 'rep');
  77. INSERT INTO currencies (id, symbol) VALUES (14, 'gno');
  78. INSERT INTO currencies (id, symbol) VALUES (15, 'etc');
  79. INSERT INTO currencies (id, symbol) VALUES (16, 'icn');
  80. INSERT INTO currencies (id, symbol) VALUES (17, 'xlm');
  81. INSERT INTO currencies (id, symbol) VALUES (18, 'mln');
  82. INSERT INTO currencies (id, symbol) VALUES (19, 'bcn');
  83. INSERT INTO currencies (id, symbol) VALUES (20, 'bch');
  84. INSERT INTO currencies (id, symbol) VALUES (21, 'doge');
  85. INSERT INTO currencies (id, symbol) VALUES (22, 'eos');
  86. INSERT INTO currencies (id, symbol) VALUES (23, 'nxt');
  87. INSERT INTO currencies (id, symbol) VALUES (24, 'sc');
  88. INSERT INTO currencies (id, symbol) VALUES (25, 'zrx');
  89. INSERT INTO currencies (id, symbol) VALUES (26, 'bat');
  90. INSERT INTO currencies (id, symbol) VALUES (27, 'ada');
  91. INSERT INTO currencies (id, symbol) VALUES (28, 'usdc');
  92. INSERT INTO currencies (id, symbol) VALUES (29, 'dai');
  93. INSERT INTO currencies (id, symbol) VALUES (30, 'mkr');
  94. INSERT INTO currencies (id, symbol) VALUES (31, 'loom');
  95. INSERT INTO currencies (id, symbol) VALUES (32, 'cvc');
  96. INSERT INTO currencies (id, symbol) VALUES (33, 'mana');
  97. INSERT INTO currencies (id, symbol) VALUES (34, 'dnt');
  98. INSERT INTO currencies (id, symbol) VALUES (35, 'zil');
  99. INSERT INTO currencies (id, symbol) VALUES (36, 'link');
  100. INSERT INTO currencies (id, symbol) VALUES (37, 'algo');
  101. INSERT INTO currencies (id, symbol) VALUES (38, 'xtz');
  102. INSERT INTO currencies (id, symbol) VALUES (39, 'oxt');
  103. INSERT INTO currencies (id, symbol) VALUES (40, 'atom');
  104. INSERT INTO currencies (id, symbol) VALUES (100, 'usd');
  105. INSERT INTO currencies (id, symbol) VALUES (101, 'eur');
  106. INSERT INTO currencies (id, symbol) VALUES (102, 'rur');
  107. INSERT INTO currencies (id, symbol) VALUES (103, 'jpy');
  108. INSERT INTO currencies (id, symbol) VALUES (104, 'gbp');
  109. INSERT INTO currencies (id, symbol) VALUES (105, 'chf');
  110. INSERT INTO currencies (id, symbol) VALUES (106, 'cad');
  111. INSERT INTO currencies (id, symbol) VALUES (107, 'aud');
  112. INSERT INTO currencies (id, symbol) VALUES (108, 'zar');
  113. INSERT INTO currencies (id, symbol) VALUES (109, 'mxn');