Connecting Tableau to PostgreSQL

Let's look at the path of the configuration file:

su - postgres -c "psql -c 'SHOW config_file;'"

Open the file in nano using the obtained path, for example:

nano /etc/postgresql/12/main/postgresql.conf

Find the CONNECTIONS AND AUTHENTICATION section, uncomment listen_addresses and set it to '*':

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;

Open the pg_hba.conf file at the same path, for example:

nano /etc/postgresql/12/main/pg_hba.conf

Add a line to the IPv4 local connections section:

host    all             all             0.0.0.0/0               md5

Check the list of services:

service --status-all

Restart postrges — check the service name from the list obtained in the previous command:

service postgresql restart

Verify that it restarted correctly:

service postgresql status

You can check the connection from another server where psql is installed, replace ubuntu.totum.online with the host of your database server and totum with your postgresql user if it differs:

psql -h ubuntu.totum.online -U totum

https://www.tableau.com/support/drivers

Data Source — PostgreSQL

Operating System — ...

Bit Version — ...

The query is directed to SCHEMA.TABLE

If SHEMA-NAME contains non-standard characters including a hyphen, then enclose it in quotes "SHEMA-NAME".table

Fields in Totum are jsonb, so the query to them is FIELD_NAME->>'v'

For comparison by numeric fields (for fractional numbers, the separator is .):

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where (second_field->>'v')::NUMERIC > 20

For comparison by non-numeric fields (for checkbox comparison is done as a string field with 'true' and 'false'):

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where first_field->>'v' > 'B'

If we want to select rows by multiple field values, we can use the following notation:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where first_field->>'v' in ('B','F')

If we want to select rows excluding multiple field values, we can use the following notation:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where first_field->>'v' not in ('B','F')

Sorting by numeric fields:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where first_field->>'v' > 'B' order by (second_field->>'v')::NUMERIC asc

Sorting by non-numeric fields:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where first_field->>'v' > 'B' order by first_field->>'v' asc

To select where by an empty field (it can be null or an empty string "", for Totum this is the same, but for SQL these are different things, so use the or operator):

select first_field->>'v' as "field 1", second_field->>'v' as "field 2", list_field->>'v' as list from totum.tableau

where (first_field->>'v' is NULL or first_field->>'v' = '') order by (second_field->>'v')::NUMERIC desc

To select from fields of type List by including the value of this list:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2", list_field->>'v' as list from totum.tableau
where list_field->'v' @> '10'::jsonb order by (second_field->>'v')::NUMERIC desc

Strings and numbers stored in Totum in such lists are different for the database, to reliably search by value it needs to be duplicated through or as a number and as a string:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2", list_field->>'v' as list from totum.tableau
where (list_field->'v' @> '10'::jsonb or list_field->'v' @> '"10"'::jsonb) order by (second_field->>'v')::NUMERIC desc

If we want to select values from within the json stored in the field by keys:

select row_field->'v'->>'key_1' as "key 1", row_field->'v'->>'key_2' as "key 2" from totum.tableau where first_field->>'v' in ('B','F')

For tables with Hide deletion mode, to select active rows you need to specify where is_del = false