PostgreSQL

How to copy data from one table to another using pgsql

H

If you need to copy data from one table (table_a) into another table (table_b) using pgsql. There are 2 methods on how to do this. Method 1 NOTE: If you are using this method table_b should not yet exist. If you want an exact duplicate without indexing or relying on objects use this. SELECT * INTO table_b FROM table_a Method 2 NOTE: This method will work if table_b already exist. This method uses...

How to copy a columns data from one table into a column in another table using PostGreSQL

H

How to copy a columns data from one table into a column in another table using PostgreSQL? Eg. name columns data from the users table into namecolumn in the new_users table. Execute the query below with different column names and table names and all the data for a specific column will be copied over to the selected column in another table. INSERT INTO n_owners(owner_name) SELECT owner_name FROM...

How to create a backup of a PostgreSQL database to a SQL file

H

How to create a backup of a PostgreSQL database to a sql file? This sql file then can be used to restore the database on a later stage. Follow the steps below in order to create a backup Open a Terminal Window if not yet in command line Login as user postgres. eg. sudo su postgres  Type in the following command pg_dump db_name -CdiOv > /tmp/db_name_backup.sql pg_dump = Utility to backup a...

How to export data to a CSV file from a PostgreSQL database together with table headings

H

Type in the following command in postgres using the command line: copy products to ‘/home/user/products.csv’ delimiters ‘,’ CSV HEADER products = (Database Table) ‘/home/user/products.csv‘ = (Location where to save the csv file) You need to have write permissions to write the file to the filesystem with the postgres user CSV HEADER – This is the feature...

How to export data to a CSV file from a PostgreSQL database

H

Type in the following command in postgres using the command line:
copy products to ‘/home/user/products.csv’ delimiters ‘,’

products = (Database Table)
‘/home/user/products.csv‘ = (Location where to save the csv file)
You need to have write permissions to write the file to the filesystem with the postgres user

How to import a CSV file into a postgresql?

Editing the pg_hba.conf file on a PostgreSQL database server so that a specific (other) server will be able to connect to the PostgreSQL database server

E

Editing the pg_hba.conf file on a postgresql database server so that a specific (other) server will be able to connect to the postgresql database server. Example: Say I have a server where all of my postgresql databases are running and I have an application on another server that needs to access a database from the database server. To make this possible I need to edit the pg_hba.conf file to...

How to execute SQL commands from a file in PostgreSQL

H

In my case I had a .sql file with hundreds of insert statements and I wanted to run all of them, I was left with two options namely manually entering every command via copy and paste or executing the file. I went for the second option and it worked. I used Postgres 8.4 on the command line on a Ubuntu machine. Answer: Follow these steps Connect to the relevant database eg. sudo -u postgres psql...

How to send the output of a PostgreSQL query to a CSV file using the CLI (Command Line Interface)

H

Log into the PostgreSQL interface on the command line, you should see a similar screen like the one below Welcome to psql 8.1.21, the PostgreSQL interactive terminal.   Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help with psql commands        \g or terminate with semicolon to execute query        \q to quit   database=# Type the following command...

How to create and delete a PostgreSQL database on the command line

H

This can be accomplished by using the dropdb and createdb command line wrappers The syntax to create a new database on the command line is createdb [ options ] dbname   Example: createdb -O user  new_database - E UTF8 This will create a new database called new_database and assign the user user to it and use the encoding UTF8   To drop a database on the command line the syntax dropdb [...

How to check the encoding for a database in PostgreSQL

H

To do this login to the command line and switch to the main Postgres user. eg su postgres The type the psql -l command this will produce a list of databases with there encodings                   List of databases             Name          |   Owner    | Encoding     ------------------------+------------+-----------    DB1                    | postgres   | UTF8    DB2                    |...

How to install and setup PostgreSQL on Fedora Core 15

H

Open a new Terminal Window and then install the latest PostgreSQL Server sudo yum install postgresql-server Now we need to initialize the Database Cluster sudo service postgresql initdb Now start the server service postgresql start Now login with the postgres user sudo su -l postgres This user automatically gets created when the PostgresSQL server gets installed Now we create a new PostgreSQL...

About Author

Ian Carnaghan

I am a software developer and online educator who likes to keep up with all the latest in technology. I also manage cloud infrastructure, continuous monitoring, DevOps processes, security, and continuous integration and deployment.

Follow Me