Skip to content

Instantly share code, notes, and snippets.

@agrrd

agrrd/memfile2pg.sql

Last active Jan 16, 2021
Embed
What would you like to do?
Kea migrate memfile leases to PostgreSQL
BEGIN;
CREATE OR REPLACE FUNCTION inet2bigint(inet) RETURNS bigint AS
$$
SELECT ($1::inet - '0.0.0.0'::inet)::bigint;
$$ LANGUAGE 'sql';
DROP CAST IF EXISTS (inet AS bigint);
CREATE CAST (inet AS bigint)
WITH FUNCTION inet2bigint(inet)
AS IMPLICIT;
DROP TABLE IF EXISTS lease4_staging;
CREATE TABLE lease4_staging (
address text ,
hwaddr text,
client_id text,
valid_lifetime text,
expire text,
subnet_id text,
fqdn_fwd text,
fqdn_rev text,
hostname text,
state text,
user_context text
);
\copy lease4_staging from '/tmp/kea-leases4.csv' delimiter ',' CSV header;
INSERT INTO lease4
SELECT
address::inet::bigint,
CONCAT('\x', REPLACE(hwaddr, ':',''))::bytea,
CONCAT('\x', REPLACE(client_id, ':',''))::bytea,
valid_lifetime::bigint,
to_timestamp(expire::int),
subnet_id::bigint,
fqdn_fwd::boolean,
fqdn_rev::boolean,
hostname::text,
state::bigint,
user_context
FROM
lease4_staging;
-- wipe staging object
DROP CAST (inet AS bigint);
DROP TABLE lease4_staging;
DROP FUNCTION inet2bigint(inet);
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment