PostgreSQL – Table Value Constructor

Background

Part of a DBA’s workload is to engage the Developers Community and make sure that SQL Changes are streamline and easy to review and deploy.

Adding Data

Recently we needed to add about one hundred records to a single table.

Table Value Constructor

Thankfully SQL 92 thought quite a bit ahead and recommends support for Table Value Constructor.

PostgreSQL

Sample Implementation


    DROP TABLE IF EXISTS actor;

    CREATE TEMPORARY TABLE actor
    (
          id SERIAL
          primary KEY

        , fullname varchar(100) not null

        , dateCreated TIMESTAMP not null
          DEFAULT CURRENT_TIMESTAMP

        , CONSTRAINT uniqueFullName
          unique
          (
              fullname
          )
    );

   insert into actor
   (
        fullname
   )
   VALUES
   (
       'Farrah Leni Fawcett'
   )
   ,
   (
       'Jaclyn Smith'
   )
   ,
   (
       'Kate Jackson'
   )
   ,
   (
       'John Forsythe'
   )

   ;

   select *
   from   actor
   ;

   drop table actor;

Output

actor.output.01.20191014.0740PM

Summary

Out of all the major database platforms, Oracle is the only one that does not currently support “Table Value Constructor“.

References

  1. Markus Winand
    • Modern-SQL.com
      • values — Create Rows out of Nothing
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s