> For the complete documentation index, see [llms.txt](https://tech.x2bee.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://tech.x2bee.com/dev-guide/pjt-prepare/framework/postgresql.md).

# PostgreSQL 참고

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

## 참고 1 : 참고 항목

* II. The SQL Language
* 8. Data Types
* VII. Internals
* 51. 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](http://www.sqlines.com/oracle/datatypes/clob)  | Character large object, ⇐ 4G                |
| 7                                   | [DATE](http://www.sqlines.com/oracle/datatypes/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)](http://www.sqlines.com/oracle/datatypes/raw) | 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:

{% code title="table\_column\_query.sql" %}

```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;
```

{% endcode %}

***

##