PostgreSQL 참고

PostgreSQL Documentation URL: https://www.postgresql.org/docs/

참고 1 : 참고 항목

• II. The SQL Language

• 1. Data Types

• VII. Internals

• 1. System Catalogs

  • 51.64. System Views

• VIII. Appendixes

  • A. PostgreSQL Error Codes

참고 2 : Oracle vs PostgreSQL Data Types

Data type mapping:

	Oracle	PostgreSQL
1	BFILE	Pointer to binary file, ⇐ 4G
2	BINARY_FLOAT	32-bit floating-point number
3	BINARY_DOUBLE	64-bit floating-point number
4	BLOB	Binary large object, ⇐ 4G
5	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 2000
6	CLOB	Character large object, ⇐ 4G
7	DATE	Date and time
8	DECIMAL(p,s), DEC(p,s)	Fixed-point number
9	DOUBLE PRECISION	Floating-point number
10	FLOAT(p)	Floating-point number
11	INTEGER, INT	38 digits integer
12	INTERVAL YEAR(p) TO MONTH	Date interval
13	INTERVAL DAY(p) TO SECOND(s)	Day and time interval
14	LONG	Character data, ⇐ 2G
15	LONG RAW	Binary data, ⇐ 2G
16	NCHAR(n)	Fixed-length UTF-8 string, 1 ⇐ n ⇐ 2000
17	NCHAR VARYING(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000
18	NCLOB	Variable-length Unicode string, ⇐ 4G
19	NUMBER(p,0), NUMBER(p)	8-bit integer, 1 <= p < 3
16-bit integer, 3 <= p < 5	SMALLINT
32-bit integer, 5 <= p < 9	INT
64-bit integer, 9 <= p < 19	BIGINT
Fixed-point number, 19 <= p <= 38	DECIMAL(p)
20	NUMBER(p,s)	Fixed-point number, s > 0
21	NUMBER, NUMBER(*)	Floating-point number
22	NUMERIC(p,s)	Fixed-point number
23	NVARCHAR2(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000
24	RAW(n)	Variable-length binary string, 1 ⇐ n ⇐ 2000
25	REAL	Floating-point number
26	ROWID	Physical row address
27	SMALLINT	38 digits integer
28	TIMESTAMP(p)	Date and time with fraction
29	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone
30	UROWID(n)	Logical row addresses, 1 ⇐ n ⇐ 4000
31	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 4000
32	VARCHAR2(n)	Variable-length string, 1 ⇐ n ⇐ 4000
33	XMLTYPE	XML data

출처: https://www.tutorialdba.com/2018/04/oracle-vs-postgresql-data-types.html

---

참고 3 : PostgreSQL Table/Column 조회 샘플

SQL to retrieve table/column information:

table_column_query.sql

select clm.table_catalog,
       clm.table_schema,
       clm.table_name as "Table Name",
       pg_catalog.obj_description(pc.oid, 'pg_class') as "Table Comments",
       clm.column_name as "Column Name",
       pg_catalog.col_description(pa.attrelid, pa.attnum) as "Column Comments",
       clm.ordinal_position as "Column Order",
       clm.data_type as "Data Type",
       clm.is_nullable as "IsNull?",
       clm.character_maximum_length as "Char Length",
       clm.character_octet_length as "Byte Length"
from information_schema.columns clm
inner join pg_catalog.pg_class pc
    on clm.table_name = pc.relname
    and pg_catalog.pg_table_is_visible(pc.oid)
    and pc.relkind in ('r', '')
inner join pg_catalog.pg_attribute pa
    on pa.attrelid = pc.oid
    and pa.attnum > 0
    and not pa.attisdropped
where 1=1
  and clm.table_catalog = 'x2commerce'
  and clm.table_schema = 'public'
order by clm.table_name, clm.ordinal_position;

---