lightdb for postgresql数据类型详解与jdbc、java、c、libpq类型对应

 

PostgreSQL Data Type	SQL/JDBC Data Type	Java Type
bool	BIT	boolean
bit	BIT	boolean
int8	BIGINT	long
bigserial	BIGINT	long
oid	BIGINT	long
bytea/blob	BINARY	byte[]
char	CHAR	String
bpchar	CHAR	String
number/numeric	NUMERIC	java.math.BigDecimal
int4	INTEGER	int
serial	INTEGER	int
int2	SMALLINT	short
smallserial	SMALLINT	short
float4	REAL	float
float8	DOUBLE	double
money	DOUBLE	double
name	VARCHAR	String
text/clob	VARCHAR	String
varchar/varchar2	VARCHAR	String
date	DATE	java.sql.Date
time	TIME	java.sql.Time
timetz	TIME	java.sql.Time
timestamp/datetime	TIMESTAMP	java.sql.Timestamp
timestamptz	TIMESTAMP	java.sql.Timestamp
cardinal_number	DISTINCT	Mapping of underlying type
character_data	DISTINCT	Mapping of underlying type
sql_identifier	DISTINCT	Mapping of underlying type
time_stamp	DISTINCT	Mapping of underlying type
yes_or_no	DISTINCT	Mapping of underlying type
xml	SQLXML	java.sql.SQLXML
refcursor	REF_CURSOR	Undefined
_abc	ARRAY	java.sql.array

1 REF_CURSOR doesn’t appear in the jdbc appendices, but is mentioned in section “13.3.3.4 REF Cursor Support” of the specification, and may map to Types.REF_CURSOR. 

2 _abc stands for one of many ARRAY data types available in PostgreSQL (_record to _yes_or_no).

pg数组对应对应java类型为：java.sql.Array。如下：

try {
 
 String[] usa = {"New York", "Chicago", "San Francisco"};
 String[] canada = {"Montreal", "Toronto", "Vancouver"};
 String[] uk = {"London", "Birmingham", "Oxford"};

 /*
 Convert String[] to java.sql.Array using JDBC API
 */
 Array arrayUSA = conn.createArrayOf("text", usa);
 Array arrayCanada = conn.createArrayOf("text", canada);
 Array arrayUK = conn.createArrayOf("text", uk);
 String sql = "INSERT INTO city_example VALUES (?, ?)";
 PreparedStatement pstmt = conn.prepareStatement(sql);
 
 pstmt.setString(1, "USA");
 pstmt.setArray(2, arrayUSA);
 pstmt.executeUpdate();
 
 pstmt.setString(1, "Canada");
 pstmt.setArray(2, arrayCanada);
 pstmt.executeUpdate();
 pstmt.setString(1, "UK");
 pstmt.setArray(2, arrayUK);
 pstmt.executeUpdate();
 
 conn.commit();
} catch (Exception e) {
 
 System.out.println(e.getMessage());
 e.printStackTrace();
}

https://www.iteye.com/problems/90761测试的几种方式都没通过。

c和sql类型的对应关系

Table 37.2. Equivalent C Types for Built-in SQL Types

SQL Type	C Type	Defined In
boolean	bool	postgres.h (maybe compiler built-in)
box	BOX*	utils/geo_decls.h
bytea	bytea*	postgres.h
"char"	char	(compiler built-in)
character	BpChar*	postgres.h
cid	CommandId	postgres.h
date	DateADT	utils/date.h
float4 (real)	float4	postgres.h
float8 (double precision)	float8	postgres.h
int2 (smallint)	int16	postgres.h
int4 (integer)	int32	postgres.h
int8 (bigint)	int64	postgres.h
interval	Interval*	datatype/timestamp.h
lseg	LSEG*	utils/geo_decls.h
name	Name	postgres.h
numeric	Numeric	utils/numeric.h
oid	Oid	postgres.h
oidvector	oidvector*	postgres.h
path	PATH*	utils/geo_decls.h
point	POINT*	utils/geo_decls.h
regproc	RegProcedure	postgres.h
text	text*	postgres.h
tid	ItemPointer	storage/itemptr.h
time	TimeADT	utils/date.h
time with time zone	TimeTzADT	utils/date.h
timestamp	Timestamp	datatype/timestamp.h
timestamp with time zone	TimestampTz	datatype/timestamp.h
varchar	VarChar*	postgres.h
xid	TransactionId	postgres.h

参考：https://www.2ndquadrant.com/en/blog/using-java-arrays-to-insert-retrieve-update-postgresql-arrays

https://phoenixnap.com/kb/postgresql-data-types

https://www.pgcon.org/2013/schedule/attachments/269_tour-of-postgresql-data-types.pdf