New answers tagged

0

PgAdmin4 has no option to modify graphically but as per PostgreSQL Document, we can alter the sequence data type to smallint, integer, and bigint Alter table column & sequence data type and set max value for the sequence ALTER SEQUENCE public.case_audit_case_audit_uid_seq AS bigint; ALTER SEQUENCE public.case_audit_case_audit_uid_seq MAXVALUE ...


1

Also, be aware that if you use an integer primary key (or unique constraint) there is no relation to the actual data. So that will mean that the data is physically sorted in a way that is possibly not very helpfull for your queries. A table is either a clustered index or a heap. A clustered index (thus the actual table) is sorted by the key that you ...


2

The big benefit is that the key is very narrow and naturally in order. Every non-clustered index you add by definition also contains the primary key in order to do look ups. So even though it doesn't appear to have much benefit, using an identity column as a PK would still be helpful. Also, if you ever expand on to this and add other tables that need to ...


0

Using PostgreSQL9.5+, you can benefit from the unique index existence, as here: CREATE TABLE foo (c1 integer, c2 varchar(20)); ​ CREATE TABLE Time: 8.268 ms ​ INSERT INTO foo (c1, c2) > SELECT i, md5(random()::text)::varchar(20) > FROM generate_series(1, 1000000) AS i; ​ INSERT 0 1000000 Time: 1609.967 ms (00:01.610) ​ CREATE UNIQUE INDEX foo_idx01 ...


1

If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck? The answer is NO because the step when SQL Server gets next identity value is outside of transaction. This is why when you perform ROLLBACK identity does not decrease and you can have gaps. Please see answer to similar question here


1

The problem with this model is that you did not identify all candidate keys in the relation and did not enforce uniqueness of those keys you did not identify. In reality {SSN} and, possibly, {Name, DoB, SomethingElse} would constitute additional candidate keys. Nevertheless, Id is still the primary key of this relation, but it does not identify the entity ...


2

What do you mean by the chronological order? If there's a transaction 1 that begins before transaction 2, but ends after transaction 2, is the first transaction transaction 1 or transaction 2? If you're using the SERIALIZABLE transaction isolation level, it guarantees that there is a total order for transactions: that is, there is some serial (non-parallel)...


16

Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always". SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1. The manual states this: [A]lthough multiple ...


Top 50 recent answers are included