Random SQL Commands

Jan 30, 2016
1 min read

SQL und PostgreSQL Snippets

Tabelle erstellen

CREATE TABLE products
(
  id           SERIAL PRIMARY KEY,
  product_no   INTEGER,
  name         text,
  price        NUMERIC
);

Details

Fremdschlüssel erstellen

CREATE TABLE orders
(
  order_id     INTEGER PRIMARY KEY,
  product_no   INTEGER REFERENCES products (product_no),
  quantity     INTEGER
);

Details

Index erstellen

CREATE INDEX products_id_idx
  ON products(id);

Daten einfügen

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);
);

Daten updaten

UPDATE products SET price = 10 WHERE price = 5;

Schema search path setzen:

SET search_path TO privat,shared,public;

Generate Table with random values;

CREATE TABLE x
AS
SELECT t AS a,
       TRUNC(t*RANDOM())::int AS b,
       MD5(RANDOM()::text) AS c,
       CURRENT_DATE- TRUNC(t*RANDOM())::int AS d
FROM GENERATE_SERIES (1,5000,1) AS t

Copying Rows Between PostgreSQL Databases

psql test -c \
"\copy (SELECT i, t FROM original_table ORDER BY i) TO STDOUT" | \
psql test -c "\copy copy_table (i, t) FROM STDIN"***