Clean setup: CREATE TABLE tbl ( given_date date , set_name varchar ); Use a singular term as column name for a single value. The data type is obviously date and not a timestamp. To transform your text parameters into a useful table: SELECT unnest(string_to_array(‘2001-01-01to2001-01-05,2001-01-10to2001-01-15’, ‘,’)) AS date_range , unnest(string_to_array(‘s1,s2’, ‘,’)) AS set_name; “Parallel unnest” is … Read more
TL;DR Use 172.17.0.0/16 as IP address range, not 172.17.0.0/32. Don’t use localhost to connect to the PostgreSQL database on your host, but the host’s IP instead. To keep the container portable, start the container with the –add-host=database:<host-ip> flag and use database as hostname for connecting to PostgreSQL. Make sure PostgreSQL is configured to listen for … Read more
serial is a pseudo data type, not an actual data type. It’s an integer underneath with some additional DDL commands executed automatically: Create a sequence (with matching name by default). Set the column NOT NULL and the default to draw from that sequence. Make the column “own” the sequence. Details: Safely and cleanly rename tables … Read more
Postgres 9.2 I quote Andrew Dunstan on the pgsql-hackers list: At some stage there will possibly be some json-processing (as opposed to json-producing) functions, but not in 9.2. Doesn’t prevent him from providing an example implementation in PLV8 that should solve your problem. (Link is dead now, see modern PLV8 instead.) Postgres 9.3 Offers an … Read more
Update: With PostgreSQL 9.5, there are some jsonb manipulation functionality within PostgreSQL itself (but none for json; casts are required to manipulate json values). Merging 2 (or more) JSON objects (or concatenating arrays): SELECT jsonb ‘{“a”:1}’ || jsonb ‘{“b”:2}’, — will yield jsonb ‘{“a”:1,”b”:2}’ jsonb ‘[“a”,1]’ || jsonb ‘[“b”,2]’ — will yield jsonb ‘[“a”,1,”b”,2]’ So, … Read more