MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

  1.  
  2. MERGE INTO acme_obj_value d
  3. USING ( SELECT object_id
  4.         FROM   acme_state_tmp
  5.       ) s
  6. ON (d.object_id = s.object_id)
  7.   WHEN matched THEN
  8.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  9.   WHEN NOT matched THEN
  10.     INSERT (d.id, d.object_id, d.date_value)
  11.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)
  12.  

PostgreSQL statement:

  1.  
  2. WITH s AS (
  3.      SELECT object_id
  4.      FROM   acme_state_tmp
  5. ),
  6. upd AS (
  7.      UPDATE acme_obj_value
  8.      SET    date_value = LEAST(l_dt, d.date_value)
  9.      FROM   s
  10.      WHERE  acme_obj_value.object_id = s.object_id
  11.      RETURNING acme_obj_value.object_id
  12. )
  13. INSERT INTO acme_obj_value(id, object_id,  date_value)
  14. SELECT NEXTVAL('acme_param_sequence'), s.object_id, l_dt
  15. FROM   s
  16. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)
  17.  

Tagged with: , ,
Posted in Coding, PostgreSQL

Delphi: Warnigs → Errors

Delphi+Elevate+Warnings+to+Errors

Want to force your dev team to address Compiler Warnings rather than ignoring them?

Elevate Warnings to Errors:

1) Go to Project Options
2) Under Delphi Compiler, select Hints and Warnings
3) Expand the Output warnings item
4) For each important Warning you wish for your developers to address rather than ignore, select the drop-down list and select Error.

Enjoy!

Thanks Simon Stuart for hint!

Delphi+Elevate+Warnings+to+Errors

Tagged with: , ,
Posted in Coding, Delphi

MicroOLAP Database Designer meets PostgreSQL 9.4!

PgMDD boxDatabase Designer for PostgreSQL is an comprehensive CASE tool which works natively under Windows OS family and Linux under Wine/WineHQ allowing you to

  • build a clear and effective database structure visually,
  • see the complete picture (diagram) representing all the tables, references between them, views, stored procedures and other objects,
  • easily generate a physical database on a server,
  • modify it according to any changes you made to the diagram using fast ALTER statements,
  • import already existing diagram from the server,
  • export diagram to the common graphic formats or as SQL script,
  • create detailed printings and reports.

This release introduces support for the latest v9.4 SQL grammar. Among other important features JSONB type support were added. Modify Engine allows creating indexes CONCURRENTLY. Several non critical bugs fixed.

Download

You’re welcome to download the Database Designer for PostgreSQL 1.10.3 right now at: http://microolap.com/products/database/postgresql-designer/download/

Feedback

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system available at http://www.microolap.com/support/

Full changelog:

  • [!] PostgreSQL 9.4 support added
  • [+] Allow creating indexes CONCURRENTLY using Modify script
  • [+] JSONB type support added
  • [*] Primary column removed from Column Manager
  • [*] Support for ENUM types improved in Column Manager
  • [*] uuid[] type added to editors
  • [-] “Quote Default Value option not saved in Column Manager” bug fixed
Tagged with: , , , ,
Posted in Announces

Meet the MicroOLAP Database Designer for MySQL v2.1.3!

New Reverse Engineering dialog

PgMDD boxDatabase Designer for MySQL is a handy tool with intuitive interface that allows you to build a clear and effective database structure visually, see the complete picture (diagram) representing all the tables, references between them, views, stored procedures within your database. Then you can easily generate a physical database on a server, modify it according to any changes you made to the diagram. The product is specially developed for MySQL database and takes into account its features.

This release introduces enhanced Reverse Engineering dialog, options to prevent annoying warning messages, full support for modern table row formats, support for BLACKHOLE storage engine.

You’re welcome to download the Database Designer for MySQL 2.1.3 right now at:
http://microolap.com/products/database/mysql-designer/download/

Full change log:
[+] REDUNDANT and COMPACT row formats support is added
[+] BLACKHOLE storage engine support is added
[+] “Show warning before object deletion” option added to Environment Options
[+] “Show warning before editor closing” option added to Environment Options
[*] Reverse Engineering dialog vastly improved
[*] “Tables in a diagram row” option removed from Reverse Engineering dialog as deprecated

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system available at
http://www.microolap.com/support/

This slideshow requires JavaScript.

Tagged with: , , , ,
Posted in Announces, MySQL

MicroOLAP Database Designer for PostgreSQL v1.10.2 released

PgMDD boxDatabase Designer for PostgreSQL is an comprehensive CASE tool which works natively under Windows OS family and Linux under Wine/WineHQ allowing you to

  • build a clear and effective database structure visually,
  • see the complete picture (diagram) representing all the tables, references between them, views, stored procedures and other objects,
  • easily generate a physical database on a server,
  • modify it according to any changes you made to the diagram using fast ALTER statements,
  • import already existing diagram from the server,
  • export diagram to the common graphic formats or as SQL script,
  • create detailed printings and reports.

This release fixes two exasperating bugs with user interface as well as improves Check Diagram functionality. One should consider to upgrade to this release as soon as possible.

You’re welcome to download the Database Designer for PostgreSQL 1.10.2 right now at:
http://microolap.com/products/database/postgresql-designer/download/

Full changelog:
[-] “Access Violation error occurs if Run New Database Wizard option is checked in the Generate Database” bug fixed
[-] “Index fields popup editor deselects all previously checked columns” bug fixed
[*] Check Diagram makes Output and Result List visible
[*] Check Diagram doesn’t show index warnings for tables with PK’s or Unique constraints anymore

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system available at
http://www.microolap.com/support/

Tagged with: , , , ,
Posted in Announces

PostgresDAC meets RAD Studio XE7!

PostgresDAC v2.12.2 with support for Delphi XE7 and C++ Builder XE7 is available immediately!

You’re welcome to download the PostgresDAC v2.12.2 right now at:
http://microolap.com/products/connectivity/postgresdac/download/
or login to your private area on our site at
http://microolap.com/my/downloads/

Tagged with: , , , , ,
Posted in Announces

MicrOLAP Database Designer for PostgreSQL v1.10.0 is out!

PgMDD boxDatabase Designer for PostgreSQL is an comprehensive CASE tool which works natively under Windows OS family and Linux under Wine/WineHQ allowing you to

  • build a clear and effective database structure visually,
  • see the complete picture (diagram) representing all the tables, references between them, views, stored procedures and other objects,
  • easily generate a physical database on a server,
  • modify it according to any changes you made to the diagram using fast ALTER statements,
  • import already existing diagram from the server,
  • export diagram to the common graphic formats or as SQL script,
  • create detailed printings and reports.

Improved and enhanced long awaited release is available. It introduces support for the latest v9.4 SQL grammar as well as improved support for inheritance functionality. Modify Engine allows mark constraints as NOT VALID. Several non critical bugs fixed.

You’re welcome to download the Database Designer for PostgreSQL 1.10.0 right now at:
http://microolap.com/products/database/postgresql-designer/download/

Full changelog:
[!] SQL Executor and Validator support v9.4 grammar now
[!] Support for table inheritance improved dramatically
[+] “Always make PK columns first” option introduced in Diagram Options
[+] Allow CHECK constraints to be declared NOT VALID in Modify script
[+] Allow FOREIGN KEY constraints to be declared NOT VALID in Modify script
[+] Shortcut for “Save As..” and “Export” menu items added
[+] SQL keywords are properly highlighted according to 9.4 version
[*] Comments now shown at Preview tab of Table Editor by default
[*] Handling of incorrect file paths improved in Generate and Modify Database
[*] Merging of reversed user types with existing in model improved during Reverse Engineering
[-] “Changing domain schema doesn’t affect table representation where domain used” bug fixed
[-] “Changing some options in the Model Options doesn’t activate OK & Apply buttons” bug fixed
[-] “Column default values containing backslashes are corrupted after reloading model” bug fixed
[-] “Leak proof option doesn’t shown in the Function editor after reopen” bug fixed
[-] “LEAKPROOF attribute causes syntax error in the SQL Executor” bug fixed

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system available at
http://www.microolap.com/support/

Tagged with: , , , ,
Posted in Announces
Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 403 other followers