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
This should be 1) correct and 2) as fast as possible: SELECT u.login, u.id, u.first_name FROM pref_users u WHERE u.login >= now()::date + interval ‘1h’ AND u.login > u.logout ORDER BY u.login; As there are no future timestamps in your table (I assume), you need no upper bound. Some equivalent expressions: SELECT localtimestamp::date + interval … 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
The currval will return the last value generated for the sequence within the current session. So if another session generates a new value for the sequence you still can retrieve the last value generated by YOUR session, avoiding errors. But, to get the last generated value on any sessions, you can use the above: SELECT … Read more
select count(*) from information_schema.tables; Or if you want to find the number of tables only for a specific schema: select count(*) from information_schema.tables where table_schema=”public”;
Based on @Anshu answer I am using: SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_activity WHERE state <> ‘idle’ AND query NOT LIKE ‘% FROM pg_stat_activity %’ ORDER BY age;
Database template1 exists only to provide barebone structure to create another empty database. You should never logon to template1, otherwise you will have problems. Probably easiest solution for you is to restart PostgreSQL server process, and logon again. Database that should always exist and is safe to logon is postgres. If restarting is not an … 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
You may use: SELECT last_value FROM sequence_name; Update: this is documented in the CREATE SEQUENCE statement: Although you cannot update a sequence directly, you can use a query like: SELECT * FROM name; to examine the parameters and current state of a sequence. In particular, the last_value field of the sequence shows the last value … Read more
This did the trick for me: cursor.execute(“””SELECT table_name FROM information_schema.tables WHERE table_schema=”public””””) for table in cursor.fetchall(): print(table)