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
1537 votes
9 answers
3.2m views

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
  • 32.7k
542 votes
5 answers
1.1m views

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
418 votes
5 answers
790k views

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,345
389 votes
4 answers
897k views

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
  • 32.7k
363 votes
12 answers
1.2m views

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,743
346 votes
3 answers
215k 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?
Jonas's user avatar
  • 32.7k
301 votes
10 answers
959k 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 ...
pedrosanta's user avatar
  • 3,323
241 votes
6 answers
386k views

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,605
227 votes
3 answers
248k views

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,373
208 votes
5 answers
554k views

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,185
197 votes
17 answers
320k views

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,111
196 votes
5 answers
279k views

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
192 votes
12 answers
254k views

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.5k
157 votes
4 answers
250k views

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,338
155 votes
3 answers
167k views

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,661
145 votes
4 answers
161k views

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
144 votes
8 answers
231k views

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 s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering if ...
Lucas Kauffman's user avatar
139 votes
15 answers
345k views

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,061
139 votes
1 answer
165k views

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
139 votes
3 answers
196k views

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
  • 32.7k
137 votes
5 answers
155k views

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,631
136 votes
7 answers
284k 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} ...
Majid Azimi's user avatar
  • 2,201
134 votes
8 answers
432k views

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,533
133 votes
2 answers
275k views

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 currenttimestamp.com and timestamp.1e5b.de I ...
Jonas's user avatar
  • 32.7k
131 votes
3 answers
227k views

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
123 votes
3 answers
59k views

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,661
119 votes
1 answer
126k views

Postgres Count with different condition on the same query

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

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, ...
sudo.ie's user avatar
  • 1,281
113 votes
11 answers
349k 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: ...
Jonas's user avatar
  • 32.7k
104 votes
3 answers
202k views

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,819
100 votes
2 answers
104k views

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,689
96 votes
7 answers
139k views

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,062
95 votes
3 answers
468k views

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,345
93 votes
5 answers
41k views

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
  • 1,963
92 votes
4 answers
83k views

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,399
91 votes
3 answers
245k views

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,212
89 votes
6 answers
13k views

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
86 votes
2 answers
131k views

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,017
86 votes
4 answers
85k views

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
85 votes
10 answers
290k views

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
  • 32.7k
82 votes
4 answers
200k views

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
81 votes
4 answers
59k views

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
  • 911
80 votes
6 answers
76k views

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.5k
79 votes
2 answers
49k views

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,195
78 votes
6 answers
148k views

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,559
73 votes
4 answers
55k views

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.4k
73 votes
3 answers
43k views

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
  • 32.7k
72 votes
6 answers
187k views

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,103
71 votes
7 answers
129k views

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
70 votes
1 answer
87k views

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go. The application currently consists of a command ...
Jim Dennis's user avatar

1
2 3 4 5
332