Skip to main content
1 vote

Alter all tables in schema to set an existing field as an IDENTITY PRIMARY KEY

I think this can do the job: SELECT format( 'ALTER TABLE %I.%I ADD PRIMARY KEY (objectid); ALTER TABLE %I.%I ALTER objectid ADD GENERATED ALWAYS AS IDENTITY;', table_schema, table_name, ...
s.k's user avatar
  • 372
1 vote

How to correctly deal with IDENTITY fields on parent/child tables when using inheritance in PostgreSQL

Even though it looks like the two tables are tightly connected, they are each their own, independent table. The connection is only that the inheritance child perforce has all the columns of the parent,...
Laurenz Albe's user avatar
  • 53.4k
1 vote

Auto-increment [id] field in a table in an SQL-Server database

Typically the best way to get this done if you have to preserve the values in the id column and not manually insert the id is - CREATE TABLE dbo.Settings_x ( GroupId INT --or ...
Caleb Carl's user avatar
1 vote

PostgreSQL: How to update a identity column to fix the error "duplicate key value violates unique constraint"

You don't need to know the sequence name behind the identity column to modify the value. It can be done with ALTER TABLE tab ALTER col RESTART WITH 1000;
Laurenz Albe's user avatar
  • 53.4k
1 vote

PosgtreSQL — Is it possible to specify a sequence name in a GENERATED BY DEFAULT AS IDENTITY column?

You can specify the sequence name when you create an identity column: create table my_table (id bigint primary key generated by default as identity (sequence name my_seq)); and then check the ...
Mathias Lundin's user avatar
1 vote
Accepted

PosgtreSQL — Is it possible to specify a sequence name in a GENERATED BY DEFAULT AS IDENTITY column?

No, there is no way to specify the sequence name when you create an identity column. PostgreSQL chooses the name automatically as <tablename>_<columnname>_seq. If there is already a ...
Laurenz Albe's user avatar
  • 53.4k
1 vote

The fastest and most memory efficient way to reset PK column values for a table with over 5 mililon records

The biggest bottleneck in your scheme is rebuilding the clustered index, followed by the single threaded and fully logged UPDATE. Avoiding both of those might offset the cost of an export and import. ...
Paul White's user avatar
  • 86.4k

Only top scored, non community-wiki answers of a minimum length are eligible