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);
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
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
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
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
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
In PostgreSQL, you can use the \connect meta-command of the client tool psql: \connect DBNAME or in short: \c DBNAME
If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is public) DROP SCHEMA public CASCADE; CREATE SCHEMA public; If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants. GRANT ALL ON SCHEMA public TO … Read more
To log in without a password: sudo -u user_name psql db_name To reset the password if you have forgotten: ALTER USER user_name WITH PASSWORD ‘new_password’;
Type \q and then press ENTER to quit psql. UPDATE: 19-OCT-2018 As of PostgreSQL 11, the keywords “quit” and “exit” in the PostgreSQL command-line interface have been included to help make it easier to leave the command-line tool.