Sql error 42703 ошибка столбец не существует

Wondering how to fix PostgreSQL Error code 42703? We can help you.

One of the most common error codes with the PostgreSQL database is 42703. It will be seen along with the error message “column does not exist”. This error indicates either that the requested column does not exist, or that the query is not correct.

Here at Bobcares, we often handle requests from our customers to fix similar PostgreSQL errors as a part of our Server Management Services. Today we will see how our support engineers fix this for our customers.

How to fix PostgreSQL Error code 42703

Often, the error is caused by a lack of quotes. We can add double quotes to the column name to fix this error.

For example:

We will try to run a simple select query:

SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMIT 10;

And get the following error:

ERROR: column return_part_i.cntrcttrmntnind does not exist LINE 1: SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMI... ^ HINT: Perhaps you meant to reference the column "return_part_i.CntrctTrmntnInd". SQL state: 42703 Character: 8

if we have a camel case in our column name we must ensure to wrap the column name with a double quote.

This can be done in the following way:

SELECT "CntrctTrmntnInd"  FROM return_part_i LIMIT 10;

PostgreSQL columns (object) names are case sensitive when specified with double quotes. Unquoted identifiers are automatically used as lowercase so the correct case sequence must be written with double quotes.

If we want a LIMIT in result we must use an order by

SELECT "CntrctTrmntnInd" FROM return_part_i ORDER BY "CntrctTrmntnInd" LIMIT 10;

When used with quotes, Postgresql is case sensitive regarding identifier names like table names and column names.
So a common issue that triggers this error is when we use the column name in our commands in any other cases other than that of the original one.

For instance, if the column name is “Price”, using “price” in the command can trigger the error.

Thus we need to make sure that the cases are correct.

[Need assistance? We can help you]

Conclusion

In short, we saw how our Support Techs fix PostgreSQL Error code 42703 for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

I am trying to do a cohort analysis and compare average number of rentals based on the renter’s first rental year(= the year where a renter rented first time). Basically, I am asking the question: are we retaining renters whose first year renting was 2013 than renters whose first year was 2015?

Here is my code:

SELECT renter_id, 
       Min(Date_part('year', created_at)) AS first_rental_year, 
       ( Count(trip_finish) )             AS number_of_trips 
FROM   bookings 
WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
  AND  first_rental_year = 2013 
GROUP  BY 1 
ORDER  BY 1; 

The error message I get is:

ERROR:  column "first_rental_year" does not exist
LINE 6: ... 'aboard', 'ashore', 'concluded', 'disputed') AND first_rent...
                                                             ^

********** Error **********

ERROR: column "first_rental_year" does not exist
SQL state: 42703
Character: 208

Any help is much appreciated.

PostgreSQL database code 42703 is a common error encountered and triggers an error message «column does not exist«. Basically, this error indicates either that the requested column does not exist, or that the query is not correct.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related  PostgreSQL queries.

In this context, we shall look into how to fix this PostgreSQL error.

How to resolve PostgreSQL Error code 42703 ?

Sometimes this error is a result of lack of quotes. We can add double quotes to the column name to fix this error.

For example:

We will try to run a simple select query:

SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMIT 10;

And get the following error:

ERROR: column return_part_i.cntrcttrmntnind does not exist LINE 1: SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMI... ^ HINT: Perhaps you meant to reference the column "return_part_i.CntrctTrmntnInd". SQL state: 42703 Character: 8

if we have a camel case in our column name we must ensure to wrap the column name with a double quote.

This can be done in the following way:

SELECT "CntrctTrmntnInd"  FROM return_part_i LIMIT 10;

PostgreSQL columns (object) names are case sensitive when specified with double quotes. Unquoted identifiers are automatically used as lowercase so the correct case sequence must be written with double quotes.

If we want a LIMIT in result we must use an order by:

SELECT "CntrctTrmntnInd" FROM return_part_i ORDER BY "CntrctTrmntnInd" LIMIT 10;

When used with quotes, Postgresql is case sensitive regarding identifier names like table names and column names.

So a common issue that triggers this error is when we use the column name in our commands in any other cases other than that of the original one.

For instance, if the column name is «Price», using «price» in the command can trigger the error.

Thus we need to make sure that the cases are correct.

[Need assistance in fixing PostgreSQL Errors? We can help you. ]

The problem persisting occasionaly. Occurs when I try a insert.
Do anyone have any idea?

«An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.»

InnerException = ERRO: 42703: coluna»Convenios_Id» da relação «plano» não existe

My properties for relationship:

//Convenio Model

[Table(name: "convenio", Schema = "public")]
[Serializable]
public class Convenios
{       
    [Key, Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual ICollection<Planos> ListaPlanos { get; set; }

    /* ... */
}

/* Model Planos */

[Table(name: "plano", Schema = "public")]
[Serializable]
public class Planos
{

    [Key, Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column("convenio_id")]
    [Required]
    public int ConvenioId { get; set; }

    [ForeignKey("ConvenioId")]
    public virtual Convenios ConvenioVinculado { get; set; }

    /* ... */

}

This is a query generated by Npgsql/Entity Framework:

ERROR: coluna «Convenios_Id» da relação «plano» não existe no
caracter 421 COMMAND: INSERT INTO
«public».»plano»(«convenio_id»,»descricao»,»codigo»,»tabela_id»,»padrao»,»percpac»,»percconv»,»banda_porte»,»banda_uco»,»valch»,»usuario»,»status»,»valfilme»,»codfilme»,»codigofilm»,»motivo»,»limite»,»mpercconv»,»mpercpac»,»fpercpac»,»fpercconv»,»autoriza»,»deparapla»,»valorauto»,»autori»,»ambobriga»,»descmat»,»obs»,»tipotab»,»valcopart»,»dtultger»,»operador_id_ultger»,»marca»,»dtultgerini»,»dtultgerfin»,»Convenios_Id»)
VALUES (0,E’Qui et eum soluta sed et expedita’,NULL,cast(177 as
int8),1,25,75,35,65,cast(30 as
numeric),NULL,0,NULL,NULL,NULL,NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,E’Eos,
modi deserunt beatae et eveniet, mollitia omnis voluptate nulla
eiusmod labore.’,2,61,NULL,NULL,NULL,NULL,NULL,cast(5143 as int8));

I don’t have idea about this problem…

The problem persisting occasionaly. Occurs when I try a insert.
Do anyone have any idea?

«An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.»

InnerException = ERRO: 42703: coluna»Convenios_Id» da relação «plano» não existe

My properties for relationship:

//Convenio Model

[Table(name: "convenio", Schema = "public")]
[Serializable]
public class Convenios
{       
    [Key, Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual ICollection<Planos> ListaPlanos { get; set; }

    /* ... */
}

/* Model Planos */

[Table(name: "plano", Schema = "public")]
[Serializable]
public class Planos
{

    [Key, Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column("convenio_id")]
    [Required]
    public int ConvenioId { get; set; }

    [ForeignKey("ConvenioId")]
    public virtual Convenios ConvenioVinculado { get; set; }

    /* ... */

}

This is a query generated by Npgsql/Entity Framework:

ERROR: coluna «Convenios_Id» da relação «plano» não existe no
caracter 421 COMMAND: INSERT INTO
«public».»plano»(«convenio_id»,»descricao»,»codigo»,»tabela_id»,»padrao»,»percpac»,»percconv»,»banda_porte»,»banda_uco»,»valch»,»usuario»,»status»,»valfilme»,»codfilme»,»codigofilm»,»motivo»,»limite»,»mpercconv»,»mpercpac»,»fpercpac»,»fpercconv»,»autoriza»,»deparapla»,»valorauto»,»autori»,»ambobriga»,»descmat»,»obs»,»tipotab»,»valcopart»,»dtultger»,»operador_id_ultger»,»marca»,»dtultgerini»,»dtultgerfin»,»Convenios_Id»)
VALUES (0,E’Qui et eum soluta sed et expedita’,NULL,cast(177 as
int8),1,25,75,35,65,cast(30 as
numeric),NULL,0,NULL,NULL,NULL,NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,E’Eos,
modi deserunt beatae et eveniet, mollitia omnis voluptate nulla
eiusmod labore.’,2,61,NULL,NULL,NULL,NULL,NULL,cast(5143 as int8));

I don’t have idea about this problem…

[{«Product»:{«code»:»SS6PEW»,»label»:»Sterling Order Management»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Extensions»,»Platform»:[{«code»:»PF016″,»label»:»Linux»}],»Version»:»9.3″,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}}]