psql: INSERT, if already exists, SELECT (two cidr version)

It is actually quite weird. I already set that the cidr is not null, but still, i can happily insert null value inside that.

Hence, it come the problem. I can't select if one of the column's value is null. COALESCE didn't help (on its own). Now we have to modify our script a little bit.

One of the solution is setting default value for cidr-typed column. But what value? 0.0.0.0/0 obviously didn't help. Because null is null. 0.0.0.0/0 is everyone.

Now here what i do.

Let say, we have

	CREATE TABLE audit_log.ip_addresses
	(
	ip_address_id bigint NOT NULL DEFAULT nextval('audit_log.ip_addresses_ip_address_id_seq'::regclass),
	address_client cidr,
	x_forwarded_for cidr,
	CONSTRAINT ip_addresses_pkey PRIMARY KEY (ip_address_id),
	CONSTRAINT address_uq UNIQUE (address_client, x_forwarded_for)
	)

view raw create_table_audit_log.ip_addresses.sql hosted with ❤ by GitHub

Hence, the (unoptimized, but working) query is like this:

*change 192.168.43.199 to Address Client and 0.0.0.0 to X Forwarded For Address, both nullable

	WITH ip_addresses_new AS (
	INSERT INTO audit_log.ip_addresses (address_client, x_forwarded_for)
	SELECT CAST('192.168.43.199' as cidr), CAST('0.0.0.0' as cidr) WHERE NOT EXISTS
	(
	SELECT ip_address_id FROM audit_log.ip_addresses
	WHERE COALESCE(address_client::text, '') = COALESCE(CAST('192.168.43.199' as cidr)::text, '') AND COALESCE(x_forwarded_for::text, '') = COALESCE(CAST('0.0.0.0' as cidr)::text, '')
	)
	RETURNING ip_address_id
	)
	SELECT *,'INSERT' FROM ip_addresses_new
	UNION
	SELECT ip_address_id,'SELECT' FROM audit_log.ip_addresses i
	WHERE COALESCE(i.address_client::text, '') = COALESCE(CAST('192.168.43.199' as cidr)::text, '') AND COALESCE(i.x_forwarded_for::text, '') = COALESCE(CAST('0.0.0.0' as cidr)::text, '')

view raw query_insert_or_select_two_cidr.sql hosted with ❤ by GitHub

tadah