psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)

learn more… | top users | synonyms

1
vote
3answers
1k views

access columns returned by psql from bash

I want to use value returned by psql in bash. This is the script: psql "connection parameters" -c "SELECT pg_database_size('dbname');" The output is like this: pg_database_size ------------------...
1
vote
1answer
907 views

How to know how many lines are copied from table - psql

I want to write a script to backup a table using COPY. psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > backup....
26
votes
1answer
49k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
2
votes
1answer
2k views

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server): D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > D:\...
5
votes
2answers
2k views

Span single transaction over several sequential psql sessions

I am running PostgreSQL 8.4 database server and psql terminal front-end on Ubuntu 10.04 Lucid Lynx and would like to span a single transaction over several sequential psql sessions. When I connect to ...
4
votes
1answer
641 views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...
4
votes
1answer
16k views

Postgres client copy (\copy) command doesn't have access to a temporary table?

I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a ...
27
votes
3answers
70k views

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...
3
votes
2answers
12k views

psql denies access to file when using COPY FROM

When I do this: COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV; On this: -rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv psql tells me this: ERROR: could not open ...
12
votes
1answer
3k views

Source several files at once with psql

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
3
votes
1answer
3k views

How do I install pgcrypto on Windows?

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve ...
104
votes
2answers
49k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
6
votes
1answer
7k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
25
votes
3answers
61k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
579
votes
7answers
1.1m views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...