Linked Questions

54
votes
2answers
57k views

Default value for UUID column in Postgres

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
25
votes
2answers
29k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
30
votes
1answer
7k views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
6
votes
1answer
1k views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
5
votes
1answer
765 views

Conditional INSERT with a nested CTE?

I am trying to figure out if there is a way I can make nested CTEs work for this particular case. Consider the following (highly contrived) scenario which is based on the actual application: there's ...
1
vote
1answer
241 views

Composite constraint of numrange and boolean

I have a current constraint like this: ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&...
3
votes
1answer
47 views

Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes

I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash. For example the hash of test is ...
2
votes
1answer
95 views

What are the drawbacks of using DOMAINS to store metadata about the column?

I came across a question about a data type for MD5 values. The recommendation in that question states using uuid as type for that field. The argumentation is solid. But I find that this might be ...
1
vote
1answer
93 views

Postgres SQL - Compare Count of rows, returning where a < b or b doesn't exist

I'm trying to select the 'hash' value from the tags table where either of the following conditions are true: Count of the 'thumb\_%' rows is Greater than Count of 'dht\_%' rows Count of the 'thumb\_%'...
1
vote
1answer
51 views

Why does ENCODE on BYTEA not seem to have any performance impact?

I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64). Initial thought was to enforce a valid hex string storing it ...