Skip to main content

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
1565 votes
9 answers

How do I list all databases and tables using psql?

When I log in with psql --username=postgres, how do I list all databases and tables? I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, ...
Jonas's user avatar
  • 33.3k
560 votes
5 answers

List all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with ...
Stephane Rolland's user avatar
438 votes
5 answers

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
Stéphane's user avatar
  • 5,625
402 votes
4 answers

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
Jonas's user avatar
  • 33.3k
373 votes
12 answers

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

I am trying to run a website sent to me but after doing so this error appeared connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", ...
Jin's user avatar
  • 3,843
353 votes
3 answers

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?
Jonas's user avatar
  • 33.3k
309 votes
10 answers

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 ...
pedrosanta's user avatar
  • 3,403
249 votes
6 answers

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I ...
Alex's user avatar
  • 2,685
232 votes
3 answers

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?
GSto's user avatar
  • 2,423
214 votes
5 answers

Granting access to all tables for a user

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all ...
PlaidFan's user avatar
  • 2,245
203 votes
5 answers

How to get the name of the current database from within PostgreSQL?

Using \c <database_name> in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERROR: ...
Amelio Vazquez-Reina's user avatar
203 votes
17 answers

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. ...
randomguy's user avatar
  • 2,171
200 votes
14 answers

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * -...
Adam Matan's user avatar
  • 11.8k
167 votes
5 answers

How to list all constraints of a table in PostgreSQL?

How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
Thirumal's user avatar
  • 2,468
157 votes
3 answers

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
Manuel Leduc's user avatar
  • 1,681
148 votes
4 answers

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
Stephane Rolland's user avatar
146 votes
8 answers

Pattern matching with LIKE, SIMILAR TO or regular expressions

I had to write a simple query where I go looking for people's name that start with a B or a D: SELECT FROM spelers s WHERE LIKE 'B%' OR LIKE 'D%' ORDER BY 1 I was wondering if ...
Lucas Kauffman's user avatar
144 votes
3 answers

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
Jonas's user avatar
  • 33.3k
143 votes
15 answers

PostgreSQL not running on Mac

The error in its entirety reads: psql: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?...
Michael P.'s user avatar
  • 2,101
143 votes
7 answers

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} ...
Majid Azimi's user avatar
  • 2,281
143 votes
1 answer

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
vastlysuperiorman's user avatar
140 votes
8 answers

How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string ('foo, ...
Christoph's user avatar
  • 1,593
137 votes
5 answers

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
Joe's user avatar
  • 1,645
135 votes
3 answers

Default value for UUID column in Postgres

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
Basil Bourque's user avatar
135 votes
2 answers

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to and I ...
Jonas's user avatar
  • 33.3k
127 votes
3 answers

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
Luciano's user avatar
  • 1,721
124 votes
1 answer

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema:!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table inner ...
jackhammer013's user avatar
121 votes
3 answers

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...'s user avatar
  • 1,311
116 votes
11 answers

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: ...
Jonas's user avatar
  • 33.3k
110 votes
2 answers

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ------------...
JohnMerlino's user avatar
  • 1,789
110 votes
3 answers

What is the search_path for a given database and user?

I can see the current search_path with: show search_path ; And I can set the search_path for the current session with: set search_path = "$user", public, postgis; As well, I can permanently set ...
user664833's user avatar
  • 1,909
103 votes
7 answers

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
jd.'s user avatar
  • 1,132
100 votes
3 answers

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
Stéphane's user avatar
  • 5,625
95 votes
5 answers

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
codecool's user avatar
  • 2,003
94 votes
4 answers

How to drop all connections to a specific database without stopping the server?

I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases. How can I do ...
sorin's user avatar
  • 1,242
93 votes
4 answers

Are views harmful for performance in PostgreSQL?

The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6): The danger with using views is filtering a query against a view, expecting to read a very ...
ARX's user avatar
  • 1,439
93 votes
4 answers

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column? Edit: Not a dupe of: ...
Daniel Serodio's user avatar
89 votes
6 answers

Do stored procedures prevent SQL injection?

Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection ...
Am1rr3zA's user avatar
  • 1,523
88 votes
2 answers

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
GuidoS's user avatar
  • 1,047
87 votes
4 answers

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
myahya's user avatar
  • 971
85 votes
10 answers

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don'...
Jonas's user avatar
  • 33.3k
84 votes
4 answers

PostgreSQL: Remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
Marcus Junius Brutus's user avatar
84 votes
2 answers

Understanding "bitmap heap scan" and "bitmap index scan"

I'll try to explain my misunderstandings by the following example. I didn't understand fundamentals of the Bitmap Heap Scan Node. Consider the query SELECT customerid, username FROM customers WHERE ...
St.Antario's user avatar
  • 1,245
83 votes
6 answers

What is the best way to store an email address in PostgreSQL?

What would be the right datatype to store email addresses in PostgreSQL? I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
Adam Matan's user avatar
  • 11.8k
78 votes
6 answers

How do I efficiently get "the most recent corresponding row"?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
Tom Ellis's user avatar
  • 1,609
75 votes
7 answers

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...;...
Rob Bednark's user avatar
  • 2,203
74 votes
7 answers

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in the SO post Ignoring time zones altogether in Rails and ...
Marcus Junius Brutus's user avatar
74 votes
4 answers

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
Derek Downey's user avatar
  • 23.5k
74 votes
3 answers

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
Jonas's user avatar
  • 33.3k
72 votes
9 answers

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
AliBZ's user avatar
  • 1,807

2 3 4 5