Dev Stack Source

Answer : The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored: select concat_ws('//', owner_addr1, owner_addr2, owner_addr3) Test: red=# select concat_ws('//', 'abc', null, null, 'xx', null, 'xyz', null) as address; address -------------- abc//xx//xyz (1 row) Probably most obvious to use a CASE statement. There are 4 cases: owner_addr2 IS NULL AND owner_addr3 IS NULL => '' owner_addr2 IS NULL AND owner_addr3 IS NOT NULL => '//' || owner_addr3 owner_addr2 IS NOT NULL AND owner_addr3 IS NULL => '//' || owner_addr2 owner_addr2 IS NOT NULL AND owner_addr3 IS NOT NULL '//' || owner_addr2 || '//' owner_addr3 SELECT owner_addr1 || CASE WHEN owner_addr2 IS NULL AND owner_addr3 IS NULL THEN '' WHEN owner_addr2 IS NULL AND owner_addr3 IS NOT NULL THEN '//'...

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 : The following is the un-installation for PostgreSQL 9.1 installed using the EnterpriseDB installer. You most probably have to replace folder /9.1/ with your version number. If /Library/Postgresql/ doesn't exist then you probably installed PostgreSQL with a different method like homebrew or Postgres.app . To remove the EnterpriseDB One-Click install of PostgreSQL 9.1: Open a terminal window. Terminal is found in: Applications->Utilities->Terminal Run the uninstaller: sudo /Library/PostgreSQL/9.1/uninstall-postgresql.app/Contents/MacOS/installbuilder.sh If you installed with the Postgres Installer, you can do: open /Library/PostgreSQL/9.2/uninstall-postgresql.app It will ask for the administrator password and run the uninstaller. Remove the PostgreSQL and data folders. The Wizard will notify you that these were not removed. sudo rm -rf /Library/PostgreSQL Remove the ini file: sudo rm /etc/postgres-reg.ini Remove the PostgreSQL user using System Preferences -> U...

Answer : Use the aggregate FILTER option in Postgres 9.4 or later: SELECT category , count(*) FILTER (WHERE question1 = 0) AS zero , count(*) FILTER (WHERE question1 = 1) AS one , count(*) FILTER (WHERE question1 = 2) AS two FROM reviews GROUP BY 1; Details for the FILTER clause: Aggregate columns with additional (distinct) filters If you want it short : SELECT category , count(question1 = 0 OR NULL) AS zero , count(question1 = 1 OR NULL) AS one , count(question1 = 2 OR NULL) AS two FROM reviews GROUP BY 1; Overview over possible variants: For absolute performance, is SUM faster or COUNT? Proper crosstab query crosstab() yields the best performance and is shorter for longer lists of options: SELECT * FROM crosstab( 'SELECT category, question1, count(*) AS ct FROM reviews GROUP BY 1, 2 ORDER BY 1, 2' , 'VALUES (0), (1), (2)' ) AS ct (category text, zero int, one int, two int); Detailed explanation...

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 : 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 : 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 : 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 : 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 : Consult: https://odbc.postgresql.org/ Download: https://www.postgresql.org/ftp/odbc/versions/msi/ Data sources (ODBC) on Windows: Start → Search → odbc → User DSN → Add/Configure Example : $MyServer = "<ip>" $MyPort = "5432" $MyDB = "<database>" $MyUid = "<user>" $MyPass = "<pass>" $DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;" $DBConn = New-Object System.Data.Odbc.OdbcConnection; $DBConn.ConnectionString = $DBConnectionString; $DBConn.Open(); $DBCmd = $DBConn.CreateCommand(); $DBCmd.CommandText = "SELECT * FROM tb_module;"; $DBCmd.ExecuteReader(); $DBConn.Close(); Check if the DSN exists in ODBC data source. If not you have to create one going to 'Control Panel', 'Admin. Tools', 'Data Sources (ODBC)'. Then select 'Add User DSN'- Select the PostgreSQL driver, and fill in...

Answer : Simplify in a similar fashion to what you had in mind: DO $do$ BEGIN IF NOT EXISTS ( SELECT FROM pg_catalog.pg_roles -- SELECT list can be empty for this WHERE rolname = 'my_user') THEN CREATE ROLE my_user LOGIN PASSWORD 'my_password'; END IF; END $do$; (Building on @a_horse_with_no_name's answer and improved with @Gregory's comment.) Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE (up to at least pg 12). And you cannot execute dynamic DDL statements in plain SQL. Your request to "avoid PL/pgSQL" is impossible except by using another PL. The DO statement uses plpgsql as default procedural language. The syntax allows to omit the explicit declaration: DO [ LANGUAGE lang_name ] code ... lang_name The name of the procedural language the code is written in. If omitted, the default is plpgsql . The accepted answer suffers from a race condition if two such scripts...

Answer : DROP Table each time before creating TEMP table as below: BEGIN DROP TABLE IF EXISTS temp_table1; create temp table temp_table1 -- Your rest Code comes here The problem of temp tables is that dropping and recreating temp table bloats pg_attribute heavily and therefore one sunny morning you will find db performance dead, and pg_attribute 200+ gb while your db would be like 10gb. So we're very heavy on temp tables having >500 rps and async i\o via nodejs and thus experienced a very heavy bloating of pg_attribute because of that. All you are left with is a very aggressive vacuuming which halts performance. All answers given here do not solve this, because they all bloat pg_attribute heavily. So the solution is elegantly this create temp table if not exists my_temp_table (description) on commit delete rows; So you go on playing with temp tables and save your pg_attribute. You want to DROP term table after commit (not DELETE ROWS), so: begin create temp table temp...

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 : Use the copy_from cursor method f = open(r'C:\Users\n\Desktop\data.csv', 'r') cur.copy_from(f, temp_unicommerce_status, sep=',') f.close() The file must be passed as an object. Since you are coping from a csv file it is necessary to specify the separator as the default is a tab character The way I solved this problem particular to use psychopg2 cursor class function copy_expert (Docs: http://initd.org/psycopg/docs/cursor.html). copy_expert allows you to use STDIN therefore bypassing the need to issue a superuser privilege for the postgres user. Your access to the file then depends on the client (linux/windows/mac) user's access to the file From Postgres COPY Docs (https://www.postgresql.org/docs/current/static/sql-copy.html): Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access ri...

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...

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 : The error message is instrumental: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5433? port You may be trying to connect to the wrong port. Standard port is 5432 . Check how (and whether at all) you started your postgres server: postgres@db:~$ ps -auxww | grep ^postgres ... <stripped more lines> postgres 1274 0.0 0.3 1437240 57308 ? S May27 5:01 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf The manual has related information here. In my example, settings from /etc/postgresql/9.1/main/postgresql.conf got used, which says (among many other settings): port = 5432 Or run: netstat -nlp | grep postgres Or just look here (at least in Debian or Ubuntu): ls -lA /var/run/postgresql/ PostgreSQL picks the next free port if you create a new database cluster. Since you installed repeatedl...

Answer : No. That's why it is called MySQL Workbench. The reason is that the MWB uses a lot of MySQL specific functionality. I don't think there is currently something similar for Postgres, but you could try Glom or pgadmin3. A company-independent database manager is the commercial Aqua Data Studio - maybe it's worth a try.