search

nette / database

💾 A database layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle.
https://doc.nette.org/database
Other
512 stars 108 forks source link

PostgreSQL identity column #275

Closed diegosardina closed 3 years ago

diegosardina commented 3 years ago

Since PostgreSQL 10, identity columns are recommended instead of serial types as stated in the documentation (and serial types are even discouraged here).

Database Explorer doesn't recognise identity columns. Just adding a.attidentity = ANY (ARRAY['a', 'd']) does the trick.

A note about the last line in the change: With an auto increment column created in the correct way (by specifying SERIAL or IDENTITY) using pg_get_serial_sequence() is enough. However serial types can be created manually, but if the sequence is created without OWNED BY (e.g. Doctrine), that procedure returns null. I left the previous one to avoid breakage but since IDENTITY can't be created manually (and that procedure is the only way to get the sequence name in this case), in the future just pg_get_serial_sequence() is the way.

milo commented 3 years ago

Why closed?

diegosardina commented 3 years ago

Some tests were failing and I wasn't able to fix them in time, just need a bit more time and I will do a new pull request :-)

However one of the problem was with pg_get_serial_sequence() that needed to be guarded in some cases (and by quoting the table name with quote_ident() ).

If you are interested in reviewing it I will ping you when it's ready

dg commented 3 years ago

Please do not open a new pull request, always edit the current one.

diegosardina commented 3 years ago

Does it cause problems? Unfortunately I deleted the old branch :-\