database
storing year in database [closed]
A year is an integer and even supports integer arithmetic in a meaningful way so text makes no sense. You don’t have a month or day so date is right out the window. The fine manual has this to say about smallint: The smallint type is generally only used if disk space is at a … Read more
When does “select for update” lock and unlock?
Locks are taken during (usually at or near the beginning of) a command’s execution. Locks (except advisory locks) are released only when a transaction commits or rolls back. There is no FOR UNLOCK, nor is there an UNLOCK command to reverse the effects of the table-level LOCK command. This is all explained in the concurrency … Read more
How to generate a random, unique, alphanumeric ID of length N in Postgres 9.6+?
Figured this out, here’s a function that does it: CREATE OR REPLACE FUNCTION generate_uid(size INT) RETURNS TEXT AS $$ DECLARE characters TEXT := ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789’; bytes BYTEA := gen_random_bytes(size); l INT := length(characters); i INT := 0; output TEXT := ”; BEGIN WHILE i < size LOOP output := output || substr(characters, get_byte(bytes, i) % l … Read more
Recommended NoSQL Database for use with Python [closed]
Most of the nosql databases have python clients which are actively supported. Pick your database based on your usage needs. Using it from python shouldn’t be a problem. To name a few: Cassandra: https://github.com/datastax/python-driver Riak: https://github.com/basho/riak-python-client MongoDB: http://api.mongodb.org/python/current/ CouchDB: http://wiki.apache.org/couchdb/Getting_started_with_Python Redis: https://github.com/andymccurdy/redis-py
Sample database for PostgreSQL
There’s a PgFoundry project that contains several example PostgreSQL databases. Most of these haven’t been updated for a while, but will still work with recent PostgreSQL versions. If you need a bigger database, the MusicBrainz music metadata database has full database dumps available for download.
Is there a performance hit using decimal data types (MySQL / Postgres)
Pavel has it quite right, I’d just like to explain a little. Presuming that you mean a performance impact as compared to floating point, or fixed-point-offset integer (i.e. storing thousandsths of a cent as an integer): Yes, there is very much a performance impact. PostgreSQL, and by the sounds of things MySQL, store DECIMAL / … Read more
How to convert a postgres database to SQLite?
I found this blog entry which guides you to do these steps: Create a dump of the PostgreSQL database. ssh -C [email protected] pg_dump –data-only –inserts YOUR_DB_NAME > dump.sql Remove/modify the dump. Remove the lines starting with SET Remove the lines starting with SELECT pg_catalog.setval Replace true for ‘t’ Replace false for ‘f’ Add BEGIN; as … Read more
What is the correct way of QSqlDatabase & QSqlQuery?
When you create a QSqlDatabase object with addDatabase or when you call removeDatabase, you are merely associating or disassociating a tuple (driver, hostname:port, database name, username/password) to a name (or to the default connection name if you don’t specify a connection name). The SQL driver is instantiated, but the database will only be opened when … Read more