Postgresql Questions

I try to list out the questions during working on Postgresql here for the future reference.

How to convert date string to timestamp in postgresql?

1
2
3


SELECT TO_TIMESTAMP('2017-03-31 9:30:20','YYYY-MM-DD HH:MI:SS');
-- it will return as following:
2017-03-31 09:30:20.000000

How to return a string value from jsonb in Postgresql?

Use jsonb->>key operator, be carefuly if you use jsonb->key it will return back jsonb type, not a string.

1
2
3
4
5
6
7


select '{
  "first_name": "Adam",
  "last_name": "Smith",
  "created_at": "2021-12-07 03:08:57",
  "updated_at": "2021-12-07 03:09:00"
}'::jsonb->>'first_name';
-- it will return `Adam`

Can I extract the info from jsonb in postgresql?

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

Extracts JSON sub-object at the specified path. (This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.)

1

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"

How to put log in stored procedures?

You need to change the config file postgresql.conf and set log_min_messages = notice, there are some options you can choose, check the config file.

IS NOT NULL work with composite type data

If you have a value something that is a composite type, for example a row of a table, something IS NOT NULL is only true if none of the attributes of the composite are NULL. check more details here

How to save 24hours time format

if you see some error like this Postgresql ERROR: hour "16" is invalid for the 12-hour clock you could do this TO_TIMESTAMP(i_test_date ->> 'created_at', 'YYYY-MM-DD HH24:MI:SS') to save the time as 24hours format

Table of Contents