Tag Info

New answers tagged

0

You are mixing requirements for representation and storage. That's a common misconception for people coming from spreadsheet programs, where you typically do both at once. Just store a serial column. The underlying integer column occupies 4 bytes and is very efficient for various purposes in the DB. In comparison, 'PAT0000001' as text or varchar occupies 11 ...


1

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL. If so, yes, what you describe is possible, but please, please don't do this. A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g. CREATE TABLE blah( id serial primary key ); is actually shorthand for: ...


0

I was going to write a long post telling you that there are basically 3 ways to implement polymorphic associations, but someone (@Bill Karwin) has already done it better in a more compact way: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32 Your original solution is the first one presented on the slides, the one you propose is the 3rd ...


1

Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive. However, depending on the variable innodb_autoinc_lock_mode, other non-trivial inserts like insert... on duplicate ...


0

It depends, could be an hour or so. Perhaps you may like to give percona toolkit a try; pt-online-schema-change. pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully. Source: ...


1

As your goal is for: the end users to not able to guess the primary keys by simply incrementing numbers. then rather than just a variable increment of your keys (so they have to search a few tens or hundreds of values), why not go for something completely nonlinear? There's a function in the PostgreSQL wiki that should suit your needs, pseudo_encrypt. ...


1

I suggest a function taking a regclass parameter that runs ALTER SEQUENCE with a new randomly generated increment before it returns the next value from a given sequence. Can be used as drop-in replacement for nextval(). Per documentation on ALTER SEQUENCE: increment The clause INCREMENT BY increment is optional. A positive value will make an ...



Top 50 recent answers are included