Postgres: Create Postal Code Domain

This example creates the us_postal_code data type and then uses the type in a table definition. A regular expression test is used to verify that the value looks like a valid US postal code:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TABLE us_snail_addy (
  address_id SERIAL PRIMARY KEY,
  street1 TEXT NOT NULL,
  street2 TEXT,
  street3 TEXT,
  city TEXT NOT NULL,
  postal us_postal_code NOT NULL
);

Sequelize: Postgres ORM for Node.js

Sequelize is a promise-based ORM for Node.js and io.js. It supports the dialects PostgreSQL, MySQL, MariaDB, SQLite and MSSQL and features solid transaction support, relations, read replication and more.

Example usage

<span class="hljs-keyword">var</span> Sequelize = <span class="hljs-built_in">require</span>(<span class="hljs-string">'sequelize'</span>);
<span class="hljs-keyword">var</span> sequelize = <span class="hljs-keyword">new</span> Sequelize(<span class="hljs-string">'database'</span>, <span class="hljs-string">'username'</span>, <span class="hljs-string">'password'</span>);

<span class="hljs-keyword">var</span> User = sequelize.define(<span class="hljs-string">'User'</span>, {
  username: Sequelize.STRING,
  birthday: Sequelize.DATE
});

sequelize.sync().then(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">()</span> </span>{
  <span class="hljs-keyword">return</span> User.create({
    username: <span class="hljs-string">'janedoe'</span>,
    birthday: <span class="hljs-keyword">new</span> <span class="hljs-built_in">Date</span>(<span class="hljs-number">1980</span>, <span class="hljs-number">6</span>, <span class="hljs-number">20</span>)
  });
}).then(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(jane)</span> </span>{
  <span class="hljs-built_in">console</span>.log(jane.get({
    plain: <span class="hljs-literal">true</span>
  }))
});

 

 

 

Reading from the filesystem with Postgres

Let’s try to make the following SQL statement work:

SELECT file.read('/tmp/test.txt');  

.. Now we just need to fill in the function with logic to read from the filesystem!
CREATE FUNCTION file.read(file text)  
  RETURNS text AS $$
    DECLARE
      content text;
      tmp text;
    BEGIN
      file := quote_literal(file);
      tmp := quote_ident(uuid_generate_v4()::text);

      EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)';
      EXECUTE 'COPY ' || tmp || ' FROM ' || file;
      EXECUTE 'SELECT content FROM ' || tmp INTO content;
      EXECUTE 'DROP TABLE ' || tmp;

      RETURN content;
    END;
  $$ LANGUAGE plpgsql VOLATILE;