All Questions
Tagged with sql postgresql
42,999
questions
1730votes
20answers
1.5mviews
Select first row in each GROUP BY group?
As the title suggests, 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 ...
- 138k
1050votes
17answers
817kviews
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.
- 36.7k
702votes
8answers
374kviews
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.2k
601votes
7answers
524kviews
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 ...
- 8,073
489votes
5answers
1.0mviews
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 ...
- 30.7k
449votes
33answers
928kviews
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 ...
- 19k
431votes
10answers
320kviews
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');
- 5,062
426votes
6answers
474kviews
updating table rows in postgres using subquery
Using postgres 8.4, My goal is to update existing table:
CREATE TABLE public.dummy
(
address_id SERIAL,
addr1 character(40),
addr2 character(40),
city character(25),
state character(2),
...
- 5,275
420votes
14answers
369kviews
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 ...
- 6,228
418votes
12answers
293kviews
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 ...
- 7,172
408votes
8answers
163kviews
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?
- 12.3k
372votes
14answers
537kviews
How to 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 the ...
- 54.6k
365votes
4answers
627kviews
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 ...
- 5,493
333votes
1answer
158kviews
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;
- 6,733
332votes
17answers
274kviews
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: ...
- 3,719