The Stack Overflow podcast is back! Listen to an interview with our new CEO.
2

You don't show the column definition, but it must be similar to "conversionFactor" decimal(10,2) At any rate, the scale of the column must be 2, so PostgreSQL has to round the number to be able to sore it in that column. You will have to use ALTER TABLE to change the colum definition to be able to store more fractional digits in the column.


2

Your explain plan is a bit confusing, as it looks like the index scan is getting the data for all 200 user_ids at once, but then doing that 200 times. But doing the experiment, that is not what it is doing, each iteration of the nested loop is getting the data for one user_id from that list, not the whole list. So it is just a presentation issue in the ...


2

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types. You could define your own tuple type and use that instead of a record: CREATE TYPE tuple AS (a int, b int); SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*; SELECT 1, (CASE ...


1

There are many possible query styles, most will readily use your PK index on (sensor_id, time) as it fits the task. (Postgres can read indexes backwards practically as fast.) This should be near perfect: SELECT s.sensor_id, sd.time, sd.value FROM unnest ('{1,3}'::int[]) s(sensor_id) LEFT JOIN LATERAL ( SELECT * FROM sensor_data sd WHERE sd....


1

Based on your comments, the INSERT could look something like this: INSERT INTO log (userid, clientaddr, calltime, query) SELECT user_id, -- function argument a.client_addr, a.query_start, a.query FROM pg_stat_activity AS a WHERE a.pid = pg_backend_pid(); The key to identifying the current session is the process ID of the backend ...


1

To do it with built-in tools of Postgres and the hstore module exclusively, without involving jsonb, as requested, replace: audit_row.row_data = hstore(NEW.*) - excluded_cols; with: audit_row.row_data = hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(hstore(NEW.*) - excluded_cols) WHERE value IS NOT NULL ) ); While ...


1

Your query eliminates user_id from the passed array. Typically, you'd want to show those with a count of 0. LEFT JOIN LATERAL .. ON true, followed by COALESCE takes care of that. If you actually want those eliminated switch to CROSS JOIN and drop COALESCE, same performance: SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count FROM unnest('{200 user ...


1

You could have a table CREATE TABLE user_info ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users, property text NOT NULL, value TEXT NOT NULL ); and store the additional properties there. That would be the relational way of doing it. In recent PostgreSQL versions you could also add a jsonb column for ...


1

In the example CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french ); where the ( COPY = french ) is located? Text search configurations are stored in pg_catalog.pg_ts_config. They can also be displayed with \dF[+] in psql. Can be the copy statement ( COPY = french ) use data from the STDIN with my own rules? No. The COPY clause in CREATE TEXT SEARCH ...


Only top voted, non community-wiki answers of a minimum length are eligible