7

Database-level settings are not stored in any configuration file, but the pg_db_role_setting catalog, the only question is how to get them out there. As often in similar cases, psql can be of tremendous help here. The key is \set ECHO_HIDDEN on, which then shows the queries behind the different meta-commands. In one of my sandbox DBs I see the following: ...


6

It seems to be the default behaviour of SQL Workbench, have a look at this article: How do I change the resolution or scale of decimal data type on SQL Workbench. Quoted from the article: Normally, SQL Workbench doesn't display the decimal data with the full scale. By default the scale is 2. We can change the scale by the setting. Solution ...


3

Other answers clarified that trigram similarity is based on alphanumeric characters only. That's why all your examples match 100 %. You may still be able to make use of a trigram GiST or GIN index and establish your desired sort order with additional ORDER BY expressions. For your demonstrated case: SELECT my_column, similarity('$ Hello', my_column) AS sml ...


3

To add the new column use: ALTER TABLE the_table ADD COLUMN id_int integer; To populate the new column you need an UPDATE statement. I am assuming you have a primary key column named pk_column in your table. Obviously you need to replace that with the actual primary key column in your table. update the_table set id_int = t.rn from ( select ...


2

It is hard to make a prediction, because that depends on many factors like the chosen execution plan. You should first find out where the files are that keep growing. If they are in the base/12345 subdirectory of the PostgreSQL data directory (the number will be different), it is the materialized view that is being built. That would mean that your disk ...


2

I just tried it, and it is not possible, because exported snapshots are not replicated. Snapshots are persisted in the pg_snapshots subdirectory of the data directory, and you will see that they don't show up on the standby.


2

Plans are invalidated if any object that they use suffers “cache invalidation”. Compare this source comment: * Currently, we track exactly the dependencies of plans on relations and * user-defined functions. On relcache invalidation events or pg_proc * syscache invalidation events, we invalidate just those plans that depend * on the ...


2

You can also query the pg_settings view SELECT name, setting FROM pg_settings WHERE name in ('cluster_name','server_version','port'); @Laurenz Albe suggests using current_setting() which makes it easier to select results in a single row


2

That would be SELECT current_setting('cluster_name'), current_setting('server_version'); You can get all parameters that way. For automated processing, server_version_num is often better.


2

SELECT DISTINCT DATE_TRUNC('day', Createdtime) "Created date", MIN(Createdtime) OVER (PARTITION BY DATE_TRUNC('day', Createdtime)) "1st record", FIRST_VALUE(Value) OVER (PARTITION BY DATE_TRUNC('day', Createdtime) ORDER BY Createdtime ASC) "1st Value", MAX(Createdtime) OVER (PARTITION BY DATE_TRUNC('day', ...


2

This is not very complicated to do. If a BEFORE trigger returns a NULL value, this prevents the UPDATE from taking place. So in the trigger function you can check the conditions if an UPDATE is allowed. If not, Run an UPDATE statement that marks the row as inactive and do an INSERT. Something along the lines (not tested!): create or replace function ...


2

The fast query retrieves and sorts 26229 rows - for that small number of rows, the sorting can be done in memory, so obviously this is going to be quick. First because retrieving the data only takes ~500ms and then the sorting is done in 50ms. The slow query retrieves 560135 rows (20 times as many as the first query) but the time it wook - 149939ms - seems ...


2

I guess you can use the ASCII function: SELECT ID_STRING_A,ID_STRING_B , ASCII(ID_INT_A) - 64 AS ID_INT_A , ASCII(ID_INT_B) - 64 AS ID_INT_B FROM ... Perhaps the intention's more clear using: , ASCII(ID_INT_A) - ASCII('A') + 1 AS ID_INT_A EDIT, since the question where changed something like this is possible: WITH T (n, s) as ( ...


2

Your requirement is a little difficult to understand. It seems you want a unique ID value per unique string value, but not unique across the entire data set, i.e. if you have ABCDEF multiple times in the data set, the integer value will be the same across them. If so, you can use the DENSE_RANK() function to produce an incrementing integer id grouped based ...


1

CREATE TEMP TABLE map ( id serial PRIMARY KEY, str text NOT NULL ); INSERT INTO map (str) SELECT DISTINCT id_string_a FROM mytab; ALTER TABLE mytab ADD id_int_a integer; UPDATE mytab SET id_int_a = map.id FROM map WHERE mytab.id_string_a = map.str; DROP TABLE map; id_string_b is left as an exercise to the reader.


1

The problem is: Solution: Result: Thanks!


1

It seems that you need only the maximum of the last inserted (user_id,object_id) as a couple. The first INSERT could be: WITH inserted_rows as ( INSERT INTO dest_table SELECT user_id, object_id, object_type, colN, ... FROM source_table ORDER BY user_id, colN, object_id -- this is indexed LIMIT 1000 -- batch size RETURNING user_id, object_id ) SELECT row(...


1

I found the following solution UPDATE table1 set table1.date = (table2.date + (table2.numberOfDays || 'day')::INTERVAL) FROM tabel2 WHERE table1.id = table2.table1_id OR UPDATE table1 set table1.date = table2.date + make_interval(days => table2.numberOfDays) FROM tabel2 WHERE table1.id = table2.table1_id


1

If the file is not in pg_xlog, but there is a .ready file in archive_status, then somebody must have removed that file, which is why archival is failing. Temporarily set archive_command = '/bin/true' until the view pg_stat_archiver indicates that PostgreSQL is happy again, then set the parameter back to its original value. Perform a new base backup ...


1

apt-get install postgresql-10-repmgr fixed the issue.


1

Unlogged tables are not written in the write ahead log. In the case of a crash the data can not be retrieved.


1

You also have a table holding unique stations with identifiers, Could look like this: CREATE TABLE uniq_stations (station_id text); INSERT INTO uniq_stations VALUES ('001'), ('002'), ('003'); There will be more columns, which are irrelevant for us. This should be much faster then: SELECT station_id, s.value, date FROM uniq_stations u CROSS JOIN ( ...


1

You will find that you need superuser access for certain tasks, e.g. CREATE EXTENSION. Vieving database metadata is not a security problem. Just because you know that there is a column with credit card data doesn't get you any closer to accessing it. There is no way to keep somebody with shell access as PostgreSQL OS user from doing anything they want. ...


1

You need to unnest the array elements and combine that with an EXISTS condition select t.* from arr_test t where exists (select * from jsonb_array_elements(t.data -> 'my_arr') as x(o) where x.o ->> 'serial' = 'AAA'); Alternatively you can write that with a contains operator @>: select t.* from arr_test t where ...


1

That's not what indexes are for, you need a check constraint for that, For the "at most one can be not null" you can use a little trick: casting a boolean expression to an integer yields 0 or 1. If you add up the values for a is not null condition, the rule "at most one can be not null" translates to "the sum of those must always be 1": alter table ...


1

You could maintain a history table that has the same structure as the original table plus a timestamp column. Then create a trigger that copies the old row into that history table before applying the UPDATE. During the INSERT into the history table, the trigger would populate the timestamp column to store the point in time when the change occurred. The ...


1

If you are a superuser, you can temporarily disable the system triggers that implement the foreign key checks: BEGIN; ALTER TABLE example_2 DISABLE TRIGGER ALL; INSERT INTO example_2 SELECT g FROM generate_series(1, 1000) g, generate_series(1, 10000); ALTER TABLE example_2 ENABLE TRIGGER ALL; COMMIT; Dire warning: Do this only if you ...


1

Your SQL script looks good, except that you shouldn't use CREATE USER ... PASSWORD '...'; to give a user a password. The problem is that the password goes across the line in clear text and might end up in the server log. Either hash the password on the client side and send it that way, or use psql's \password command to change the password interactively. ...


1

As far as I know, the only way in PostgreSQL is to run something like pg_dumpall >/dev/null which will scream if it encounters table corruption. However, that won't test index integrity. For that, PostgreSQL v11 has the amcheck extension.


1

If the model is stable and there are always just these three references from shipment to personal_data, plain columns with FK constraints are the right and most efficient way to do it. Something like: CREATE TABLE shipment ( shipment_id serial PRIMARY KEY , shipper_id int NOT NULL REFERENCES personal_data , receiver_id int NOT NULL REFERENCES ...


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