All Questions
Tagged with sql postgresql
49,620
questions
2006
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
...
- 152k
1122
votes
21
answers
941k
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.3k
757
votes
8
answers
724k
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,753
738
votes
12
answers
426k
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
737
votes
35
answers
448k
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.8k
583
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 ...
- 33.9k
548
votes
8
answers
705k
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,655
532
votes
12
answers
476k
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,186
499
votes
13
answers
387k
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,132
494
votes
34
answers
1.1m
views
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 20.5k
492
votes
14
answers
620k
views
How to log PostgreSQL queries?
How to enable logging of all SQL executed by PostgreSQL 8.3?
Edited (more info)
I changed these lines :
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
...
- 12.6k
488
votes
9
answers
219k
views
Postgres and Indexes on Foreign Keys and Primary Keys
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?
- 14k
481
votes
14
answers
430k
views
How to concatenate strings of a string field in a PostgreSQL 'group by' query?
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:
ID
COMPANY_ID
EMPLOYEE
1
1
Anna
2
1
Bill
3
2
Carol
4
2
Dave
and I wanted to ...
- 7,080
478
votes
17
answers
797k
views
Declare a variable in a PostgreSQL query
How do I declare a variable for use in a PostgreSQL 8.3 query?
In MS SQL Server I can do this:
DECLARE @myvar INT;
SET @myvar = 5/
SELECT * FROM somewhere WHERE something = @myvar;
How do I do ...
- 60.6k
465
votes
4
answers
914k
views
How to get the top 10 values in postgresql?
I have simple question:
I have a postgresql table: Scores(score integer).
How would I get the highest 10 scores the fastest?
UPDATE:
I will be doing this query multiple times and am aiming for the ...
- 6,623
459
votes
1
answer
248k
views
How to change a PG column to NULLABLE TRUE?
How can I accomplish this using Postgres? I've tried the code below but it doesn't work:
ALTER TABLE mytable ALTER COLUMN mycolumn BIGINT NULL;
- 8,163
421
votes
5
answers
580k
views
How to list active connections on PostgreSQL?
Is there a command in PostgreSQL to select active connections to a given database?
psql states that I can't drop one of my databases because there are active connections to it, so I would like to see ...
- 20.4k
417
votes
18
answers
414k
views
Reset auto increment counter in postgres
I would like to force the auto increment field of a table to some value, I tried with this:
ALTER TABLE product AUTO_INCREMENT = 1453
AND
ALTER SEQUENCE product RESTART WITH 1453;
ERROR: relation &...
- 4,641
414
votes
5
answers
207k
views
Create unique constraint with null columns
I have a table with this layout:
CREATE TABLE Favorites (
FavoriteId uuid NOT NULL PRIMARY KEY,
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
MenuId uuid
);
I want to create a unique ...
- 90.2k
393
votes
8
answers
815k
views
Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
I want to extract just the date part from a timestamp in PostgreSQL.
I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value.
For example, if I have ...
- 3,997
383
votes
4
answers
237k
views
In Postgresql, force unique on combination of two columns
I would like to set up a table in PostgreSQL such that two columns together must be unique. There can be multiple values of either value, so long as there are not two that share both.
For instance:
...
- 39.4k
374
votes
10
answers
286k
views
Update multiple rows in same query using PostgreSQL
I'm looking to update multiple rows in PostgreSQL in one statement. Is there a way to do something like the following?
UPDATE table
SET
column_a = 1 where column_b = '123',
column_a = 2 where ...
- 17.6k
365
votes
11
answers
531k
views
What's the fastest way to do a bulk insert into Postgres? [closed]
I need to programmatically insert tens of millions of records into a Postgres database. Presently, I'm executing thousands of insert statements in a single query.
Is there a better way to do this, ...
- 25.1k
362
votes
9
answers
470k
views
How to find duplicate records in PostgreSQL
I have a PostgreSQL database table called "user_links" which currently allows the following duplicate fields:
year, user_id, sid, cid
The unique constraint is currently the first field called "id", ...
- 6,747
357
votes
8
answers
339k
views
Best practices for SQL varchar column length [closed]
Every time is set up a new SQL table or add a new varchar column to an existing table, I am wondering one thing: what is the best value for the length.
So, lets say, you have a column called name of ...
- 10.8k
354
votes
9
answers
292k
views
Postgresql GROUP_CONCAT equivalent?
I have a table and I'd like to pull one row per id with field values concatenated.
In my table, for example, I have this:
TM67 | 4 | 32556
TM67 | 9 | 98200
TM67 | 72 | 22300
TM99 | 2 | 23009
TM99 ...
- 10.3k
351
votes
8
answers
422k
views
Postgres manually alter sequence
I'm trying to set a sequence to a specific value.
SELECT setval('payments_id_seq'), 21, true;
This gives an error:
ERROR: function setval(unknown) does not exist
Using ALTER SEQUENCE doesn't seem ...
- 27.3k
347
votes
19
answers
861k
views
Cannot simply use PostgreSQL table name ("relation does not exist")
I'm trying to run the following PHP script to do a simple database query:
$db_host = "localhost";
$db_name = "showfinder";
$username = "user";
$password = "password";
$dbconn = pg_connect("host=$...
- 5,100
347
votes
8
answers
356k
views
How can I add a column that doesn't allow nulls in a Postgresql database?
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet):
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL;
Each ...
- 16k
343
votes
10
answers
197k
views
How to use RETURNING with ON CONFLICT in PostgreSQL?
I have the following UPSERT in PostgreSQL 9.5:
INSERT INTO chats ("user", "contact", "name")
VALUES ($1, $2, $3),
($2, $1, NULL)
ON CONFLICT("user", "contact") DO ...
- 6,077
339
votes
10
answers
675k
views
How to set auto increment primary key in PostgreSQL?
I have a table in PostgreSQL with many columns, and I want to add an auto increment primary key.
I tried to create a column called id of type BIGSERIAL but pgadmin responded with an error:
ERROR: ...
- 12.5k
339
votes
8
answers
590k
views
How to round an average to 2 decimal places in PostgreSQL?
I am using PostgreSQL via the Ruby gem 'sequel'.
I'm trying to round to two decimal places.
Here's my code:
SELECT ROUND(AVG(some_column),2)
FROM table
I get the following error:
PG::Error: ...
- 3,923
339
votes
7
answers
356k
views
PostgreSQL DISTINCT ON with different ORDER BY
I want to run this query:
SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY purchases.purchased_at DESC
But I get this error:
...
- 5,176
335
votes
8
answers
668k
views
PostgreSQL: Give all permissions to a user on a PostgreSQL database
I would like to give a user all the permissions on a database without making it an admin.
The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same cluster so I ...
- 35.5k
332
votes
16
answers
351k
views
postgresql list and order tables by size
How can I list all the tables of a PostgreSQL database and order them by size?
- 11.9k
330
votes
31
answers
299k
views
How to list table foreign keys
Is there a way using SQL to list all foreign keys for a given table? I know the table name / schema and I can plug that in.
- 11.3k
329
votes
6
answers
661k
views
How to compare dates in datetime fields in Postgresql?
I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows).
I have a column in my table say update_date with type timestamp without timezone. Client can search ...
- 3,309
327
votes
5
answers
313k
views
ERROR: permission denied for sequence cities_id_seq using Postgres
I ran following sql script on my database:
create table cities (
id serial primary key,
name text not null
);
create table reports (
id serial primary key,
cityid integer not null references cities(...
- 3,626
325
votes
11
answers
533k
views
Best way to check for "empty or null value"
What is best way to check if value is null or empty string in Postgres sql statements?
Value can be long expression so it is preferable that it is written only once in check.
Currently I'm using:
...
- 27k
322
votes
5
answers
316k
views
What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
Since PostgreSQL came out with the ability to do LATERAL joins, I've been reading up on it since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall ...
- 16.6k
316
votes
7
answers
250k
views
PostgreSQL delete with inner join
DELETE B.*
FROM m_productprice B
INNER JOIN m_product C ON B.m_product_id = C.m_product_id
WHERE C.upc = '7094' AND B.m_pricelist_version_id = '1000020'
i am getting the following error ...
- 4,732
308
votes
6
answers
456k
views
PostgreSQL create table if not exists
In a MySQL script you can write:
CREATE TABLE IF NOT EXISTS foo ...;
... other stuff ...
and then you can run the script many times without re-creating the table.
How do you do this in PostgreSQL?
- 5,840
306
votes
8
answers
458k
views
Check if value exists in Postgres array
Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this:
select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int)
But I ...
- 7,138
300
votes
7
answers
343k
views
Fastest check if row exists in PostgreSQL
I have a bunch of rows that I need to insert into table, but these inserts are always done in batches. So I want to check if a single row from the batch exists in the table because then I know they ...
- 11.9k
299
votes
13
answers
329k
views
Export specific rows from a PostgreSQL table as INSERT SQL script
I have a database schema named: nyummy and a table named cimory:
create table nyummy.cimory (
id numeric(10,0) not null,
name character varying(60) not null,
city character varying(50) not null,...
- 8,926
298
votes
7
answers
321k
views
How to speed up insertion performance in PostgreSQL
I am testing Postgres insertion performance. I have a table with one column with number as its data type. There is an index on it as well. I filled the database up using this query:
insert into ...
- 64.3k
297
votes
3
answers
379k
views
IN vs ANY operator in PostgreSQL
What is the difference between IN and ANY operator in PostgreSQL?
The working mechanism of both seems to be the same. Can anyone explain this with an example?
- 10.5k
296
votes
5
answers
228k
views
How do I ALTER a PostgreSQL table and make a column unique?
I have a table in PostgreSQL where the schema looks like this:
CREATE TABLE "foo_table" (
"id" serial NOT NULL PRIMARY KEY,
"permalink" varchar(200) NOT NULL,
...
- 20.3k
290
votes
9
answers
609k
views
List all tables in postgresql information_schema
What is the best way to list all of the tables within PostgreSQL's information_schema?
To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table ...
- 19.8k
286
votes
3
answers
423k
views
How to alter a column's data type in a PostgreSQL table?
Entering the following command into a PostgreSQL interactive terminal results in an error:
ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11);
What is the correct command to alter the data type ...
- 21k