PostgreSQL | PostgreSQL Basic Syntax | How to Enter String and Numeric Values

Tags:

• PostgreSQL
• Database

How to Enter String Constants

Write strings by enclosing them in single quotes (').

'string'

To include a single quote in a string, write two single quotes in a row.

devkuma=# select 'My father''s car';
    ?column?
-----------------
 My father's car
(1 row)

For characters that need special handling inside a string, write them as characters inside single quotes. Characters such as \ are processed as ordinary characters.

If strings are written consecutively with only a line break or whitespace between them, they are treated as one string.

devkuma=# select 'Hello'
devkuma-# 'World';
  ?column?
------------
 HelloWorld
(1 row)

The string Hello and the string World are split across a line break, but they are processed as one string.

Escape Strings

When you want to display characters that cannot be entered from the keyboard, or when you want to enter special characters, PostgreSQL uses escape strings. Add E or e before the single quote.

E'string'
e'string'

The following special characters can be entered in escape strings.

\b      backspace character
\f      form feed
\n      newline
\r      carriage return
\t      tab character
\o      octal byte value
\xh     hexadecimal byte value
\uxxxx  16-bit or 32-bit hexadecimal Unicode character value

When using \ as a character in an escape string, write two backslashes as in \\. Also, when writing a single quote as a character, you can write two single quotes as in a normal string, '', or write it as \'.

The following displays a string that includes a tab character.

postgres=# select e'문자\t열';
  ?column?
------------
 문자    열
(1 row)

How to Enter Numeric Constants

To write a number, enter the number as-is.

7
105
3.512

When writing a decimal point, at least one digit is required before or after it.

.552
8.

You can also write numbers using the exponent symbol e. In that case, at least one digit is required after e.

8e5
1.41e-3

If you add + or - before a number, + or - is treated as an operator, not as part of the number.

-42
+602

How to Enter Bit String Constants

To write a bit string constant, write B or b before the normal string. The only characters that can be used in the string are 0 and 1.

B'01 '
b'1001 '

You can also write it in hexadecimal notation instead of binary. Add X or x before the normal string. The characters that can be used in the string are 0 through 9 and A through F.

X'3F '
x'A37E '

–

This page explained how to enter values such as strings and numbers in PostgreSQL.