What Type Of Support Is Available For PostgreSQL?

What Type Of Support Is Available For PostgreSQL?


By adding a PostgreSQL connection to your workspace, you can:
  • Automatically import and sync sales transactions to your workspace
    • We support any SQL query as long as we can incrementally retrieve data (details below)
    • Newly created PostgreSQL records will be synched to your workspace
    • Modified PostgreSQL records will be updated in your workspace
    • Deletion of PostgreSQL records will not result in any deletion in your workspace
    • PostgreSQL records no longer matching your SQL query will not result in any deletion in your workspace

To add a PostgreSQL connection, go to Settings > Connections and click on Add for PostgreSQL: 



Next, enter your user name, password, host name, port, and database. 



Please make sure the chosen account has permission to read from the SQL tables you want to access. You'll see the following:



Next, click on Transaction > Add Transactions and select PostgreSQL.
 

Here is a sample SQL query:
      SELECT * FROM public.MyTable WHERE Updated >= @Threshold ORDER BY Updated ASC

Your SQL query must allow for incremental data retrieval based on a last modified date:
  1. Your table should have a update field tracking when each record was last modified.
    1. In the example above, the field is called "Updated", but it could be named something else (ex: "Last Modified", "Last Updated On", etc.).
    2. When a record is created, the update field should also be set.
    3. We recommend that you use a timestamp (not a basic date) to track the last modified date.
  2. Your SELECT statement should include the update field - examples.
    1. "SELECT *" works because it includes the update field.
    2. "SELECT Updated, Amount" works because it includes the update field.
    3. "SELECT Amount" does NOT work because it does NOT include the update field.
  3. Your WHERE statement should include an AND condition with update field >= @Threshold - examples.
    1. "WHERE Updated >= @Threshold" works because it has a condition on the update field.
    2. "WHERE Amount > 2000 AND Updated >= @Threshold" works because it has an AND condition on the update field.
    3. "WHERE Amount > 2000" does NOT work because it does NOT have a condition on the update field.
    4. "WHERE Amount > 2000 OR Updated >= @Threshold" does NOT work because it has an OR condition on the update field.
  4. Your ORDER BY statement should include a primary ordering by update field ascending - examples.
    1. "ORDER BY Updated ASC, " works because it has primary ordering by update field ascending.
    2. "ORDER BY Updated ASC, Id DESC" works because it has primary ordering by update field ascending.
    3. "ORDER BY ID ASC" does NOT work because it does NOT have primary ordering by update field ascending.
    4. "ORDER BY ID ASC, Updated ASC" does NOT work because it does NOT have primary ordering by update field ascending.

To learn more about connections, click here.
To learn more about managing synchronizations, click here.
To learn more about importing transactions, click here.

    • Related Articles

    • What Type Of Support Is Available For NetSuite?

      This article assumes you've already setup a connection to NetSuite. Next, click on Transaction > Add Transactions and select NetSuite. Here is a basic SuiteQL query: SELECT T.*, TO_CHAR(T.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS ...

    • What Type Of Support Is Available For Stripe?

      By adding a Stripe connection to your workspace, you can automatically import and sync sales transactions to your workspace We support charges, invoices, refunds You will need to map fields the first time you import transactions Newly created Stripe ...

    • What Type Of Support Is Available For SalesForce?

      By adding a SalesForce connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace and only suggest adding missing ones You will be able to review and edit ...

    • What Type Of Support Is Available For QuickBooks Desktop?

      All editions of QuickBooks Desktop are supported: Pro, Enterprise, etc. By adding a QuickBooks connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace ...

    • What Type Of Support Is Available For QuickBooks Online?

      By adding a QuickBooks connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace and only suggest adding missing ones You will be able to review and edit ...