Tagged Questions
2
votes
4answers
194 views
psql, getting a list of databases to be parsed by a script
I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them:
psql -l -A -t
but the output shows an obvious issue: the records are ...
0
votes
1answer
27 views
How can I time SQL-queries using psql from command line?
I am sorry, I cannot comment, so I am posting a new question. There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run ...
1
vote
1answer
13 views
Can't list users from heroku psql
I have a heroku postgres add-on database with a user table. However, when I attempt to select * from user, all I get is:
current_user
----------------
rtsjlhdfptlaqd
(1 row)
The table name is ...
0
votes
2answers
1k views
PostgreSQL cannot start after replacing contents inside its Data folder
When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
1
vote
1answer
34 views
Postgresql: set default psql client encoding
When I connect to a Postgresql DB using psql, I often get these messages:
=> SELECT * FROM question_view ;
ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in ...
1
vote
1answer
316 views
Tree structure hierarchical query sorting
I have the following structure of table.
CREATE TABLE table1
(
serial_num integer NOT NULL,
parent_num integer,
parent_key text,
key_ary_num integer[],
level integer,
rank integer,
...
18
votes
9answers
33k views
PostgreSQL is running locally but I cannot connect. Why?
Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
0
votes
1answer
30 views
Can PostgreSQL results include the query?
I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment.
psql -U <username> -h <servername> -d <databasename> -f
...
0
votes
0answers
12 views
Creation of a table with foreign keys in postgres is taking longer time [duplicate]
I am wondering if someone can help me understand this.
I tried to add an column to a table of 22000 rows which is referencing other tables(one of these tables have 0.5 million records). It didn't ...
0
votes
1answer
18 views
Understanding postgres prompts
What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.
0
votes
1answer
66 views
Restore postgres in command line on ubuntu failed
This question may be asked many times. We are trying to move a postgres db from windows (psql 9.3) to ubuntu runing postgres 9.3. Here is what we did:
In windows, create backup for db cis with ...
0
votes
0answers
18 views
Connecting to postgres server from remote client gives error
I need advice of the following please.
I can login to PGAdminIII from my workstation and read/write to my database. However trying to login from the command line gives me an error.
After entering ...
1
vote
1answer
37 views
Is there a way to define macros/alias in psql?
It is somewhat annoying to type EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in psql tool every time you tune queries.
So, is there a way to define an aliases (autocompletable ones would be very nice) in psql ...
1
vote
1answer
31 views
How do I automatically set meta commands when starting psql?
When I start a psql session, I would like certain meta commands to already be set.
For instance:
\x auto
\set ON_ERROR_STOP on
I was hoping to find an rc or config file but couldn't. The command ...
2
votes
1answer
53 views
How to Substitute Variables in PostgreSQL
The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL?
INSERT INTO control_threshold (
threshold_id, group_name, description, sql, ...
5
votes
2answers
3k views
How to turn off header only in psql (postgresql)
I'm using PostgreSQL 9.1.X
I am trying to build psql script to print results without a header but including a footer.
http://www.postgresql.org/docs/9.1/static/app-psql.html
From the document ...
1
vote
0answers
79 views
Is there a psql equivalent of bash's reverse-search-history?
I am very fond of bash's reverse-search-history (C-r) (command-line feature):
Search backward starting at the current line and moving โupโ through the history as necessary. This is an incremental ...
177
votes
6answers
306k 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 ...
1
vote
2answers
226 views
psql: id SERIAL PRIMARY KEY not starting with 1?
This is something I don't understand, can somebody explain?
I create a table
CREATE TABLE cali (id SERIAL PRIMARY KEY,
ALK_from char,
ALK_to char,
AND_from char,
AND_to char);
Then ...
0
votes
0answers
43 views
Postgres 9.2 on Windows
Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
1
vote
1answer
34 views
Understanding the output of \d <table> in psql
Say I run the following command:
\d mtb.big_table
I get the output shown below. My questions are:
What are column modifiers? (what does default now() mean?)
Under Indexes, Why do some indices ...
1
vote
1answer
1k views
Cannot start psql with user postgres โCould not change directory to /home/user โ
I did a silly mistake. I wanted to set postgres permissions to a folder for exporting data from my database. So I set something like sudo chmod 777 -u postgres /home/user and then my home partition ...
1
vote
1answer
91 views
Listing all databases in PostgreSQL [closed]
I have a basic question about PostgreSQL:
When connecting to my database server via pgadmin3, I can see that there are multiple databases.
For example, let's say I can see "test", "test1", and ...
0
votes
0answers
153 views
Unable to push table from pgadmin to heroku
I have tried https://devcenter.heroku.com/articles/heroku-postgresql#local-setup but
heroku pg:psql
keeps returning "not found" heroku pg:psql app::databaseName returns The local psql command could ...
6
votes
3answers
16k 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:
...
3
votes
1answer
5k views
Usage of COPY FROM STDIN in postgres
I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org.
I understand the commands, since I previously ...
2
votes
1answer
902 views
Postgres Dump To TXT File
Is there a way to convert a Postgres dump file to a text file?
I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text.
...
0
votes
2answers
461 views
Recursive update for tree structure in PostgreSQL
I have the following structure of table.
id chNum parentid
--- ------ ---------
1 1 NULL
2 1.1 1
3 1.1.1 2
4 2 ...
6
votes
2answers
18k 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 ...
2
votes
1answer
14k views
How to import a.csv file into a postgresql database using \copy?
I am trying to import a .csv file into a postgresql database. I am using pgadmin III for that on a win7 machine.
My query looks like that:
\COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, ...
2
votes
1answer
52 views
psql coloured prompt misbehaving
In theory, there is a possibility to use coloured prompts in psql. I decided (not the first time :) that I need them, so I thought I would give them a try. So I have in my .psqlrc the following:
...
1
vote
1answer
122 views
Postgresql socket not listening on the right one
I'm trying to get my postgresql to work.
It is clearly running, according to: ps aux | grep postgres | grep -v grep
I am getting -
username 5781 0.0 0.0 2441364 524 ?? Ss ...
0
votes
2answers
168 views
Storing data in PostgreSQL: One table or two?
I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month.
Question: Because every product can have different ...
2
votes
1answer
101 views
Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?
I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache ...
1
vote
1answer
69 views
Hiding variable input in psql
I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end ...
2
votes
0answers
608 views
Is there a Postgres admin GUI that can execute a master file containing multiple sql files
I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.
example: ...
1
vote
3answers
696 views
psql asks for password, CREATE USER didn't specify one
I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER ...
3
votes
1answer
350 views
Variable substitution in psql when using \copy
I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that:
The ...
1
vote
1answer
73 views
There is an equivalent of login.sql on PostgresSQL?
When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed.
It's usefull to set preferences...
There is an equivalent ...
0
votes
0answers
154 views
Postgres DB - Commit xlog files after server crash
After a server crash, it would seem that all transactions going back a number of months are not showing up in the live Postgres DB. Investigation has found all xlog files that does contain the data ...
3
votes
1answer
149 views
How to prevent query execution after exiting the \e editor in psql?
Is there an option that can change the "exit external editor -> query runs automatically" behaviour of the psql \e switch ? (Other than intentionally mangling the query syntax).
1
vote
1answer
1k views
restore compressed dump query in postgresql asks password
I'm using ubuntu OS and I took backup using
sudo -u postgres pg_dump dbname | gzip > filename.gz
in the file path as...
/home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname ...
3
votes
1answer
498 views
Getting SQLSTATE in psql error messages
I have the following problem.
SELECT * FROM tgvbn();
ERROR: function tgvbn() does not exist
LINE 1: SELECT * FROM tgvbn();
^
HINT: No function matches the given name and ...
1
vote
1answer
2k views
Drop multiple databases in one script
I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
2
votes
2answers
1k views
How to execute a query from psql without waiting for the result?
My query (to create a new table from existing table) takes a very long time. So I've set up a remote database in my office - more RAM there.
I can connect to my database from home as usual with psql. ...
4
votes
1answer
361 views
PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED
I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables:
psql -U usernamefoo databasenamefoo -f dml_script.sql
The table constraints have ...
2
votes
1answer
1k 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 > ...
1
vote
1answer
390 views
PostgreSQL psql - not working but no errors [closed]
I'm a complete noob to PostgreSQL, so I'm probably missing something completely obvious.
Connected to my local db via psql on terminal. Any command I run to make db changes, or even a simple select ...
3
votes
2answers
1k views
How to conditionally stop a psql script (based on a variable value)?
Let's consider the following example (from the start of a psql script):
\c :db_to_run_on
TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there
Now if ...
2
votes
1answer
2k views
PostgreSQL: Change default port used by utilites like psql, createdb, etc
I have 2 instances of PostgreSQL running on some servers. One on the default port (5432) and the other instance on port 5433. Some users (and processes) only need access to the second instance (5433) ...
