SQL X-to-Y

SQL X-to-Y(damirsystems.com)

64 points by panda17 5 years ago | 30 comments

A Postgres feature I took way too long to discover: exclusion constraints, which is essentially "unique by some function of a column value" instead of "unique by a column value": https://www.postgresql.org/docs/13/ddl-constraints.html#DDL-...

darksaints 5 years ago | |

You, probably like myself, took way too long to discover them because they have a fairly niche usage and are probably Postgres' least appropriately documented feature. It makes it hard to imagine the ways that it can be used. We should probably do something about that.

ramraj07 5 years ago | | |

I read the second description in the manual and amazed it exists. I can totally see use cases where you want "almost unique" constraints (same name, date of birth too close??)

gberger 5 years ago |

Why do I have to complete a captcha to access this website?

afandian 5 years ago | |

You don't. Just close the site! I was curious but not that curious...

(edit: it's archived: http://archive.is/H9EYE )

t0mas88 5 years ago | | |

They even dare to tell you to turn off ad blocking to get through the Cloudflare captcha. So much for "making the internet faster"...

yamrzou 5 years ago | |

In addition, I find the new hCaptcha used by Cloudflare much worse than reCAPTCHA. It's slower, with ambiguous inputs demanding more mental effort, and sometimes it just refuses my correct answers.

flotzam 5 years ago | | |

Google probably has a good enough handle on you to serve the easy captchas. But keep in mind that for users they can't fingerprint (e.g. with Tor Browser), reCAPTCHA is usually insanely hard or even blocked ("Your computer or network may be sending automated queries"). hCaptcha has been heavenly compared to that.

dspillett 5 years ago | |

I've seen some sites suggest they CAPTCHAs will go away if I disable my ad blocker[†], so if not everyone is getting it this could be a factor. Before I bother even clicking to see if I get the CAPTCHA, is the content worth the effort?

[†] I can see how this replaces ad revenue at all, so I assume it is an attempt to make running an ad blocker seem like an inconvenience rather than a benefit.

jontro 5 years ago | | |

I still got the captcha even though I'm running no adblocker

panda17 5 years ago | |

Sorry, firewall thing -- dependent on recent (geo-ip) threats. Nothing to do with ads -- no ads.

orf 5 years ago | | |

Turn it off.

crtasm 5 years ago | | |

What threat to you are bots viewing your website?

netsharc 5 years ago | |

Same here, today I figured out I'm not human...

SPBS 5 years ago |

I find the notation compact but confusing. Are Thing and Category essentially tables? What does `thing {T, C}` mean, if T already means Thing?

panda17 5 years ago | |

A thing is uniquely identified by T, category by C.

May read it as: CREATE TABLE thing (T_ID integer, C_ID integer)

mulmen 5 years ago |

Is there further discussion of the "Alternate Key" somewhere? This is a new term to me. Is it like a candidate key?

panda17 5 years ago | |

In general there can be more than one candidate key for a given relation (table). Only one candidate key is "primary", all others are "alternate".

mulmen 5 years ago | | |

Thanks that makes sense.

Is this an industry term or are you just using it here to emphasize the difference?

kthejoker2 5 years ago | |

Yes, it is exactly a candidate key as they describe it - simply another field which uniquely identifies rows in the entity.

EDIT: Agree with panda17, they're using it as "candidate keys which were not chosen as primary key."

taffer 5 years ago |

SQL Assertions are incidentally the most requested feature for the Oracle database: https://community.oracle.com/tech/apps-infra/discussion/4390...

mulmen 5 years ago | |

/me mumbles something about the BOOLEAN data type.

mongol 5 years ago |

What is AK? I know PK and FK but AK has escaped me... at least the acronym...

marcos100 5 years ago | |

Basically, every time you use UNIQUE in a column or set of columns that is not a PK, it is an AK (Alternate Key).