O2P系列： 开发需要注意的一些不同点

postgres@[local:/tmp]:2030=#51374 create table tab_bigint1(id bigint);
CREATE TABLE

postgres@[local:/tmp]:2030=#51374 create table tab_bigint2(id bigint);
CREATE TABLE

postgres@[local:/tmp]:2030=#51374 insert into tab_bigint1 select generate_series(1,1000000);
INSERT 0 1000000
postgres@[local:/tmp]:2030=#51374 insert into tab_bigint2 select generate_series(1,1000000);
INSERT 0 1000000

postgres@[local:/tmp]:2030=#51374 select count(1) from tab_numeric1 t1 inner join tab_numeric2 t2 on t1.id = t2.id;
count
---------
1000000
(1 row)

Time: 408.367 ms

postgres@[local:/tmp]:2030=#51374 create table tab_numeric1(id numeric);
CREATE TABLE
postgres@[local:/tmp]:2030=#51374 create table tab_numeric2(id numeric);
CREATE TABLE

postgres@[local:/tmp]:2030=#51374 insert into tab_numeric1 select generate_series(1,1000000);
INSERT 0 1000000
postgres@[local:/tmp]:2030=#51374 insert into tab_numeric2 select generate_series(1,1000000);
INSERT 0 1000000

postgres@[local:/tmp]:2030=#51374 select count(1) from tab_bigint1 t1 inner join tab_bigint2 t2 on t1.id = t2.id;
count
---------
1000000
(1 row)

Time: 340.888 ms

NO_DATA_FOUND

SQL> DECLARE
v_name varchar(100);
begin
select name into v_name from t1 where id = 1;
DBMS_OUTPUT.put_line(v_name);
end;
/ 2 3 4 5 6 7
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

do
$$
declare
v_name varchar;
begin
select name
into v_name
from t1
where id = -1;
raise notice '%', v_name;
end;
$$ language plpgsql;

NOTICE: <NULL>
DO

TOO_MANY_ROWS

SQL> DECLARE
v_name varchar(100);
begin
select name into v_name from t1 where id = 1;
DBMS_OUTPUT.put_line(v_name);
end;
/
2 3 4 5 6 7 DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

postgres@[local:/tmp]:2030=#9114 select name from t1 where id =1;
name
-------
jason
jason
jason
(3 rows)

postgres@[local:/tmp]:2030=#9114 do
postgres-# $$
postgres$# declare
postgres$# v_name varchar;
postgres$# begin
postgres$# select name
postgres$# into v_name
postgres$# from t1
postgres$# where id = 1;
postgres$# raise notice '%', v_name;
postgres$# end;
postgres$# $$ language plpgsql;
NOTICE: jason
DO

postgres@[local:/tmp]:2030=#9114 do
postgres-# $$
postgres$# declare
postgres$#    v_name varchar;
postgres$# begin
postgres$#  select name  
postgres$#    into strict  v_name
postgres$#    from t1
postgres$#    where id = 1;
postgres$#  raise notice '%', v_name;
postgres$# end;
postgres$# $$ language plpgsql;
ERROR:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement


postgres@[local:/tmp]:2030=#9114 do
postgres-# $$
postgres$# declare
postgres$#    v_name varchar;
postgres$# begin
postgres$#  select name  
postgres$#    into strict  v_name
postgres$#    from t1
postgres$#    where id = -1;
postgres$#  raise notice '%', v_name;
postgres$# end;
postgres$# $$ language plpgsql;
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

Outer Joins

SELECT a.field1, b.field2
FROM a, b
WHERE a.item_id = b.item_id(+)

SELECT a.field1, b.field2
FROM a
LEFT OUTER JOIN b
ON a.item_id = b.item_id;

Subquery in FROM

SELECT * FROM (SELECT * FROM t2)

postgres@[local:/tmp]:2030=#9114 SELECT * FROM (SELECT * FROM t2);
ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM (SELECT * FROM t2);
^
HINT: For example, FROM (SELECT ...) [AS] foo.
postgres@[local:/tmp]:2030=#9114 SELECT * FROM (SELECT * FROM t2) as t2;
id | name
----+------
3 |
(1 row)

Connect by

Genrate_series

SQL> select level rn from dual connect by level <= 5;

RN
----------
1
2
3
4
5

postgres@[local:/tmp]:2030=#84421 SELECT * FROM generate_series(1,5) as rn;
rn
----
1
2
3
4
5
(5 rows)

postgres@[local:/tmp]:2030=#1675 insert into t1 select 1, 'jason';
INSERT 0 1
postgres@[local:/tmp]:2030=#1675 commit;
WARNING:  there is no transaction in progress
COMMIT

postgres@[local:/tmp]:2030=#1675 create table TAB_UPPER (id int);
CREATE TABLE

postgres@[local:/tmp]:2030=#1675 select * from pg_tables where tablename = 'tab_upper';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | tab_upper | postgres   |            | f          | f        | f           | f
(1 row)

postgres@[local:/tmp]:2030=#1675 create table "TAB_UPPER" (id int);
CREATE TABLE

postgres@[local:/tmp]:2030=#1675 select * from pg_tables where tablename = 'TAB_UPPER';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | TAB_UPPER | postgres   |            | f          | f        | f           | f
(1 row)

postgres@[local:/tmp]:2030=#1675 insert into tab_upper select 1;
INSERT 0 1
postgres@[local:/tmp]:2030=#1675 select * from  tab_upper;
 id 
----
  1
(1 row)

postgres@[local:/tmp]:2030=#1675 select * from TAB_UPPER;
 id 
----
  1
(1 row)

postgres@[local:/tmp]:2030=#1675 select * from  "TAB_UPPER";
 id 
----
(0 rows)

postgres@[local:/tmp]:2030=#113686 \d t1
                         Table "public.t1"
 Column |          Type           | Collation | Nullable | Default 
--------+-------------------------+-----------+----------+---------
 id     | integer                 |           |          | 
 name   | character varying(1000) |           |          | 
Indexes:
    "idx" btree (id)

postgres@[local:/tmp]:2030=#113686 create index idx on t1(id);
ERROR:  relation "idx" already exists
postgres@[local:/tmp]:2030=#113686 create index idx1 on t1(id);
CREATE INDEX

postgres@[local:/tmp]:2030=#113686 \d+ t1
                                                    Table "public.t1"
 Column |          Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+-------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer                 |           |          |         | plain    |             |              | 
 name   | character varying(1000) |           |          |         | extended |             |              | 
Indexes:
    "idx" btree (id)
    "idx1" btree (id)
Access method: heap


postgres@[local:/tmp]:2030=#113686 SELECT
postgres-#     t.schemaname,
postgres-#     t.tablename,
postgres-#     c.reltuples::bigint                            AS num_rows,
postgres-#     pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
postgres-#     psai.indexrelname                              AS index_name,
postgres-#     pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
postgres-#     CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END  AS "unique",
postgres-#     psai.idx_scan                                  AS number_of_scans,
postgres-#     psai.idx_tup_read                              AS tuples_read,
postgres-#     psai.idx_tup_fetch                             AS tuples_fetched
postgres-# FROM
postgres-#     pg_tables t
postgres-#     LEFT JOIN pg_class c ON t.tablename = c.relname
postgres-#     LEFT JOIN pg_index i ON c.oid = i.indrelid
postgres-#     LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
postgres-# WHERE
postgres-#     t.schemaname NOT IN ('pg_catalog', 'information_schema')
postgres-# ORDER BY 1, 2;
 schemaname |    tablename    | num_rows | table_size |     index_name     | index_size | unique | number_of_scans | tuples_read | tuples_fetched 
------------+-----------------+----------+------------+--------------------+------------+--------+-----------------+-------------+----------------
 public     | TAB_UPPER       |       -1 | 0 bytes    |                    |            | N      |                 |             |               
 public     | payment_request |       -1 | 0 bytes    |                    | 0 bytes    | Y      |                 |             |               
 public     | payment_request |       -1 | 0 bytes    |                    | 0 bytes    | Y      |                 |             |               
 public     | student_grade   |       -1 | 8192 bytes | student_grade_pkey | 16 kB      | Y      |               0 |           0 |              0
 public     | t1              |  5000002 | 211 MB     | idx1               | 107 MB     | N      |               0 |           0 |              0
 public     | t1              |  5000002 | 211 MB     | idx                | 107 MB     | N      |               3 |           9 |              9
 public     | tab_upper       |       -1 | 8192 bytes |                    |            | N      |                 |             |               
 public     | tax_revenue     |       -1 | 8192 bytes | tax_revenue_pkey   | 16 kB      | Y      |               0 |           0 |              0
(8 rows)

postgres@[local:/tmp]:2030=#113686 SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
postgres-#        (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
postgres-#        (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
postgres-# FROM (
postgres(#     SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
postgres(#                                          coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
postgres(#     FROM pg_index) sub
postgres-# GROUP BY key HAVING count(*)>1
postgres-# ORDER BY sum(pg_relation_size(idx)) DESC;
  size  | idx1 | idx2 | idx3 | idx4 
--------+------+------+------+------
 214 MB | idx  | idx1 |      | 
(1 row)