X

Actions

Home > Application Reference > PostgreSQL

Jump to a Section

Overview

Authorizing an Account

Authorization of a PostgreSQL Account

The first time you add a PostgreSQL card to a Flow, you will need to create a configuration. This will allow you to connect to your account, save your account information, and reuse that configuration next time you build a Flow with the connector. That way, you don’t have to sign in every time you want to build a Flow with this Connector. Instead, use the configuration you have already set up. You can create and save multiple configurations and share them with your teammates.

Here’s how to set up a new configuration in PostgreSQL:

  1. Enter in an Account Nickname. This should be unique so if you are connecting multiple PostgreSQL accounts you will be able to tell them apart (e.g. “Postgres 1”).
  2. Enter your PostgreSQL Username and Password.
  3. Enter the Database Host, the server that hosts your database (e.g. “stampy.db.elephantsql.com”). Do not include the protocol or port.
  4. Enter your Database Name, the unique name of your database on the server.
  5. Enter the Database Port, the port that your database can be accessed at. The default port for PostgreSQL is  ”5432”. You can find your port in the URL used to access your database after the hostname (e.g. “postgres://[username]:[password]@[hostname]:5432/[database name]“).
  6. Click Create to finish this configuration.

    Actions

    • Build Row Object

      Creates a row of data for an existing table in PostgreSQL. Good for use with the ‘Create Multiple Rows’ Action. If you wish to add a single row, use the ‘Create Row’ Action.

      Options

      • Table Name (drop down menu): A list of existing tables within your database. Choose the table which you wish to add to.

      Input Fields

      Input fields are dynamically generated based on existing columns in your table.

      Output Fields

      • Row Object (object): The row object that you can use for the ‘Create Multiple Rows’ Action.

    • Create Multiple Rows

      Inserts rows into a table in PostgreSQL. Use the ‘Build Row Object’ Action to create the Row Objects that you can drag and drop into this card.

      Options

      • Table Name (drop down menu): A list of existing tables within your database. Choose the table which you wish to add to. Or choose “–Insert Table–” if you wish to add the table as an input value at runtime.

      Input Fields

      if Table Name is “–Insert Table–”:

      • Table
        • Table Name (text): The name of the table you will be adding to.
      • Rows
        • Row Objects (objects): Add in Row Objects that will be added to the table.

      if Table Name is chosen as an option:

      • Rows
        • Row Objects (objects): Add in Row Objects that will be added to the table.

      Output Fields

      • Rows (list of objects): A list of rows newly added.

    • Create Row

      Inserts a row into a table in PostgreSQL. If you wish to add more than one row at once, use ‘Build Row Object’ and then ‘Create Multiple Rows’ to add the row objects.

      Options

      • Table Name (drop down menu): A list of existing tables within your database. Choose the table which you wish to add to. Choose “”–Insert Table–” to add the table as an input value.

      Input Fields

      If Table Name is “–Insert Table–”:

      • Table
        • Table Name (text): The name of the table you will be adding to.
      • Row
        • Row Object (object): Enter in a row object for the row to be added. Use ‘Build Row Object’.

      If the table is defined in Options:

      • Rows (dynamically generated based on existing columns in that table).

      Output Fields

      • Row Object (object): The new row.

    • Delete Rows

      Uses a Where Expression to delete rows in a table in PostgreSQL.

      Options

      • Table Name (drop down menu): A list of existing tables within your database. Choose the table which you wish to add to. Or “–Insert Table–“if you wish to add the table as an input.

      Input Fields

      if “–Insert Table–” is chosen:

      • Table
        • Table Name (text): The name of the table you wish to edit.
      • Where
        • Where Expression
          • Field (drop down menu): The column with the values used in the expression. (i.e. “year”).
          • Operator (drop down menu): The operator (equals, does not equal, less than, less than or equals to, greater than, greater than or equals to).
          • Value (text): The right-hand side of the condition. (i.e. “1995”).
          • ie: to delete all movies from your table in the year 1995 you would choose “year”, “=”, and “1995”.
      • Confirm
        • Execute Query? (T/F): Choose true to delete the rows when executed or choose False to do a “dry run” to test the result but not actually delete the rows.

      if the table is selected as an option:

      • Where
        • Where Expression
          • Field (drop down menu): The column with the values used in the expression. (i.e. “year”).
          • Operator (drop down menu): The operator (equals, does not equal, less than, less than or equals to, greater than, greater than or equals to).
          • Value (text): The right-hand side of the condition. (i.e. “1995”).
          • ie: to delete all movies from your table in the year 1995 you would choose “year”, “=”, and “1995”.
      • Confirm
        • Execute Query? (T/F): Choose true to delete the rows when executed or choose False to do a “dry run” to test the result but not actually delete the rows.

      Output Fields

      • Result
        • Affected Rows (list of objects):
          • dynamically generated columns
        • Query (object): The query used.

    • Execute Query

      Uses raw SQL to execute a query in PostgreSQL.

      Input Fields

      • Query
        • Raw SQL (text): The SQL statment you wish to execute (i.e. “SELECT * FROM mytable WHERE column_name =1).
      • Confirm
        • Execute Query? (T/F): Choose true to  execute the query or choose False to do a “dry run” to test the results.

      Output Fields

      • Result
        • Rows (list of objects): The result of the query.
        • Query (object): The query used.

    • Import from CSV

    • Query Rows

      Uses a Where Expression to return a selection of rows from a table in PostgreSQL.

      Options

      • Table Name (drop down menu): A list of existing tables within your database. Choose the table which you wish to add to. Or “–Insert Table–“if you wish to add the table as an input.
      • Result Set (drop down menu): Whether you wish to receive the first matching row, or a list of all matching rows.

      Input Fields

      if “–Insert Table–” is chosen:

      • Table
        • Table Name (text): The name of the table you wish to query.
      • Filter
        • Where Expression
          • Field (drop down menu): The column with the values used in the expression. (i.e. “year”).
          • Operator (drop down menu): The operator (equals, does not equal, less than, less than or equals to, greater than, greater than or equals to).
          • Value (text): The right-hand side of the condition. (i.e. “1995”).
          • ie: to return all movies from your movies table that have the year 1995 you would choose “year”, “=”, and “1995”.
      • Sort
        • Column (text): The name of the column you wish to organize the list with (i.e. “year”).
        • Direction (drop down menu): Either Ascending or Descending.

      if the table is selected as an option:

      • Filter
        • Where Expression
          • Field (drop down menu): The column with the values used in the expression. (i.e. “year”).
          • Operator (drop down menu): The operator (equals, does not equal, less than, less than or equals to, greater than, greater than or equals to).
          • Value (text): The right-hand side of the condition. (i.e. “1995”).
          • ie: to return all movies from your movies table that have the year 1995 you would choose “year”, “=”, and “1995”.
      • Sort
        • Column (text): The name of the column you wish to organize the list with (i.e. “year”).
        • Direction (drop down menu): Either Ascending or Descending.

      Output Fields

      Will either be a list of rows or a single row depending on your ‘Result Set’ option selection.

      • Row(s) (objects): The queried rows.
        • dynamically generated column data.

    • Read Table

      Reads the table column schema in PostgreSQL.

      Options

      • Table Name (drop down menu): The table you wish to read.

      Output Fields

      • Column Schema (list of objects): Returns a list containing the column name and type.

    • Update Rows

      Uses a Where Expression to update rows in a table in PostgreSQL.

      Options

      • Table Name (drop down menu): A list of existing tables within your database. Choose the table which you wish to add to. Or “–Insert Table–“if you wish to add the table as an input.

      Input Fields

      if “–Insert Table–” is chosen:

      • Table
        • Table Name (text): The name of the table you wish to edit.
      • Row
        • Row Object (object): Insert in a row object. Use ‘Build Row Object’ to create one.
      • Where
        • Where Expression
          • Field (drop down menu): The column with the values used in the expression. (i.e. “year”).
          • Operator (drop down menu): The operator (equals, does not equal, less than, less than or equals to, greater than, greater than or equals to).
          • Value (text): The right-hand side of the condition. (i.e. “1995”).
          • ie: to change the date for all movies from your table that have the year 1995 you would choose “year”, “=”, and “1995”.
      • Confirm
        • Execute Query? (T/F): Choose true to update the rows when executed or choose False to do a “dry run” to test the result but not actually update the rows.

      if the table is selected as an option:

      • Row
        • Dynamically generated columns for the updated row information.
      • Where
        • Where Expression
          • Field (drop down menu): The column with the values used in the expression. (i.e. “year”).
          • Operator (drop down menu): The operator (equals, does not equal, less than, less than or equals to, greater than, greater than or equals to).
          • Value (text): The right-hand side of the condition. (i.e. “1995”).
          • ie: to delete all movies from your table in the year 1995 you would choose “year”, “=”, and “1995”.
      • Confirm
        • Execute Query? (T/F): Choose true to delete the rows when executed or choose False to do a “dry run” to test the result but not actually delete the rows.

      Output Fields

      • Result
        • Rows (object): The updated rows.
        • Query (object): The query used.