Tagged Questions
209
votes
12answers
101k views
Insert, on duplicate update in PostgreSQL?
Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax:
INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, ...
197
votes
10answers
99k views
save (postgres) sql output to 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.
179
votes
6answers
134k views
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:
...
144
votes
22answers
11k views
Use email address as primary key?
Is email address a bad candidate for primary when compared to auto incrementing numbers?
Our web application needs the email address to be unique in the system. So, I thought of using email address ...
108
votes
1answer
37k views
postgres: upgrade a user to be a superuser? [closed]
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 ...?
Thanks for your help.
97
votes
3answers
236k 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 ...
88
votes
10answers
95k 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 ...
83
votes
12answers
47k views
Which is better? Performing calculations in sql or in your application [closed]
shopkeeper table has following fields:
id (bigint),amount (numeric(19,2)),createddate (timestamp)
Let's say, I have the above table. I want to get the records for yesterday and
generate a report ...
73
votes
4answers
26k 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?
62
votes
3answers
26k views
How do I query using fields inside the new PostgreSQL JSON datatype?
I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2.
Specifically, given a series of JSON records:
[
{name: "Toby", occupation: "Software Engineer"},
{name: ...
61
votes
2answers
10k views
Optimise PostgreSQL for fast testing
I am switching to PostgreSQL from SQLite for a typical Rails application.
The problem is that running specs became slow with PG.
On SQLite it took ~34 seconds, on PG it's ~76 seconds which is more ...
61
votes
5answers
25k 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 ...
51
votes
14answers
38k views
Is it possible to make a recursive SQL query?
I have a table similar to this:
CREATE TABLE example (
id integer primary key,
name char(200),
parentid integer,
value integer);
I can use the parentid field to arrange data into a tree ...
47
votes
4answers
33k views
Copy a table (including indexes) in postgres
I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can't delete data from ...
46
votes
10answers
42k views
How do I UPDATE a row in a table or INSERT it if it doesn't exist?
I have the following table of counters:
CREATE TABLE cache (
key text PRIMARY KEY,
generation int
);
I would like to increment one of the counters, or set it to zero if the corresponding ...
43
votes
4answers
77k 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 ...
42
votes
4answers
64k views
Set auto increment primary key in Postgresql
I have a table in PostgreSQL with 22 columns, and I want to add an auto increment primary key.
I tried to create a column called id of type bigserial but pgadmin responds with an error:
ERROR: ...
42
votes
9answers
17k views
postgreSQL group by different from mysql?
I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by:
ERROR: column "XYZ" must ...
40
votes
12answers
28k views
Postgres: SQL 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.
39
votes
5answers
23k views
Best practices for SQL varchar column length
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 ...
38
votes
7answers
31k views
Show which columns an index is on in PostgreSQL
I would like to get the columns that an index is on in PostgreSQL.
In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column.
mysql> show indexes from foos;
...
36
votes
13answers
31k views
How to delete duplicate entries?
I have to add a unique constraint to an existing table. This is fine except that the table has millions of rows already, and many of the rows violate the unique constraint I need to add.
What is the ...
36
votes
7answers
38k views
How can I add a column to a Postgresql database that doesn't allow nulls?
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 ...
34
votes
5answers
36k views
Possible to perform cross-database queries with postgres?
I'm going to guess that the answer is no based on the below error message (and this Google result), but is there anyway to perform a cross-database query using Postgres?
databaseA=# select * from ...
33
votes
13answers
4k views
How to filter SQL results in a has-many-through relation
Assuming I have the tables student, club, and student_club:
student {
id
name
}
club {
id
name
}
student_club {
student_id
club_id
}
I want to know how to find all students ...
33
votes
3answers
20k views
Generate a random number in the range 1 - 10
Since my approach for a test query which I worked on in this question did not work out, I'm trying something else now. Is there a way to tell pg's random() function to get me only numbers between 1 ...
32
votes
6answers
30k views
List stored functions using a table in PostgreSQL
Just a quick and simple question: in PostgreSQL, how do you list the names of all stored functions/stored procedures using a table using just a SELECT statement, if possible? If a simple SELECT is ...
32
votes
1answer
13k views
SQL, Postgres OIDs, What are they and why are they useful?
I am looking at some PostgreSQL table creation and I stumbled upon this:
CREATE TABLE (
...
) WITH ( OIDS = FALSE );
I read the documentation provided by postgres and I know the concept of object ...
32
votes
4answers
13k views
Grouped LIMIT in PostgreSQL: show the first N rows for each group?
I need to take the first N rows for each group, ordered by custom column.
Given the following table:
db=# SELECT * FROM xxx;
id | section_id | name
----+------------+------
1 | 1 | A
2 ...
31
votes
5answers
61k 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 ...
31
votes
4answers
7k 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 ...
30
votes
9answers
53k views
How do you use script variables in PostgreSQL?
In MS SQL Server, I create my scripts to use customizable variables:
DECLARE @somevariable int
SELECT @somevariable = -1
INSERT INTO foo VALUES ( @somevariable )
I'll then change the value of ...
29
votes
7answers
40k views
How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
In postgres I have a table with a varchar column. The data is supposed to be integers and I need it in iteger type in a query. Some values are empty strings.
The following:
SELECT myfield::integer ...
29
votes
2answers
104k views
postgresql: INSERT INTO … (SELECT * …)
I'm not sure if its standard SQL:
INSERT INTO tblA
(SELECT id, time
FROM tblB
WHERE time > 1000)
What I'm looking for is: what if tblA and tblB are in different DB Servers.
Does ...
28
votes
5answers
38k views
How to declare a variable in a PostgreSQL query
This probably sounds like a really stupid question, but 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 *
...
28
votes
5answers
26k views
How do I force Postgres to use a particular index?
How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan?
26
votes
5answers
42k views
Return setof record (virtual table) from function
I need a Postgres function to return a virtual table (like in Oracle) with custom content. The table would have 3 columns and an unknown number of rows.
I just couldn't find the correct syntax on the ...
26
votes
3answers
8k views
“ORDER BY … USING” clause in PostgreSQL
The ORDER BY clause is decribed in the PostgreSQLdocumentation as:
ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]
Can someone give me some examples how to ...
25
votes
2answers
12k views
How do I get the MIN() of two fields in Postgres?
Let's say I have a table like this:
name | score_a | score_b
-----+---------+--------
Joe | 100 | 24
Sam | 96 | 438
Bob | 76 | 101
... | ... | ...
I'd like to select the ...
24
votes
2answers
25k views
How can you get the active users connected to a postgreSQL database via SQL?
How can you get the active users connected to a postgreSQL database via SQL? This could be the userid's or number of users.
24
votes
6answers
21k views
PostgreSQL - fetch the row which has the Max value for a column
I'm dealing with a Postgres table (called "lives") that contains records with columns for time_stamp, usr_id, transaction_id, and lives_remaining. I need a query that will give me the most recent ...
23
votes
4answers
15k 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: ...
23
votes
3answers
18k views
PostgreSQL wildcard LIKE for any of a list of words
I have a simple list of ~25 words. I have a varchar field in PostgreSQL, let's say that list is ['foo', 'bar', 'baz']. I want to find any row in my table that has any of those words. This will work, ...
23
votes
2answers
22k views
Update or Insert (multiple rows and columns) from subquery in PostgreSQL
I'm trying to do something like this in postgres:
UPDATE table1 SET (col1, col2) = (SELECT col2, col3 FROM othertable WHERE othertable.col1 = 123);
INSERT INTO table1 (col1, col2) VALUES (SELECT ...
21
votes
7answers
31k views
List all sequences in a Postgres db 8.1 with SQL
I'm converting a db from postgres to mysql.
Since i cannot find a tool that does the trick itself, i'm going to convert all postgres sequences to autoincrement ids in mysql with autoincrement value.
...
8
votes
3answers
45k views
SQL JOIN and different types of JOINs
I have been going through many threads on SO and some other forums. So I thought I would summarize "What is SQL JOIN?" and "What are different types of SQL JOINs?".
5
votes
4answers
6k views
IN Clause with NULL or IS NULL
Postgres is the database
Can I use a NULL value for a IN clause? example:
SELECT *
FROM tbl_name
WHERE id_field IN ('value1', 'value2', 'value3', NULL)
I want to limit to these four values.
I ...
2
votes
2answers
74 views
how does oracle makes 1 million insertion in 1 second
Few years ago, I found oracle can achieve 1 million insertion in 1 second, first let me explain this, I remember in SQL/PLUS interactive command line
set timing on
create table tbl as
select ROWNUM ...
2
votes
2answers
30 views
PostgreSQL: joining arrays within group by clause
We have a problem grouping arrays into a single array.
We want to join the values from two colums into one single array and aggregate these arrays of multiple rows.
Given the following input:
| id | ...
0
votes
2answers
18 views
Improving App Speed with a Second Serial column in PostgreSQL
Edit: (Explanation added)
I have a table that stores some data, it has the structure indicated below.
id tparti_id orde desc
1 1 10 One thing
2 1 20 Another ...
