1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
-- postgres test script
\set
\set SYNTAX_HL_FORMAT terminal16m
\set SYNTAX_HL true
\?
\copyright
\set SYNTAX_HL_STYLE dracula
select 'test''
' \g
\set NAME myname
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS books CASCADE;
DROP TYPE IF EXISTS book_type CASCADE;
DROP TABLE IF EXISTS authors CASCADE;
DROP FUNCTION IF EXISTS say_hello(text) CASCADE;
CREATE TABLE authors (
author_id SERIAL PRIMARY KEY,
name text NOT NULL DEFAULT ''
);
CREATE INDEX authors_name_idx ON authors(name);
CREATE TYPE book_type AS ENUM (
'FICTION',
'NONFICTION'
);
CREATE INDEX authors_name_idx ON authors(name);
\set SYNTAX_HL_STYLE paraiso-dark
CREATE TABLE books (
/*
this is a multiline comment
*/
book_id SERIAL PRIMARY KEY,
author_id integer NOT NULL REFERENCES authors(author_id),
isbn text NOT NULL DEFAULT '' UNIQUE,
booktype book_type NOT NULL DEFAULT 'FICTION',
title text NOT NULL DEFAULT '',
year integer NOT NULL DEFAULT 2000,
available timestamp with time zone NOT NULL DEFAULT 'NOW()',
tags varchar[] NOT NULL DEFAULT '{}'
);
CREATE INDEX books_title_idx ON books(title, year);
insert into authors (name) values
('jk rowling'),
('author amazing')
\g
select * from authors;
\set COLNAME name
\set NAME amaz
\echo `echo hello`
select :"COLNAME" from authors where :COLNAME like '%' || :'NAME' || '%'
\print \raw
\g
\gset AUTHOR_
select :'AUTHOR_name';
\begin
insert into authors (name) values ('test');
\rollback
insert into authors (name) values ('hello');
select * from authors;
insert into books (author_id, isbn, title, year, available) values
(1, '1', 'one', 2018, '2018-06-01 00:00:00'),
(2, '2', 'two', 2019, '2019-06-01 00:00:00')
;
select * from books b inner join authors a on a.author_id = b.author_id;
CREATE FUNCTION say_hello(text) RETURNS text AS $$
BEGIN
RETURN CONCAT('hello ', $1);
END;
$$ LANGUAGE plpgsql;
select say_hello('a name!') \G
/* exiting! */
\q
|
