I don’t know quite how to phrase this so please help me with the title as well. 🙂 I have two tables. Let’s call them A and B. The B table has a a_id foreign key that points at A.id. Now I would like to write a SELECT statement that fetches all A records, with an additional column containing the
Tag: postgresql
How to extract year and month from date in PostgreSQL without using to_char() function?
I want to select sql: SELECT “year-month” from table group by “year-month” AND order by date, where year-month – format for date “1978-01″,”1923-12”. select to_char of couse work, but not “right” order: Answer e.g. http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html
SQL: Advantages of an ENUM vs. a one-to-many relationship?
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this: But the same thing can also be shown using a user-defined type / ENUM: (Example shown using PostgreSQL, but other RDBMS’s have similar syntax) The biggest disadvantage I see to using an ENUM is that it’s more
How to drop multiple tables in PostgreSQL using a wildcard
When working with partitions, there is often a need to delete all partitions at once. However Does not work. (The wildcard is not respected). Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard? Answer Use a comma separated list: If you realy need a footgun, this one will do it’s
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 …
Variable value assignment using RETURNING clause
I try to do this, but it’s a syntax error, what am I doing wrong? my table: Answer You need to use the INTO clause in the RETURNING to set the value being returned into your variable: You also need to specify the data type of your variable; I’m glad to see postgresql supports %TYPE and %ROWTYPE.
Query to group by maximum depth of PostgreSQL Ltree?
I want to query for all products with the name “Shania Twain”, but I want group them only by the tree with the deepest nlevel. Assuming I have a table like the following with the name categories So, for example, The problem lies with the HAVING clause requiring a boolean expression. The clause MAX(nlevel(cat.tree)) will return an integer. How do
How to show row numbers in PostgreSQL query?
I’d like to show the observation number for each record returned by a PostgreSQL query. I think in 8.4 windowing functions can perform this capability.
Slow simple update query on PostgreSQL database with 3 million rows
I am trying a simple UPDATE table SET column1 = 0 on a table with about 3 million rows on Postegres 8.4 but it is taking forever to finish. It has been running for more than 10 min. Before, I tried to run a VACUUM and ANALYZE commands on that table and I also tried to create some indexes (although
PostgreSQL Index Usage Analysis
Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the “profiler” tool for SQLServer, but I’m not aware of a similar tool included with Postgres. Answer I like this to find missing indexes: This checks if there