Linked Questions

55
votes
3answers
284k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
32
votes
1answer
8k views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
15
votes
4answers
12k views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
15
votes
1answer
55k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
3
votes
2answers
19k views

Very slow execution of a simple query

I try to create a report for my data, but it is really slow on a big table. The table structure is: CREATE TABLE posts ( id serial NOT NULL, project_id integer, moderation character varying(...
5
votes
1answer
11k views

Improve UPDATE performance on big table

I'm using Postgres 9.5 on Amazon RDS (2vCPU, 8 GB RAM). I use pganalyze to monitor my performance. I have around 200K records in the database. In my Dashboard I see the following queries are taking ...
5
votes
2answers
926 views

How to make this query use my multicolumn index?

Currently, I have a view which is defined like this: View "public.customer_list" Column | Type | Modifiers | Storage | Description -----------+---------...
2
votes
1answer
2k views

Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

In an Twitter exchange, Simon West asks to Brandur, Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before ...
4
votes
2answers
424 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
1
vote
3answers
212 views

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
1
vote
1answer
110 views

RIGHT() function in postgresql 9.1 not behaving as anticipated?

The following is with regard to PostgresQL 9.1 : I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing ...