27

I can't see any use case where there are 100 boolean variables that are directly related to the user, and thus belongs to the user table. My guess is that these booleans are for training, certifications or similar. I'll assume this for the rest of my answer. A separate table "user_training" is the way to do this. In this table you have the "...


14

I recommend using varbit, mostly because it is space efficient. Sure, storage space is cheap, but you have to cache in RAM, which is not so cheap, and manipulating smaller rows performs much better. It is also easy to manipulate: CREATE TABLE mytable (id bigint PRIMARY KEY, flags varbit); INSERT INTO mytable VALUES (1, b'11010001000110100'); To get the ...


4

The problem is that it is impossible to determine how and where the sequence is used. It doesn't have to be the DEFAULT clause of a column that owns the sequence: nextval could be called anywhere and used in creative ways, even outside the database. Even if you consider only the case of an identity column, scanning the table for "missing values" ...


4

You can enable log_lock_waits to get information about sessions that have to wait more than deadlock_timeout for a lock. You can reduce deadlock_timeout to see shorter waits. Other than that, monitor the wait_event_type and wait_event in pg_stat_activity. If you see locks regularly, that can be a problem.


3

The ON CONFLICT clause is strictly for handling unique violation and exclusion constraint violation errors, as documented in the manual. There is no similar syntax allowing you to handle foreign key violation errors. You can ignore invalid references by incorporating checks into your insert statement, although you will need to switch from INSERT...VALUES to ...


3

I resolved this issue as follows (all code below is available on the fiddle here): I added some records just to make sure that I was getting the dates I wanted - and in response the OP's comment about having a cut_off date... INSERT INTO tmp_deps (id, code, name, start_date, end_date) VALUES (1, '11111111', 'Root Dep', '1970-01-01', '9999-12-31'), (2, '...


3

You are trying to connect according to the rule below host all all 127.0.0.1/32 md5 which says password is required. Most probably there is a line in .pgpass which has password for manage_invite database only. You can either remove -h localhost from command line which will use the line below; local all all ...


3

There are four mode: column, composite_type, and array, json Array is my favorite mode, Because, there is no column quantity limitation plenty of agg functions easy operate compare to json Here is example of column, composite and array; drop table if exists t_manycolumns ; drop table if exists t_composite ; drop table if exists t_arry ; drop type if ...


3

\ds without argument doesn't show sequences that are not accessible through the current search_path, whereas information_schema.sequences ignores the search path. On the other hand, information_schema.sequences won't show sequences that the current user cannot actually use, whereas \ds won't filter those out. According to the documentation: Only those ...


3

I'm not sure what you mean by "export sequences". I suppose that you mean to extract all statements as: CREATE SEQUENCE [sequence name] ....; ALTER SEQENCE [sequence name] OWNED BY [table name] If that's so than I would do pg_dump of db and then parse extract all sequence related DDL-statements. E.g. # CREATE SEQUENCE $ pg_restore db.dump --...


3

Since this is a tree structure, you can use a "parent" field with a FOREIGN KEY reference to the table itself, as described in Adjacency list vs. nested sets by Quassnoi. For example, if you had another item E with tree_path = 3.1, then the parent values of B, C, D would be E. The parent of E would be A. The parent of A would be NULL. If you delete ...


3

https://stackoverflow.com/questions/60151640/kdevtmpfsi-using-the-entire-cpu you should check the various possibilities described there but: https://github.com/docker-library/redis/issues/217 "...This is not a redis issue. This is a known crypto mining malware unrelated to redis..." google kdevtmpfsi, the results seem to indicate mining malware...


2

The pg_dump command you show cannot be the one you used, because there is no -o option, and assword is not a valid port number. Anyway, your problem is a different one: there is an open transaction that holds a lock on pg_namespace that prevents you from creating a schema. Debug as follows: In a psql session, run SELECT pg_backend_pid(); -- to figure out ...


2

The main problem with your trigger is that it's oblivious to concurrent changes. You requested an example in the comments so here's a simple one. Starting from the dbfiddle with 'lisa' being CEO of 'amd', let's add a second CEO: insert into staff values (alice', 'amd'); insert into roles values (2, 'ceo', 'alice'); tst=> select * from roles; id | role | ...


2

Using WHERE EXISTS() with a correlated suqeuery instead of a JOIN is one option since you are not returning anything from the joined table. Something like (untested): select "events".* from "events" WHERE exists (select 1 from "records" where "record"."status" = 'Mined') AND &...


2

WAL is retained until both all valid slots are satisfied and wal_keep_segments/wal_keep_size is satisfied. If all of your replication needs use slots, then wal_keep_size serves no purpose and should be zero. If the wal receiver becomes disconnected, the WAL is still maintained (unless max_slot_wal_keep_size is set and is exceeded) waiting for it to ...


2

One way to handle this is to create a foreign key from company to roles that always points to one of the CEOs: /* redundant, but needed as destination of the foreign key */ ALTER TABLE roles ADD UNIQUE (role, id); /* also redundant */ ALTER TABLE company ADD ceo_role text DEFAULT 'ceo' NOT NULL; ALTER TABLE company ADD CHECK (ceo_role = 'ceo'); ALTER TABLE ...


2

That's how PostgreSQL operates by default – just set shared_buffers so that the whole database fits in it. Inefficient queries can be slow even on a small database that is cached in memory. You will have to tune your workload.


2

pg_total_relation_size() sums up everything that belongs to a table, including bloat. But \dt in psql does not list all tables of a database, it only includes schemas listed in the current search_path. Check with: SHOW search_path; System tables in pg_catalog are always excluded. And there may be additional schemas. To see all tables in the database: \dt *.*...


2

If the connection you created has password saved in it, then just click the execute button again after the error comes. PgAdmin will reconnect the instance and execute the query. I am using pgAdmin 4.1.5 and that works for me. – Lohit Gupta With newer versions, you just have to right click on server and select refresh, it should automatically re-connect to ...


2

By using a materialized view and EXISTS, I lowered the execution time of the request by about 96%. I have changed : CREATE OR REPLACE VIEW public.v_univ_bf To CREATE MATERIALIZED VIEW public.v_univ_bf The materialized view is not refreshed with each query that uses it, it must be refreshed if changes have been made to the table. For my problem, I refresh ...


2

Create unix user accounts for each person, and set user-name maps in pg_ident so that everyone connects as postgres. https://www.postgresql.org/docs/10/auth-username-maps.html or alternatively create individual database users for each account and give them superuser privs. Optionally "set session authenticaton postgres" can be set as a startup ...


1

CREATE TABLE discounts (id INT, discount INT, offers JSON); INSERT INTO discounts VALUES (1, 10, '[{"type":"discount","percent":100},{"type":"freebie","id":100}]'::JSON), (2, 0, '[{"type":"discount","percent":100},{"type":"freebie","id"...


1

I added a idx_invited_member_events_inviter_user_id, and it seems to be working.


1

If the database was ever shutdown uncleanly, all the stats are reset. You could check pg_stat_database. Or they can be reset manually, either for the database or for individual tables. Can it be the case that AUTOVACUUM starts but can't finish? And similar for ANALYZE? Yes, but then there should be messages in the log file about it. For example, if you ...


1

Your insert syntax is incorrect, it should look like insert into <table> select <something> from <table> -- not like insert into <table> (something, something, (select .... And don't sprinkle parentheses at random -- they are meaningful. INSERT INTO samples SELECT nextval('my_sequence'), 'newsample', locations FROM samples WHERE ...


1

No, and sorry, but that is not acceptable from a security point of view. If the index knows the clear text data, the database (and the DBA) know the clear text data. You cannot index an encrypted column (other than for equality searches, where you can search for the encrypted data). That is the price you are paying for security.


1

This can be solved with the help of the FIRST_VALUE analytical function: UPDATE things AS tgt SET duplicate_of = src.first_id FROM ( SELECT id , title , author , FIRST_VALUE(id) OVER (PARTITION BY title, author ORDER BY id ASC) as first_id FROM things ) AS src WHERE tgt.id = src.id AND tgt.id <> src.first_id ;...


1

Index backwards First, and most prominently, the order of columns in your multicolumn index is backwards: [SharedOrder] (order_id, shared_to_owner) You need an index on "SharedOrder" (shared_to_owner, order_id) for this query. See: Is a composite index also good for queries on the first field? You may or may not need the one you have now (...


1

It makes no sense to have an identity column in a data type. After all, a data type does not hold data. So yes, that is not possible.


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