Linked Questions

97
votes
2answers
150k 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?
54
votes
2answers
69k 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 ...
38
votes
1answer
12k 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 ...
19
votes
3answers
16k views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if db ...
8
votes
3answers
1k views

Why doesn't my UNIQUE constraint trigger?

I have this UNIQUE constraint: ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE ("col1", "col2", "col3", "col4", "col5", "col6", &...
6
votes
1answer
3k 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 ...
4
votes
1answer
2k views

UUID or BIGSERIAL Foreign Keys Postgres

I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?
7
votes
1answer
2k 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 ...
1
vote
3answers
1k views

Having md5(NULL) return a non-NULL value in postgresql

I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other ...
1
vote
1answer
1k 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
527 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
142 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 ...
1
vote
1answer
352 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 ...
2
votes
1answer
123 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
2answers
62 views

Find the greatest difference between each unique record with different timestamps

I have a PostgreSQL table with the following schema and data: CREATE TABLE IF NOT EXISTS T( id uuid PRIMARY KEY, username varchar(15), person varchar(10), tweets int, ...