Postgresql – Advance primary key after mass insert

postgresqlprimary-key

I have a table with a few rows in it. It has a primary. I insert a large number of rows, including all fields including the primary key. Now when I try to an insert without specifying the primary key it fails because it's trying to use a number that was already used as a primary key in the previously inserted rows. How can I tell the table to create new primary keys at numbers higher than the existing keys?

For example, say I have rows with primary keys 1-8 in the table and I do a mass insert of rows with primary keys 9 through 64. Then I try to do a single insert without a primary key and it fails because it's trying to use 9. The next try fails because it's trying to use 10. And so on. I want to tell the table to start counting new primary keys at 65. How can I do that?

Best Answer

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

Table name and column name are two separate parameters to the pg_get_serial_sequence() function.
And you don't need a subselect.
Details:

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Mysql – Unique insert performance: Primary Key vs Unique Index

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.

Best Answer

Related Solutions

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Mysql – Unique insert performance: Primary Key vs Unique Index

Related Question