numeric is exact! Unlike claimed by another answer, numeric is not a floating-point type, but an arbitrary precision type as defined by the SQL standard. Storage is exact. I quote the manual: The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing … Read more
Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you’ll still get a wide dispersion of values which presumably is good enough for your problem. SQL functions derived from the other answers: For bigint: create … Read more
Postgres 9.1 added FOREACH to loop over arrays: DO $do$ DECLARE _arr int[] := ‘{1,2,3,4}’; _elem int; — matching element type BEGIN FOREACH _elem IN ARRAY _arr LOOP RAISE NOTICE ‘%’, _elem; END LOOP; END $do$; db<>fiddle here Works for multi-dimensional arrays, too: Postgres flattens the array and iterates over all elements. To loop over … Read more
With inheritance like @Denis mentioned, this would be very simple. Works for Postgres 8.4, too. Be sure to consider the limitations. Basically, you would have a master table, I suppose in a master schema: CREATE TABLE master.product (title text); And all other tables in various schemata inherit from it, possibly adding more local columns: CREATE … Read more
I’m pretty sure you can’t simply add on delete cascade to an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to start a transaction, drop the foreign key, add a foreign key with on delete … Read more
uuid-ossp is a contrib module, so it isn’t loaded into the server by default. You must load it into your database to use it. For modern PostgreSQL versions (9.1 and newer) that’s easy: CREATE EXTENSION IF NOT EXISTS “uuid-ossp”; but for 9.0 and below you must instead run the SQL script to load the extension. … Read more
split_part() does what you want in one step: SELECT split_part(col, ‘,’, 1) AS col1 , split_part(col, ‘,’, 2) AS col2 , split_part(col, ‘,’, 3) AS col3 , split_part(col, ‘,’, 4) AS col4 FROM tbl; Add as many lines as you have items in col (the possible maximum). Columns exceeding data items will be empty strings … Read more