All Questions

Tagged with
Filter by
Sorted by
Tagged with
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 ...
user avatar
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.
user avatar
  • 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 ...?
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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');
user avatar
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), ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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;
user avatar
  • 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: ...
user avatar
  • 3,719

15 30 50 per page
1
2 3 4 5
2867