> > > One Challenging Question for PostgreSQL Beginners [PostgreSQL Tutorials]

One Challenging Question for PostgreSQL Beginners [PostgreSQL Tutorials]

postgres-elephant-thinking-postgresql-questionApart from the usual PostgreSQL tutorials, where I myself explain the concepts, here we are gonna have a look at a funny question in PostgreSQL. Here I am not providing a ready made answer, instead you have to come up with your own answer and we will choose the best answer by voting.

The Problem Statement

Here, we have two tables named, user_details and cipher_table:

CREATE TABLE user_details
(
  user_id character varying NOT NULL,
  pass_enc character varying,
  created_time timestamp with time zone,
  CONSTRAINT user_details_pkey PRIMARY KEY (user_id)
)
WITH (
  OIDS=FALSE
);
CREATE TABLE cipher_table
(
  cipher_text character varying NOT NULL,
  plain_text character varying,
  CONSTRAINT cipher_table_pkey PRIMARY KEY (cipher_text)
)
WITH (
  OIDS=FALSE
);

The user_details table contains three columns:

  1. The user_id column holds the user ID of the generated users.
  2. pass_enc column contains the password for the user which is the md5 encoded string of concatenation of user_id and created_time (example: md5(‘2013-11-09 04:15:11.233-08User 1’))
  3. The created_time store the time of creation of the user

The cipher_table stores the password and corresponding plain text from which it is formed.

So, our challenge here is to insert 500 users dynamically with user_id like ‘User 1’, ‘User 2′,…’User 500’ and corresponding password in pass_enc (formed as explained above) and creation time in created_time column. In the same query we have to insert the passwords and corresponding plain text from which it is formed. The tables will look like below:

Capture
user_details table
cipher_table
cipher_table

Rules:

  1. The whole insertion process must be done in a single query. No user defined functions, no more than one semi column(;)
  2. The created_time must not be all same. It must be different in at least few rows.
  3. No hard-coding of values except ‘User ‘, 1, 500 and  timestamp
  4. Your query must be re-usable, that means, if you insert ‘User 1’ to ‘User 500’ at a time, you must also be able to insert ‘User 501’ to ‘User 1000’ using the same query.

So that’s it. Let’s see who gonna answer first!

The following two tabs change content below.

Vipin Raj

Vipin Raj is a software developer specialized in PostgreSQL Database and Data Modeling, the man behind technobytz and an IoT and Security enthusiast. Having 3+ years of experience in the IT industry, he is currently pursuing his masters in computer science and information security. He spend his free time writing blog posts with the intension of sharing his knowledge to the tech community.

4 thoughts on “One Challenging Question for PostgreSQL Beginners [PostgreSQL Tutorials]

  • December 8, 2013 at 2:27 am
    Permalink

    hi Raj,
    CTE is the answer! Every time you run this query it will add new 500 users with new IDs. I put my tables into “test” schema, so you have to do it with your tables too, to run this query.

    One of possible solutions:

    /*The whole insertion process must be done in a single query. No user defined functions, no more than one semi column(;)*/
    WITH countRows AS (
    SELECT COUNT(*) as rowCount FROM test.user_details -- Your query must be re-usable, that means, if you insert ‘User 1? to ‘User 500? at a time, you must also be able to insert ‘User 501? to ‘User 1000? using the same query.
    ),
    series AS (
    SELECT generate_series (countRows.rowCount +1,countRows.rowCount +500) rowNum, --No hard-coding of values except ‘User ‘, 1, 500 and timestamp
    clock_timestamp() as create_time --The created_time must not be all same. It must be different in at least few rows.
    FROM countRows
    ),
    create_users AS (
    INSERT INTO test.user_details (user_id, pass_enc, created_time)
    SELECT 'User'||series.rowNum series, md5(series.create_time||'User'||series.rowNum ), series.create_time --No hard-coding of values except ‘User ‘, 1, 500 and timestamp
    FROM series
    RETURNING *
    )
    INSERT INTO test.cipher_table (cipher_text, plain_text)
    SELECT pass_enc, created_time::text || user_id
    FROM create_users

    regards,
    Bartek

    Reply
    • December 8, 2013 at 4:19 pm
      Permalink

      Cool. It is a perfect solution with CTE.

      Reply
  • November 17, 2015 at 3:41 pm
    Permalink

    if a table have crores of row , how to get select A single whithin a second , when i select it takes 15 to 20 minuts

    Reply
    • November 18, 2015 at 6:35 pm
      Permalink

      With crores of rows, that too in temporary tables, 15 minutes seems to be less, unless you are running on a Super computer. 🙂

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *