Newbe Dev Stack

Answer : As of PostgreSQL 9.4, you can use the ? operator: select info->>'name' from rabbits where (info->'food')::jsonb ? 'carrots'; You can even index the ? query on the "food" key if you switch to the jsonb type instead: alter table rabbits alter info type jsonb using info::jsonb; create index on rabbits using gin ((info->'food')); select info->>'name' from rabbits where info->'food' ? 'carrots'; Of course, you probably don't have time for that as a full-time rabbit keeper. Update: Here's a demonstration of the performance improvements on a table of 1,000,000 rabbits where each rabbit likes two foods and 10% of them like carrots: d=# -- Postgres 9.3 solution d=# explain analyze select info->>'name' from rabbits where exists ( d(# select 1 from json_array_elements(info->'food') as food d(# where food::text = '"carrots"' d(# );...

Answer : I just needed to spend more time staring at the documentation. This command: \x on will do exactly what I wanted. Here is some sample output: select * from dda where u_id=24 and dda_is_deleted='f'; -[ RECORD 1 ]------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- dda_id | 1121 u_id | 24 ab_id | 10304 dda_type | CHECKING dda_status | PENDING_VERIFICATION dda_is_deleted | f dda_verify_op_id | 44938 version | 2 created | 2012-03-06 21:37:50.585845 modified | 2012-03-06 21:37:50.593425 c_id | dda_nickname | dda_account_name | cu_id | 1 abd_id | (New) Expanded Auto Mode: \x auto New for Postgresql 9.2; PSQL automatically fits records to the width of the scr...

Answer : Yes you can index an array, but you have to use the array operators and the GIN-index type. Example: CREATE TABLE "Test"("Column1" int[]); INSERT INTO "Test" VALUES ('{10, 15, 20}'); INSERT INTO "Test" VALUES ('{10, 20, 30}'); CREATE INDEX idx_test on "Test" USING GIN ("Column1"); -- To enforce index usage because we have only 2 records for this test... SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT * FROM "Test" WHERE "Column1" @> ARRAY[20]; Result: Bitmap Heap Scan on "Test" (cost=4.26..8.27 rows=1 width=32) (actual time=0.014..0.015 rows=2 loops=1) Recheck Cond: ("Column1" @> '{20}'::integer[]) -> Bitmap Index Scan on idx_test (cost=0.00..4.26 rows=1 width=0) (actual time=0.009..0.009 rows=2 loops=1) Index Cond: ("Column1" @> '{20}'::integer[]) Total runtime:...

Answer : VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs. These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there is a n_dead_tup column that tells, per table, how much rows need to be vacuumed. (see Monitoring statistics in the doc for functions and views related to statistics gathering). A possible strategy in your case would be to suppress the scheduled VACUUM, keeping an eye on this view and checking on which tables the n_dead_tup is going up significantly. Then apply the aggressive VACUUM to these tables only. This will be a win if there are large tables whose rows never get deleted nor updated and the aggressive VACUUM is really necessary only on smaller tables. But keep running the ANALYZE for the optimiz...

Answer : OK, found the answer by browsing sequelize source code : https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/connection-manager.js#L39 To activate SSL for PG connections you don't need native: true or ssl: true but dialectOptions.ssl: true so the following did finally work: sequelize = new Sequelize(process.env.DATABASE_URL, { dialect: 'postgres', protocol: 'postgres', dialectOptions: { ssl: true } }); You no longer need to parse the DATABASE_URL env variable, there is a Sequelize constructor which accepts the connection URL: sequelize = new Sequelize(process.env.DATABASE_URL, { dialect: 'postgres', protocol: 'postgres', dialectOptions: { ssl: true } }); One needs to add dialectOptions under ssl "development": { "username": process.env.DB_USERNAME, "password": process.env.DB_PASSWORD, "database": proce...

Answer : WITH RECURSIVE cte AS ( SELECT id, domain_name, valid FROM domains WHERE parent_id IS NULL UNION ALL SELECT domains.id, domains.domain_name, domains.valid FROM domains JOIN cte ON domains.parent_id = cte.id WHERE NOT cte.valid -- stop recursion when valid node reached ) SELECT id, domain_name FROM cte WHERE valid fiddle

Answer : Is there a way to prevent a full table scan during the alter table statement? At this time there is no supported, safe way to do that with PostgreSQL. Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet. In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea. So no, there isn't really a way. One potential alternative is to create a check constraint using NOT VALID , then validating the check constraint later. This method requires holding an ACCESS EXCLUSIVE lock only for the duration to create the constraint, which should be on the order of millisecon...

Answer : Given your specifications (plus additional info in the comments), You have a numeric ID column (integer numbers) with only few (or moderately few) gaps. Obviously no or few write operations. Your ID column has to be indexed! A primary key serves nicely. The query below does not need a sequential scan of the big table, only an index scan. First, get estimates for the main query: SELECT count(*) AS ct -- optional , min(id) AS min_id , max(id) AS max_id , max(id) - min(id) AS id_span FROM big; The only possibly expensive part is the count(*) (for huge tables). Given above specifications, you don't need it. An estimate will do just fine, available at almost no cost (detailed explanation here): SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass; As long as ct isn't much smaller than id_span , the query will outperform other approaches. WITH params AS ( SELECT 1 AS min_id...

Answer : The error message pops up when none of the schemas in your search_path can be found. Either it is misconfigured. What do you get for this? SHOW search_path; Or you deleted the public schema from your standard system database template1 . You may have been connected to the wrong database when you ran drop schema public cascade; As the name suggests, this is the template for creating new databases. Therefore, every new database starts out without the (default) schema public now - while your default search_path probably has 'public' in it. Just run (as superuser public or see mgojohn's answer): CREATE SCHEMA public; in the database template1 (or any other database where you need it). The advice with DROP SCHEMA ... CASCADE to destroy all objects in it quickly is otherwise valid. That advice can cause some trouble if you have an application user (like 'postgres') and run the DROP/CREATE commands as a different user. This would happen ...

Answer : Note/Update (2021): While this answer works , philosophically I agree with other comments that the right way to do this is to ask Postgres . Check whether the other answers that have psql -c or --command in them are a better fit for your use case (e.g. Nicholas Grilly's, Nathan Osman's, bruce's or Pedro's variant I use the following modification of Arturo's solution: psql -lqt | cut -d \| -f 1 | grep -qw <db_name> What it does psql -l outputs something like the following: List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-----------+----------+------------+------------+----------------------- my_db | my_user | UTF8 | en_US.UTF8 | en_US.UTF8 | postgres | postgres | LATIN1 | en_US | en_US | template0 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | |...

Answer : CREATE OR REPLACE FUNCTION boolean1(i smallint) RETURNS boolean AS $$ BEGIN RETURN (i::smallint)::int::bool; END; $$ LANGUAGE plpgsql; CREATE CAST (smallint AS boolean) WITH FUNCTION boolean1(smallint) AS ASSIGNMENT;

Answer : I had the similar problem but with another package. Turned out there had been a bunch of dead links pointing to the old version all other my file system. Here is what helped in my case: Run brew link <appname> (e.g. brew link postgress ); If completed successfully then you are golden, otherwise proceed with the next step; Take a look at the path in the error message (e.g. /usr/local/Cellar/postgresql/9.2.3/include/server ) transform the path by removing the Cellar/<app name>/<version> from it (e.g. /usr/local/include/server ) Find under that path all links referring to Cellar/<app name>/<version> and remove them; Goto step 1. Hope that helps brew update brew doctor is always first steps. to help finding files, update the files db sudo /usr/libexec/locate.updatedb this is similar to updatedb on ubuntu and you might want to alias it. then you may perform locate postgresql and learn more about where things are. Chanc...

Answer : From the docs, conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table. SELECT privilege is required on any column in the target table where corresponding excluded columns are read. So instead, try this per ypercubeᵀᴹ INSERT INTO accounts (id, token, affiliate_code) VALUES (value1, value2, value3) ON CONFLICT (id) DO UPDATE SET token = value2, affiliate_code = COALESCE(accounts.affiliate_code, excluded.affiliate_code); This answer helped me solve a slightly different ambiguous column problem. I have a table where we do daily roll-ups into the same table multiple times per day. We need to re-calculate the daily roll-up on an ho...

Answer : You can use NULLIF function e.g. something/NULLIF(column_name,0) If the value of column_name is 0 - result of entire expression will be NULL Since count() never returns NULL (unlike other aggregate functions), you only have to catch the 0 case (which is the only problematic case anyway): CASE count(column_name) WHEN 0 THEN 1 ELSE count(column_name) END Quoting the manual about aggregate functions: It should be noted that except for count , these functions return a null value when no rows are selected. I realize this is an old question, but another solution would be to make use of the greatest function: greatest( count(column_name), 1 ) -- NULL and 0 are valid argument values Note: My preference would be to either return a NULL, as in Erwin and Yuriy's answer, or to solve this logically by detecting the value is 0 before the division operation, and returning 0 . Otherwise, the data may be misrepresented by using 1 .

Answer : You can combine NULLIF with COALESCE : SELECT company, COALESCE(NULLIF(turnover, 0), revenue) AS revenue FROM company_profile; NULLIF returns NULL if it's arguments are equal, and the first argument otherwise.

Answer : I was able to install pgadmin4 on ubuntu 20.04 (focal fossa) using the following article as a base: https://linuxhint.com/install-pgadmin4-ubuntu/ A few changes to the instructions are required: In part 2: sudo apt-get install build-essential libssl-dev libffi-dev libgmp3-dev sudo apt-get install python3-virtualenv libpq-dev python3-dev In part 5: The latest version for the moment is: https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.20/pip/pgadmin4-4.20-py2.py3-none-any.whl (I used release, not a daily snapshot) In part 6: Use pip install pgadmin4-4.20-py2.py3-none-any.whl In part 7: Use 'python3.8' instead of 'python2.7' That's all. Worked for me. Update: Please note that it's possible install pgadmin4 (4.21) directly from the repositories now. The problem of the upstream debian repository was the python 3.8 support. They said that was fixed in this commit, but they are apparently missing this: https://github.com/postgre...

Answer : Why not include the PK definition inside the CREATE TABLE: CREATE TABLE IF NOT EXISTS mail_app_recipients ( id_draft Integer NOT NULL, id_person Integer NOT NULL, constraint pk_mail_app_recipients primary key (id_draft, id_person) ) You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests. if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY') then ALTER TABLE table_name ADD PRIMARY KEY (id); end if; You can try to DROP it before creating it ( DROP has the IF EXISTS clause): ALTER TABLE mail_app_recipients DROP CONSTRAINT IF EXISTS mail_app_recipients_pkey; ALTER TABLE mail_app_recipients ADD CONSTRAINT mail_app_recipients_pkey PRIMARY KEY ("id_draft","id_person"); Note that this require that you give a name to the primary key constraint - in th...