Questions tagged [postgresql]
PostgreSQL is an open-source, relational database management system (RDBMS) available for all major platforms, including Linux, UNIX, Windows and OS X. Mention your version of Postgres when asking questions. Consider dba.stackexchange.com for questions concerning the administration or advanced features.
postgresql
177,405
questions
2503
votes
29
answers
2.9m
views
How to show tables in PostgreSQL?
What's the equivalent to show tables (from MySQL) in PostgreSQL?
- 269k
2193
votes
25
answers
1.5m
views
PostgreSQL "DESCRIBE TABLE"
How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL with psql command?
- 23.3k
2018
votes
21
answers
1.8m
views
Select first row in each GROUP BY group?
I'd like to select the first row of each set of rows grouped with a GROUP BY.
Specifically, if I've got a purchases table that looks like this:
SELECT * FROM purchases;
My Output:
id
customer
total
...
- 153k
1967
votes
7
answers
759k
views
How to exit from PostgreSQL command line utility: psql
What command or short key can I use to exit the PostgreSQL command line utility psql?
- 22k
1881
votes
31
answers
2.7m
views
How can I change a PostgreSQL user password?
How do I change the password for a PostgreSQL user?
- 27.7k
1649
votes
34
answers
1.4m
views
How can I drop all the tables in a PostgreSQL database?
How can I drop all tables in PostgreSQL, working from the command line?
I don't want to drop the database itself, just all tables and all the data in them.
- 92.6k
1537
votes
16
answers
1.2m
views
How to switch databases in psql?
In MySQL, I used use database_name;
What's the psql equivalent?
- 265k
1360
votes
24
answers
1.1m
views
Which version of PostgreSQL am I running?
I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server ...
- 39.4k
1213
votes
28
answers
1.2m
views
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
I'm getting the error:
FATAL: Peer authentication failed for user "postgres"
when I try to make postgres work with Rails.
Here's my pg_hba.conf, my database.yml, and a dump of the full trace.
I ...
- 12.4k
1186
votes
38
answers
1.3m
views
How can I start PostgreSQL server on Mac OS X?
Final update:
I had forgotten to run the initdb command.
By running this command
ps auxwww | grep postgres
I see that postgres is not running
> ps auxwww | grep postgres
remcat 1789 0....
1127
votes
21
answers
954k
views
Save PL/pgSQL output from PostgreSQL to a CSV file
What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?
I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
- 39.4k
1076
votes
18
answers
1.4m
views
PostgreSQL error: Fatal: role "username" does not exist
I'm setting up my PostgreSQL 9.1. I can't do anything with PostgreSQL: can't createdb, can't createuser; all operations return the error message
Fatal: role h9uest does not exist
h9uest is my ...
- 11.4k
1031
votes
13
answers
592k
views
Difference between text and varchar (character varying)
What's the difference between the text data type and the character varying (varchar) data types?
According to the documentation
If character varying is used without length specifier, the type ...
- 134k
1019
votes
25
answers
656k
views
psql: FATAL: database "<user>" does not exist
I'm using Postgres.app for Mac. I've used it in the past on other machines but it's giving me some trouble when installing on my MacBook. I've installed the application and I ran:
psql -h localhost
...
- 12k
992
votes
51
answers
436k
views
Can't find the 'libpq-fe.h header when trying to install pg gem
I am using the Ruby on Rails 3.1 pre version. I like to use PostgreSQL, but the problem is installing the pg gem. It gives me the following error:
$ gem install pg
Building native extensions. This ...
- 11.4k
915
votes
22
answers
546k
views
Creating a copy of a database in PostgreSQL
What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?
- 21.5k
853
votes
12
answers
655k
views
How to drop a PostgreSQL database if there are active connections to it?
I need to write a script that will drop a PostgreSQL database. There may be a lot of connections to it, but the script should ignore that.
The standard DROP DATABASE db_name query doesn't work when ...
- 13.1k
820
votes
18
answers
670k
views
How to do an update + join in PostgreSQL?
Basically, I want to do this:
update vehicles_vehicle v
join shipments_shipment s on v.shipment_id=s.id
set v.price=s.price_per_vehicle;
I'm pretty sure that would work in MySQL (my background)...
- 279k
808
votes
18
answers
716k
views
Insert, on duplicate update in PostgreSQL?
Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax:
INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, ...
- 110k
797
votes
21
answers
1.2m
views
How to import CSV file data into a PostgreSQL table
How can I write a stored procedure that imports data from a CSV file and populates the table?
- 7,979
786
votes
18
answers
1.3m
views
Run a PostgreSQL .sql file using command line arguments
I have some .sql files with thousands of INSERT statements in them and need to run these inserts on my PostgreSQL database in order to add them to a table. The files are that large that it is ...
- 12.3k
769
votes
8
answers
743k
views
Insert text with single quotes in PostgreSQL
I have a table test(id,name).
I need to insert values like: user's log, 'my user', customer's.
insert into test values (1,'user's log');
insert into test values (2,''my users'');
insert into ...
- 9,873
751
votes
36
answers
810k
views
psql: FATAL: role "postgres" does not exist
I'm a postgres novice.
I installed the postgres.app for mac. I was playing around with the psql commands and I accidentally dropped the postgres database. I don't know what was in it.
I'm ...
- 10.7k
739
votes
12
answers
433k
views
Postgres: upgrade a user to be a superuser?
In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons.
# alter user myuser ...?
- 10.7k
738
votes
36
answers
456k
views
How to reset Postgres' primary key sequence when it falls out of sync?
I ran into the problem that my primary key sequence is not in sync with my table rows.
That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial ...
- 32.9k
719
votes
13
answers
1.1m
views
What is the format for the PostgreSQL connection string / URL?
What is the format for the PostgreSQL connection string (URL postgres://...) when the host is not the localhost?
- 7,287
712
votes
12
answers
762k
views
How do I specify a password to 'psql' non-interactively?
I am trying to automate database creation process with a shell script and one thing I've hit a road block with passing a password to psql.
Here is a bit of code from the shell script:
psql -U $...
- 15.5k
711
votes
22
answers
1.2m
views
Import SQL dump into PostgreSQL database
We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site.
Now, I'm trying to set this up on a local WAMP server to test this.
The only problem is that I don'...
- 8,582
696
votes
11
answers
758k
views
What's the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
I'm switching from MySQL to PostgreSQL and I was wondering how can I have an INT column with AUTO INCREMENT. I saw in the PostgreSQL docs a datatype called SERIAL, but I get syntax errors when using ...
- 25k
686
votes
21
answers
673k
views
Postgres: INSERT if does not exist already
I'm using Python to write to a postgres database:
sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred + ", '" + hundred_slug + "', " + status + ");"
cursor....
- 92.6k
664
votes
12
answers
945k
views
Generating a UUID in Postgres for Insert statement?
My question is rather simple. I'm aware of the concept of a UUID and I want to generate one to refer to each 'item' from a 'store' in my DB with. Seems reasonable right?
The problem is the following ...
- 12.8k
646
votes
66
answers
607k
views
Postgres could not connect to server
After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install it again, but it didn't work as well.
This is the error message. (I also got this ...
- 6,623
646
votes
13
answers
535k
views
Copying PostgreSQL database to another server
I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?
- 13.4k
631
votes
14
answers
564k
views
How do you create a read-only user in PostgreSQL?
I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be:
GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';
What is the ...
- 59.3k
612
votes
7
answers
747k
views
"use database_name" command in PostgreSQL
I am beginner to PostgreSQL.
I want to connect to another database from the query editor of Postgres - like the USE command of MySQL or MS SQL Server.
I found \c databasename by searching the ...
- 6,233
610
votes
31
answers
827k
views
Where does PostgreSQL store configuration/conf files?
I have recently installed PostgreSQL on Ubuntu with the EnterpriseDB package. I can connect to the database locally, but I can't configure it because I can't find config files. I searched through ...
- 11.2k
598
votes
16
answers
568k
views
How to make "case-insensitive" query in Postgresql?
Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result.
SELECT id FROM groups where name='administrator'
SELECT id FROM groups ...
- 21.9k
596
votes
31
answers
1.1m
views
'password authentication failed for user "postgres"'
I have installed PostgreSQL 8.4, Postgres client and Pgadmin 3. Authentication failed for user "postgres" for both console client and Pgadmin. I have typed user as "postgres" and password "postgres", ...
- 30.6k
593
votes
20
answers
542k
views
How do you find the row count for all your tables in Postgres
I'm looking for a way to find the row count for all my tables in Postgres. I know I can do this one table at a time with:
SELECT count(*) FROM table_name;
but I'd like to see the row count for all ...
- 13.4k
591
votes
5
answers
1.3m
views
How do I (or can I) SELECT DISTINCT on multiple columns?
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The ...
- 34.1k
577
votes
19
answers
812k
views
Connecting to Postgresql in a docker container from outside
I have Postgresql on a server in a docker container. How can I connect to it from the outside, that is, from my local computer? What setting should I apply to allow that?
- 5,993
570
votes
25
answers
831k
views
Kill a postgresql session/connection
How can I kill all my postgresql connections?
I'm trying a rake db:drop but I get:
ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the ...
- 18.1k
562
votes
36
answers
1.5m
views
Restore a postgres backup file using the command line?
Locally, I use pgadmin3. On the remote server, however, I have no such luxury.
I've already created the backup of the database and copied it over, but is there a way to restore a backup from the ...
- 10.5k
556
votes
8
answers
726k
views
updating table rows in postgres using subquery
I have this table in a postgres 8.4 database:
CREATE TABLE public.dummy
(
address_id SERIAL,
addr1 character(40),
addr2 character(40),
city character(25),
state character(2),
zip character(...
- 6,745
549
votes
6
answers
604k
views
How to list the tables of schemas in PostgreSQL?
When I do a \dt in psql I only get a listing of tables in the current schema (public by default).
How can I get a list of all tables in all schemas or a particular schema?
- 62.8k
545
votes
13
answers
500k
views
Postgres DB Size Command
What is the command to find the size of all the databases?
I am able to find the size of a specific database by using following command:
select pg_database_size('databaseName');
- 6,336
522
votes
27
answers
449k
views
PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
How do I modify the owner of all tables in a PostgreSQL database?
I tried ALTER TABLE * OWNER TO new_owner but it doesn't support the asterisk syntax.
- 9,188
510
votes
9
answers
219k
views
Explanation of JSONB introduced by PostgreSQL
PostgreSQL just introduced JSONB in version 9.4, and it's already trending on hacker news. How is it different from Hstore and JSON previously present in PostgreSQL?
What are its advantages and ...
- 6,384
510
votes
13
answers
396k
views
Best way to select random rows PostgreSQL
I want a random selection of rows in PostgreSQL, I tried this:
select * from table where random() < 0.01;
But some other recommend this:
select * from table order by random() limit 1000;
I have a ...
- 8,252
503
votes
12
answers
475k
views
Can't subtract offset-naive and offset-aware datetimes
I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age.
The problem I'm having is that both datetime....
- 25k