postgres: upgrade a user to be a superuser?
ALTER USER myuser WITH SUPERUSER; You can read more at the Documentation
postgresql
How to import CSV file data into a PostgreSQL table
Take a look at this short article. The solution is paraphrased here: Create your table: CREATE TABLE zip_codes (ZIP char(5), LATITUDE double precision, LONGITUDE double precision, CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar); Copy data from your CSV file to the table: COPY zip_codes FROM ‘/path/to/csv/ZIP_CODES.txt’ WITH (FORMAT csv);
Insert, on duplicate update in PostgreSQL?
PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL) INSERT INTO the_table (id, column_1, column_2) VALUES (1, ‘A’, ‘X’), (2, ‘B’, ‘Y’), (3, ‘C’, ‘Z’) ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2; Searching postgresql’s email group archives for “upsert” leads to … Read more
How to drop a PostgreSQL database if there are active connections to it?
This will drop existing connections except for yours: Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them. PostgreSQL 9.2 and above: SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname=”TARGET_DB” — ← change this to your DB AND pid <> pg_backend_pid(); PostgreSQL 9.1 and below: SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity … Read more
Creating a copy of a database in PostgreSQL
Postgres allows the use of any existing database on the server as a template when creating a new database. I’m not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn’t: CREATE DATABASE newdb WITH TEMPLATE originaldb … Read more
Difference between text and varchar (character varying)
There is no difference, under the hood it’s all varlena (variable length array). Check this article from Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/ A couple of highlights: To sum it all up: char(n) – takes too much space when dealing with values shorter than n (pads them to n), and can lead to subtle errors because of adding trailing … Read more
psql: FATAL: database “” does not exist
It appears that your package manager failed to create the database named $user for you. The reason that psql -d template1 works for you is that template1 is a database created by postgres itself, and is present on all installations. You are apparently able to log in to template1, so you must have some rights … Read more
Can’t find the ‘libpq-fe.h header when trying to install pg gem
It looks like in Ubuntu that header is part of the libpq-dev package (at least in the following Ubuntu versions: 11.04 (Natty Narwhal), 10.04 (Lucid Lynx), 11.10 (Oneiric Ocelot), 12.04 (Precise Pangolin), 14.04 (Trusty Tahr) and 18.04 (Bionic Beaver)): … /usr/include/postgresql/libpq-fe.h … So try installing libpq-dev or its equivalent for your OS: For Ubuntu/Debian systems: … Read more
PostgreSQL error: Fatal: role “username” does not exist
Use the operating system user postgres to create your database – as long as you haven’t set up a database role with the necessary privileges that corresponds to your operating system user of the same name (h9uest in your case): sudo -u postgres -i As recommended here or here. Then try again. Type exit when … Read more
Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails
The problem is still your pg_hba.conf file*. This line: local all postgres peer Should be: local all postgres md5 After altering this file, don’t forget to restart your PostgreSQL server. If you’re on Linux, that would be sudo service postgresql restart. Locating hba.conf Note that the location of this file isn’t very consistent. You can … Read more