postgres data types - ghdrako/doc_snipets GitHub Wiki

Conversion types

The CAST operator is especially useful when working with complex data types, such as arrays and JSON objects.

CREATE TABLE   string_data (  id SERIAL PRIMARY KEY,  string_value VARCHAR(20)  );
INSERT INTO string_data (string_value) VALUES ('123'), ('456'), ('789');
SELECT pg_typeof(string_value) FROM string_data;
SELECT id, CAST(string_value AS numeric) AS numeric_value FROM string_data;

pg_typeof() function Check the data type of column

Type Conversion Using CAST and :: operator

CAST(date_col AS TEXT)
date_col::text
CAST(val AS INTEGER)
num_val::numeric(10,2)

boolean

  • Use is to test against literały true,false or null rather than =
  • distinct from and not distinct from operators
  • Booleans can be aggregated thanks to bool_and and bool_or.

Character and Text

text and varchar are the same thing as far as PostgreSQL is concerned, and character varyingis an alias for varchar. When using varchar(15) you’re basically telling PostgreSQL to manage a text column with a check constraint of 15 characters.

  • function as overlay(),substring(),position()ortrim().
  • aggregates function as string_agg()
  • regular expression functions, including the very powerful regexp_split_to_table().
substring(comment.content from 1 for 25) || '…' 29 as content

regular expresion

  • operator: ~,!~,~*and!~*.

Note that PostgreSQL also supports indexing for regular expressions thanks to its trigram extension: pg_trgm.

regexp_split_to_table(themes, ',') 
regexp_split_to_array(  regexp_split_to_table(themes, ','),  ' > ')
  • regexp_split_to_table() rozbija na wiersze
  • regexp_split_to_array() rozbija na tablice a dostęp do elementow
categories[1] as category, 
categories[2] as subcategory

encodings

UTF8 Best choice

\l  -- list databases with encoding
show client_encoding;

Be aware that not all combinations ofserver encodingandclient encodingmake sense. thelatin1encoding on the client side if the server side dataset includes texts in incompatible encodings, PostgreSQL will issue an error.

format function

DO
$$ DECLARE v_stmt TEXT:='';
v_rec RECORD;
BEGIN 
 FOR v_rec IN (SELECT table_name FROM information_schema.tables WHERE table_ schema != 'pg_catalog' LIMIT 4) 
 LOOP 
  RAISE NOTICE '%', format('SELECT COUNT(*) FROM %I', v_rec.table_name);
 END LOOP;
END;
$$;
NOTICE: SELECT COUNT(*) FROM test_backup 
NOTICE: SELECT COUNT(*) FROM test 
NOTICE: SELECT COUNT(*) FROM big_table 
NOTICE: SELECT COUNT(*) FROM "a table" 
DO

used the format() function, where the string argument %I is replaced with the table names. Here, %I represents identifiers, such as column names, table names, or any other object names. The “format()” method provides several other options for constructing strings, such as generating fixed-length strings and reusing the arguments. Here is an example, where the format() function will reuse the given arguments in the string:

postgres=# DO 
$$ 
DECLARE
 v_stmt TEXT:='';
BEGIN
 SELECT format('SELECT COUNT(%2$s) FROM %1$s WHERE %2$s=%L', 'table_name', 'column_name', 'literal_value') 
 INTO v_stmt;
 RAISE NOTICE '%', v_stmt;
END;
$$;
NOTICE: SELECT COUNT(column_name) FROM table_name WHERE column_ name='literal_value' 
DO

In the preceding example, we reused the argument “column_name” in multiple places and used the third argument as a positional literal (%L).

Dealing with Null

String PostgreSQL treats an empty string (“) and NULL differently. An empty string is a string with zero characters, while NULL is not equal to any value including an empty string. In PostgreSQL, NULL and empty (“) strings are uncomparable.

use the session-level setting transform_null_equals. By setting this value to On IF v_data = NULL THEN worki With the help of the transform_null_equals parameter, PostgreSQL implicitly converts equality expressions containing NULL into expr IS NULL. This produces the desired results instead of an unknown value.

PostgreSQL provides the IS DISTINCT FROM and IS NOT DISTINCT FROM equality statements, which allow comparison between two values and return a boolean value of true or false. Consider the following example:

postgres=# SELECT NULL IS DISTINCT FROM '';
 ?column?
---------- 
t 
(1 row)

numbers

  • https://www.postgresql.org/docs/current/datatype-numeric.html
  • integer, 32 bits signed numbers
  • bigint, 64 bits signed numbers
  • smallint, 16 bits signed numbers * * numeric, arbitrary precision numbers
  • real, 32 bits floating point numbers with 6 decimal digits precision
  • double precision, 64 bits floating point numbers with 15 decimal digits precision
  • numeric (precision, scale), where the precision of a numeric is the total count of signif-icant digits in the whole number, and the scale of a numeric is the count of decimal digits in the fractional part. For example, 5.827 has a precision of 4 and a scale of 3.
select oprname, 
 oprcode::regproc, 
 oprleft::regtype, 
 oprright::regtype, 
 oprresult::regtype 
 from pg_operator 
 where oprname = '=' 
   and oprleft::regtype = 'bigint'::regtype;

Short of that, we would have to use decorated literals for numbers in all our queries, writing: where grid = bigint '1' and position = bigint '1'

unsigned number

PostgreSQL does not have an unsigned integer data type explicitly,To simulate:

  • use constraints
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    unsigned_column INTEGER CHECK (unsigned_column >= 0)
);
  • use domain - reuse in tables
CREATE DOMAIN unsigned_int AS INTEGER
CHECK (VALUE >= 0);

CREATE TABLE another_table (
    id SERIAL PRIMARY KEY,
    positive_count unsigned_int
);
* using custom function tovalidste
* using trigger