# 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 %}

***

##


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://tech.x2bee.com/dev-guide/pjt-prepare/framework/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.